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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.