How to extract data from a Word document using both Word VBA and Excel VBA. Watch the video below:
Here’s the complete code VBA code:
(1) VBA Code for MS Excel
Sub ExtractText()
Dim cDoc As Word.Document
Dim cRng As Word.Range
Dim i As Long
‘initialize the value of i o 2 i.e. start at second row
i = 2
Dim wordapp As Object
Set wordapp = CreateObject(“word.Application”)
‘you’ll give your path to the file below
wordapp.Documents.Open “c:\bracketdata\bracket-data.docx”
wordapp.Visible = True
Set cDoc = ActiveDocument
Set cRng = cDoc.Content
With cRng.Find
.Forward = True
.Text = “[”
‘The find operation ends if the beginning or end of the search range is reached
.Wrap = wdFindStop
‘ Find.Execute method (Word) Runs the specified find operation.
‘Returns True if the find operation is successful
.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
(2) VBA Code for MS Word
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:
Word VBA to extract data from square brackets
Range.Find Property in Word VBA