July 24, 2020

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

One thought on “Extract Data from Word Document Using Word and Excel VBA

  1. Sir, there are multiple tables in my word document. How can i extract specific tables having data into excel sheet using word VBA.

Comments are closed.