Extract Data from Word Document Using Word and Excel VBA

How to extract data from a Word document using both Word VBA and Excel VBA. Watch the video below:

Extract Data from Word Document Using VBA

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

Extract data from square brackets in Word document with VBA

Reference:

Word VBA to extract data from square brackets

Range.Collapse Method

Range.Find Property in Word VBA

Find.Execute Method in Word VBA

Find.Wrap Property Word VBA