Extract Data from Word Document Using Word and Excel VBA
Вставка
- Опубліковано 5 жов 2024
- How to extract data from a Word document using both Word VBA and Excel VBA.
Here's the complete code:
Sub ExtractText()
Dim cDoc As Word.Document
Dim cRng As Word.Range
Dim i As Long
i = 2
Dim wordapp As Object
Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open "c:\bracketdata\bracket-data.docx"
wordapp.Visible = True
Set cDoc = ActiveDocument
Set cRng = cDoc.Content
With cRng.Find
.Forward = True
.Text = "["
.Wrap = wdFindStop
.Execute
Do While .Found
'Collapses a range or selection to the starting or ending position
cRng.Collapse Word.WdCollapseDirection.wdCollapseEnd
cRng.MoveEndUntil Cset:="]"
Cells(i, 1) = cRng
cRng.Collapse Word.WdCollapseDirection.wdCollapseEnd
.Execute
i = i + 1
Loop
End With
wordapp.Quit
Set wordapp = Nothing
End Sub
Option Explicit
Sub ExtractText()
Dim cDoc As Word.Document, nDoc As Word.Document
Dim cRng As Word.Range, nRng As Word.Range
Set cDoc = ActiveDocument
Set nDoc = Documents.Add
Set cRng = cDoc.Content
Set nRng = nDoc.Content
cRng.Find.ClearFormatting
With cRng.Find
.Forward = True
.Text = "["
.Wrap = wdFindStop
.Execute
Do While .Found
cRng.Collapse Word.WdCollapseDirection.wdCollapseEnd
cRng.MoveEndUntil Cset:="]", Count:=Word.wdForward
nRng.FormattedText = cRng.FormattedText
nRng.InsertParagraphAfter
nRng.Collapse Word.WdCollapseDirection.wdCollapseEnd
cRng.Collapse Word.WdCollapseDirection.wdCollapseEnd
.Execute
Loop
End With
End Sub
Reference:
social.msdn.mi...
![[UA] NAVI проти G2 Esports | Blast Premier Fall Final](http://i.ytimg.com/vi/QWlIm4FGESQ/mqdefault.jpg)
nice simple code that executes well, suggestion would be to remove any formatting from source document as a elements such as hyperlinks can break the code execution.
Thank you sir,
This code running but,
Upgrade this code with
Add [ Text, image(png, jpg), maths equation, Entry button, etc ]
❤
Hello Dinesh, your video is amazing and underrated! 👍
Excellent video sir! saved hrs of my life! I
wish to extract highlighted data instead of data in brackets please guide!
Glad it helped. This link will guide: www.exceltrainingvideos.com/identify-colored-cells-excel-worksheet-and-perform-actions/
I'm trying this on a macro enabled word document (docm files) which things should i change in this code to make it work?
Totally different way of working from Word!
Thank you so much, ¡Brilliant!.
Anyway, please, could it be possible that the text format (from Word) will be transferred to the Excel? And if possible, could you teach the code to be added to the code in this video?
Tank you very much.
Cheers,
Alberto
Very nice and Brilliant code created
Glad you like it! Please share the Excel VBA tutorial with your friends also.
Hey Thank you for this help. Stay Blessed.
You're welcome!
Simply superb sir. Please do a video on filtered data. Vlookup. How to do visible cells vlookup. Please sir
Already uploaded. Search this channel or our website: www.exceltrainingvideos.com/
Use this exact code but I'm getting a "Compile error: User-defined type not defined" and it is referring to... cDoc As Word.Document
Simply how to do vlookup function when any data is in filter. For visible cells only we have to lookup using match function for headers mapping.
Query not clear.
Hello sir, I’m running into an issue here. When I ran the code for the word document, I could only run for the first page, my results from the second page is not showing up. Also my word document contains text that I want to search for and copy to excel. Is there a video of yours I could look at for more detail? Thanks for all your help in advance
Please search the channel. To get data from multiple pages in your word document, you'll have to use a looping process.
Thanks a lot. That's really helpful
Glad it was helpful! Please share the VBA tutorial with your friends.
Tqs for the reply sir. But there is no video for how to do vlookup for multer data using match.
What is multer data?
can we export date from multiple word files, copying those to excel ?
Should be possible.
hi sir. I have a case. with a worksheet of multiple columns. each data in the column may refer to another sheets on a lan or share point are which need to be validated
second. if one cell value is given. based on cell value next column to be populated based on validation from different sheet. I may have 100 plus reference sheets where data to be validated.
is that a possible case. can you help me sir
Noted
Can you show VBA How to auto transfer data to another sheet by using date?
This VBA tutorial link will guide: www.exceltrainingvideos.com/tag/copy-data-based-on-date-criteria/
Or search www.exceltrainingvideos.com
Thanks so much
Interesting
Glad you found the Excel VBA tutorial interesting. Please share with your friends too.
Can this be used to get some data from word and then add it to the meta data of the file? then adding it to say the "TAG" properties?
Try it out!
@@Exceltrainingvideos I have and it doesn't work.
Thanks for sharing... Great learning experience. Can it possible to extract a sentence after find a specific word on it?
Could you please take another session on this?
Yes you can! Try it out!
Sorry for spelling mistake. Vlookup when data is in filter. Using match for headers. Ex: filter completed when we have to lookup for pending. But the columns are not in sequence.
Noted
@@Exceltrainingvideos: thank you. Waiting for video sir
Hi Sir. I am running into an error while running this code. when I run the code first time it works fine but when I run it for the second time it throws the error on the below line
set cDoc=ActiveDocument
the error is read as "the remote server machine does not exist or unavailable."
Please advise how to remove this error.
Just save and close the document in the first extraction.
Still not working Sir. I got 462 error.