Excellent. Thanks! Is it possible to have a macro in a Word document that creates a new Excel file and populates data to it? It's more convenient for me to have my macro based in my source word document and have it create the excel spreadsheet.
Very good information...Im not a coder/scripter at all but was able to put this together. I do have one question...My results seem to be showing line numbers and not the corresponding paragraph numbers using the script. What adjustment do I need to make to grab the actual paragraph section numbers. Thanks again... Also can you provide updated links for the support data
This is great! Thank you! My only question... Is there a small change I can make somewhere to make it so that only the text in the paragraph AFTER the search word is put into the cell?
Use a looping process to loop through the multiple files in the folder and based on a condition extract the rquired data. This link will guide: www.exceltrainingvideos.com/how-to-loop-through-subfolders/ Or search www.exceltrainingvideos
I have a folder with word files with tables of the same template. I want to fetch data from the plains of all word files; please help me out with the code
Hello Dinesh sir, Thanks a lot for such a wonderful information. But I have concern placed below:- When i try to run your code on my machine my code gives "Compile error messages - User-defined type not defined" when i try to define oWord, ODoc and oRange on the top. Can you please guide me resolve this. I am pretty new to VBA. So, I may have have you a silly question. There is a same concern from Esko Brummel below. I didn't understand the explanation.
Declare a variable: Dim intCurrentLine As Integer Your new looping process will look like this: For CurrRowShtSearchItem = 2 To LastRow Set oRange = oDoc.Range With oRange.Find .Text = shtSearchItem.Cells(CurrRowShtSearchItem, 1).Text .MatchCase = False .MatchWholeWord = True While oRange.Find.Execute = True oRange.Select intCurrentLine = _ oWord.Selection.Range.Information(wdFirstCharacterLineNumber) MsgBox (intCurrentLine) myPara = oDoc.Range(0, oWord.Selection.Paragraphs(1).Range.End).Paragraphs.Count CurrRowShtExtract = CurrRowShtExtract + 1 shtExtract.Cells(CurrRowShtExtract, 1).Value = .Text shtExtract.Cells(CurrRowShtExtract, 2).Value = myPara shtExtract.Cells(CurrRowShtExtract, 3) = oDoc.Paragraphs(myPara).Range oRange.Collapse wdCollapseEnd Wend End With Next CurrRowShtSearchItem You can modify the code according to your needs.
Dinesh, How do I edit your vba code to make it pull the document path from a hyperlink I've made by concat in excel from my spreadsheet? I'd like to pass this information to the script every time I run it so I can use this multiple times on different word docs.
@@Exceltrainingvideos Many many thanks, worked perfectly fine! :-) May I ask an additional question: I want to actually embed the excel spreadsheet into a word document, so is there a way to automatically update the path & filename, so if the word doc is saved under a different name or location, I dont have to manually update the VBA code? Set oDoc = oWord.Documents.Open("C:\Users\...)
Thanks for this video. How would you update this if you were extracting from a numbered list instead of paragraphs? Ideally, I would want to record the list number the value occurred at in one cell and the content of that list number in the adjacent cell.
Hi Mr. Kumar, super useful code and great video! Can I kindly ask 2 further questions on your code: a) Is there a way to edit this code to make it pull the document path automatically from the word document I have embedded the excel spreadsheet into? So if the word doc is saved under a different name or location, I won´t have to manually update the VBA code every time? Set oDoc = oWord.Documents.Open("C:\Users\...) b) How do I need to edit the code, so it will copy the extracted data into the next empty cell? Many thanks in advance! :-)
(a) The following VBA tutorials will guide: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/ www.exceltrainingvideos.com/check-if-folder-exists-with-vba/ www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/ Or search this cannel or www.exceltrainingvideos.com for more solutions. (b) The lastrow+1 variable should solve the problem
Dear Sir, Thank you for the very interesting and useful training tutorial. I have one question. Can we search for specific data from PDF document and extract that data in excel using VBA programming?
Hello and thank you for this useful tutorial! I'm having the following issue when attempting to use your code: when I run the code I get "User defined type not defined" referring to oWord, oDoc,oRange, etc. I have enabled Microsoft Office 16.0 Object Library. Can you think of why I would be having this issue? Thanks
Oops! I confused Microsoft Office 16.0 Object Library with Microsoft WORD 16.0 Object Library. I will leave my comment up as a warning to anyone dumb as me.
This Excel VBA tutorial with a downloadable sample file will guide: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
@@Exceltrainingvideos Sir thanks very much for your response. This link takes me to the same video as above. However I went through the page fully again, it only populates the paragraph number in column 2, not the paragraph title, please give me some clue I can research and find the solution, as of now I am unable to. So I have the paragraph number, how to get the heading of that paragraph? Heading(myPara) is what I need. What is the right command for Heading/subheading?
Is there a possibility to extract specific data(like data for current date or present date) from multiple excel workbooks in a folder into a master workbook??
This VBA tutorial will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/ You can also search this channel or our website www.exceltrainingvideos.com
Is there any way we extract information between two words in word to excel. For example: "This Table contains the following information about the population of the general information". From this, I want the information from Table (including) to population (including).
Hi Sir. This is a very useful video. I have a scenario where i have to fetch the words in between [ ] bracket in a word document and store it in excel in each row. Can you help me with tat? Thanks in advance
These VBA tutorials will help: www.exceltrainingvideos.com/automate-extraction-of-specific-word-from-outlook-message/ www.exceltrainingvideos.com/how-to-extract-specific-words-from-excel-database/ www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
Hello Sir, Thank you for such a great tutorial. I have a special requirement. I want to extract complete strings of data that contain a certain word. Can we do it using this code?
Hi Mr. Kumar, I would like to thank you for your efforts, I would like to ask about comparison report or exception report; how to create or extract the differences. Appreciate to answer me or help me by creating new video for this issue. Regard
This link will guide: www.exceltrainingvideos.com/match-data-from-2-worksheets-highlight-differences-using-collections-in-vba/ Or search www.exceltrainingvideos.com
It's a good idea to read the description accompanying the video. Anyway, here's the link to the VBA tutorial with a downloadable sample file: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
@@Exceltrainingvideos dear , i apply all this but the question how to search by two parameter , for example name and tel no as i mention in the example and i try and but it is not work, if you dont mind modify the code and but it
It's very useful video!! Can you please help me on how to search list of words from excel in MS word, and then provide comments in word document? Many thanks in advance.
Same code applied to my file, but having error Compile error: Invalid Next control variable reference Stopping at Next CurrRowShtSearchItem Please guide
@@Exceltrainingvideos Dear; really i appreciate your quick reply but i tried the way but it doesn’t work for example im searching for Kumar and his Tel no start with 92 i have rows in a word Kumar anil tel no 92502 Kumar bino tel no 92603 Kumar ramish tel no 95012 Dinesh ramish tel no 92500 Vikram harmeet tel no 92604 Kumar ajit tel no 92767 so i need in the sheet1 to put the word and the number that i want to search for example Kumar and tel no start with 92 so the result will be Kumar anil tel no 92502 Kumar bino tel no 92603 Kumar ajit tel no 92767 i tried I changed .MatchWholeWord = False And I modify .Text = shtSearchItem.Cells(CurrRowShtSearchItem, 1) And shtSearchItem.Cells(CurrRowShtSearchItem, 2).Text but it is not work, please help
@@Exceltrainingvideos Many Thanks, But I Have Excel File, column G1 --> G10 are Text File, I want Tranfer This File to MS Word with one Column one Paragraph. Thanks
best video. I tried other yt tutorial but it not working with pdf due to CreateObject("AcroExch.PDDoc") but this video work by changing the file name to pdf
dear , i apply all this but the question how to search by two parameter , for example name and tel no as i mention in the example and i try and but it is not work, if you dont mind modify the code and but it
I wasn't sure if I was going to be able to find what I was looking for, so glad I found this, thanks.
I'm writing from Brazil. Your code is excellent. Thank you for sharing it!
Thank you very much! Please share my channel with your friends. Thanks.
Thank you , on point and very informative. Cheers
Thank you Dinesh. This was very helpful to extract geographical coordinates from a document.
Glad it was helpful!
Thank you for this awesome tutorial. Every steps have been clarified in very simple language, compelling me to subscribe your channel.
Thanks for the sub!
Excellent. Thanks! Is it possible to have a macro in a Word document that creates a new Excel file and populates data to it? It's more convenient for me to have my macro based in my source word document and have it create the excel spreadsheet.
Very good information...Im not a coder/scripter at all but was able to put this together.
I do have one question...My results seem to be showing line numbers and not the corresponding paragraph numbers using the script. What adjustment do I need to make to grab the actual paragraph section numbers.
Thanks again...
Also can you provide updated links for the support data
This is great! Thank you! My only question... Is there a small change I can make somewhere to make it so that only the text in the paragraph AFTER the search word is put into the cell?
These links will help: www.exceltrainingvideos.com/text-handling-vba/
www.exceltrainingvideos.com/how-to-work-with-text-functions-in-excel-2007/
Thanks for sharing this! May I know how to modify it so that it can extract from multiple MS Word documents in a specific folder at one go?
Use a looping process to loop through the multiple files in the folder and based on a condition extract the rquired data. This link will guide: www.exceltrainingvideos.com/how-to-loop-through-subfolders/
Or search www.exceltrainingvideos
Can we do this same from text file to word table format?
Thanks for share this Sir 👍 ❤️
Thank you very much for this great video and for this valuable information.
I have a folder with word files with tables of the same template. I want to fetch data from the plains of all word files; please help me out with the code
Hello Dinesh sir,
Thanks a lot for such a wonderful information.
But I have concern placed below:-
When i try to run your code on my machine my code gives "Compile error messages - User-defined type not defined" when i try to define oWord, ODoc and oRange on the top. Can you please guide me resolve this.
I am pretty new to VBA. So, I may have have you a silly question.
There is a same concern from
Esko Brummel below. I didn't understand the explanation.
Most probably you haven't activated the Microsoft Word Library as shown in the video starting at 11:55 minutes.
@@Exceltrainingvideos Thanks !! sir
Good Explained !
Thanks for the viedeo, it help me a lot, but how can i get the row or line number instead of the paragraph?
Declare a variable:
Dim intCurrentLine As Integer
Your new looping process will look like this:
For CurrRowShtSearchItem = 2 To LastRow
Set oRange = oDoc.Range
With oRange.Find
.Text = shtSearchItem.Cells(CurrRowShtSearchItem, 1).Text
.MatchCase = False
.MatchWholeWord = True
While oRange.Find.Execute = True
oRange.Select
intCurrentLine = _
oWord.Selection.Range.Information(wdFirstCharacterLineNumber)
MsgBox (intCurrentLine)
myPara = oDoc.Range(0, oWord.Selection.Paragraphs(1).Range.End).Paragraphs.Count
CurrRowShtExtract = CurrRowShtExtract + 1
shtExtract.Cells(CurrRowShtExtract, 1).Value = .Text
shtExtract.Cells(CurrRowShtExtract, 2).Value = myPara
shtExtract.Cells(CurrRowShtExtract, 3) = oDoc.Paragraphs(myPara).Range
oRange.Collapse wdCollapseEnd
Wend
End With
Next CurrRowShtSearchItem
You can modify the code according to your needs.
Dinesh, How do I edit your vba code to make it pull the document path from a hyperlink I've made by concat in excel from my spreadsheet? I'd like to pass this information to the script every time I run it so I can use this multiple times on different word docs.
How does your hyperlink look like?
Thank you, this is really great!
Is there a way to have the extracted data copied in the 2nd row of the Worksheet? Many thanks in advance!
Just initialize the value of the variable CurrRowShtExtract to 1.
@@Exceltrainingvideos Many many thanks, worked perfectly fine! :-) May I ask an additional question: I want to actually embed the excel spreadsheet into a word document, so is there a way to automatically update the path & filename, so if the word doc is saved under a different name or location, I dont have to manually update the VBA code?
Set oDoc = oWord.Documents.Open("C:\Users\...)
Could you provide a new link please since I cannot access
What if I wanted to return page number instead of paragraph number?
Thanks for this video. How would you update this if you were extracting from a numbered list instead of paragraphs? Ideally, I would want to record the list number the value occurred at in one cell and the content of that list number in the adjacent cell.
This video will guide : th-cam.com/video/aIoMAtSYW_8/w-d-xo.html
@@Exceltrainingvideos not able to get the list number sir can u help with that
Hi Sir
I have word document containing destination requirement & I want consolidate to Excel using VBA please make on video on this
Hi Mr. Kumar,
super useful code and great video! Can I kindly ask 2 further questions on your code:
a) Is there a way to edit this code to make it pull the document path automatically from the word document I have embedded the excel spreadsheet into? So if the word doc is saved under a different name or location, I won´t have to manually update the VBA code every time?
Set oDoc = oWord.Documents.Open("C:\Users\...)
b) How do I need to edit the code, so it will copy the extracted data into the next empty cell?
Many thanks in advance! :-)
(a) The following VBA tutorials will guide:
www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
www.exceltrainingvideos.com/check-if-folder-exists-with-vba/
www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
Or search this cannel or www.exceltrainingvideos.com for more solutions.
(b) The lastrow+1 variable should solve the problem
Dear Sir, Thank you for the very interesting and useful training tutorial. I have one question. Can we search for specific data from PDF document and extract that data in excel using VBA programming?
Should be possible.
Hi Sir, How can this be done? Is there any video on this? Thanks in advance
Hello and thank you for this useful tutorial! I'm having the following issue when attempting to use your code: when I run the code I get "User defined type not defined" referring to oWord, oDoc,oRange, etc. I have enabled Microsoft Office 16.0 Object Library. Can you think of why I would be having this issue? Thanks
This link will help: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
Oops! I confused Microsoft Office 16.0 Object Library with Microsoft WORD 16.0 Object Library. I will leave my comment up as a warning to anyone dumb as me.
Sir, this is gold. May I know how to populate also the heading of that particular paragraph in one column?
This Excel VBA tutorial with a downloadable sample file will guide: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
@@Exceltrainingvideos Sir thanks very much for your response. This link takes me to the same video as above. However I went through the page fully again, it only populates the paragraph number in column 2, not the paragraph title, please give me some clue I can research and find the solution, as of now I am unable to. So I have the paragraph number, how to get the heading of that paragraph? Heading(myPara) is what I need. What is the right command for Heading/subheading?
Is there a possibility to extract specific data(like data for current date or present date) from multiple excel workbooks in a folder into a master workbook??
This VBA tutorial will help: www.exceltrainingvideos.com/how-to-transfer-multiple-rows-of-data-from-multiple-workbooks-into-master-workbook-with-vba/
You can also search this channel or our website www.exceltrainingvideos.com
Is there any way we extract information between two words in word to excel. For example: "This Table contains the following information about the population of the general information". From this, I want the information from Table (including) to population (including).
Try text functions. This Excel VBA tutorial will guide: www.exceltrainingvideos.com/text-handling-vba/
@@Exceltrainingvideos i see the text functions from excel but i am looking the text from word
Is it possible to read multiple documents and also format the paragraph with Line character, please?
Will work on this theme.
i have excel 2007 and i got the VB tap but from where i can get all these code
VBA code works fine in Excel 2007. You can go to File --- Options --- Customize Ribbon --- check 'developer' and finally click OK
Sir How to prevent sheet tab names means user can't change sheet name ? VBA
Hi Sir. This is a very useful video. I have a scenario where i have to fetch the words in between [ ] bracket in a word document and store it in excel in each row. Can you help me with tat? Thanks in advance
These VBA tutorials will help:
www.exceltrainingvideos.com/automate-extraction-of-specific-word-from-outlook-message/
www.exceltrainingvideos.com/how-to-extract-specific-words-from-excel-database/
www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
Great video, thank you! I tried clicking the link and it seems like your entire website is not loading for me. Is it down right now?
This can happen sometimes due to maintenance although it's rare.
Hello sir thanks for sharing this, I have a requirement where i need to search for a particular heading in word and paste its paragraph in excel
These links will help:
docs.microsoft.com/en-us/office/vba/api/word.headersfooters
wordmvp.com/FAQs/Customization/ReplaceAnywhere.htm
Hi, is there any way we can automate this for multiple word documents?
Loop through your documents!
May i have your code sir ? Please share the link to get this code
Dear Sir, Please tell me how to extract the single line alone instead of whole paragraph....
OK
Hello Sir, Thank you for such a great tutorial. I have a special requirement. I want to extract complete strings of data that contain a certain word. Can we do it using this code?
It exactly worked for what I required to do with it, superb. Great Sir and thanks a lot.
Glad to hear that! Please share with your friends too.
@@Exceltrainingvideos Sure, I will share it ... )
Sir, can you please teach us how we can extract data from excel file to word file?
Thank you
This link will help: www.exceltrainingvideos.com/import-data-into-word-from-excel-using-vba/
You can also search www.exceltrainingvideos.com
Hi Mr. Kumar,
I would like to thank you for your efforts, I would like to ask about comparison report or exception report; how to create or extract the differences. Appreciate to answer me or help me by creating new video for this issue.
Regard
This link will guide: www.exceltrainingvideos.com/match-data-from-2-worksheets-highlight-differences-using-collections-in-vba/
Or search www.exceltrainingvideos.com
what is the command because I tried it do each one spritely not as a companion
It's a good idea to read the description accompanying the video. Anyway, here's the link to the VBA tutorial with a downloadable sample file: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
@@Exceltrainingvideos dear , i apply all this but the question how to search by two parameter , for example name and tel no as i mention in the example and i try and but it is not work,
if you dont mind modify the code and but it
I am. Getting compile error: INVALID OUTSIDE PROCEDURE. can you help me regarding this issue....
The following Excel VBA training video will help: www.exceltrainingvideos.com/extract-specific-data-from-ms-word-into-excel-with-vba/
It's very useful video!! Can you please help me on how to search list of words from excel in MS word, and then provide comments in word document? Many thanks in advance.
OK
Same code applied to my file, but having error Compile error: Invalid Next control variable reference
Stopping at Next CurrRowShtSearchItem
Please guide
how i can search in the same line but for two words
for example im searching for a person name Kumar and his tel no start with 92 ?
Easy way to do this is to use the same trick for the second word or use 'AND'.
@@Exceltrainingvideos
Dear;
really i appreciate your quick reply
but i tried the way but it doesn’t work
for example im searching for Kumar and his Tel no start with 92
i have rows
in a word
Kumar anil tel no 92502
Kumar bino tel no 92603
Kumar ramish tel no 95012
Dinesh ramish tel no 92500
Vikram harmeet tel no 92604
Kumar ajit tel no 92767
so i need in the sheet1 to put the word and the number
that i want to search for example Kumar and tel no start with 92
so the result will be
Kumar anil tel no 92502
Kumar bino tel no 92603
Kumar ajit tel no 92767
i tried
I changed
.MatchWholeWord = False
And I modify
.Text = shtSearchItem.Cells(CurrRowShtSearchItem, 1) And shtSearchItem.Cells(CurrRowShtSearchItem, 2).Text
but it is not work,
please help
I want transfer excel text to word, can help me?
This Excel VBA tutorial will help: www.exceltrainingvideos.com/import-data-into-word-from-excel-using-vba/
@@Exceltrainingvideos Thanks Sir
@@Exceltrainingvideos Many Thanks, But I Have Excel File, column G1 --> G10 are Text File, I want Tranfer This File to MS Word with one Column one Paragraph. Thanks
can we do search with conditions
Yes.
best video. I tried other yt tutorial but it not working with pdf due to CreateObject("AcroExch.PDDoc") but this video work by
changing the file name to pdf
How to extract word headers footers into excel
Noted.
Thank for replying Sir. After so many search, I got the code
Hi sir,
This video is really awesome.. Please could you assist me on
How to fix an OLE error in excel 2003?
This link will help:
windowsreport.com/excel-is-waiting-for-ole/
Great i did it :)
Great job!
How to transfer an excel file to exe permanently
You can't do this VBA. Of course, you can create an add-in. This link will help:
www.exceltrainingvideos.com/create-custom-add-in-excel/
dear , i apply all this but the question how to search by two parameter , for example name and tel no as i mention in the example and i try and but it is not work,
if you dont mind modify the code and but it
This link will guide: th-cam.com/video/9KJ0V3GdBm4/w-d-xo.html