February 26, 2019

Extract Specific Data from MS Word into Excel with VBA

How to extract specific data from MS Word into Excel with VBA automatically.
We use two worksheets in Excel. The first worksheet contains the search terms that we wish to extract from Word. The second sheet, if not available is created automatically and the search term, paragraph number and the associated data extracted from Ms Word , is displayed here. watch the video before studying the accompanying code.

Watch this video on YouTube.

Here’s the complete VBA code to extract data from a Word document quickly and easily:

Sub LocateSearchItem()
Dim shtSearchItem As Worksheet
Dim shtExtract As Worksheet
Dim oWord As Word.Application
Dim WordNotOpen As Boolean
Dim oDoc As Word.Document
Dim oRange As Word.Range
Dim LastRow As Long ‘ last row with data in shtSearchItem
Dim CurrRowShtSearchItem As Long ‘ current row in shtSearchItem
Dim CurrRowShtExtract As Long ‘ current row in shtExtract
Dim myPara As Long

On Error Resume Next

Set oWord = GetObject(, "Word.Application")

If Err Then
    Set oWord = New Word.Application
    WordNotOpen = True
End If

On Error GoTo Err_Handler

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Open("C:\Users\takyar\Desktop\VBA & Word.docx")       ' <= modify according to your path

Set shtSearchItem = ThisWorkbook.Worksheets(1)
If ThisWorkbook.Worksheets.Count < 2 Then
    ThisWorkbook.Worksheets.Add After:=shtSearchItem
End If
Set shtExtract = ThisWorkbook.Worksheets(2)

LastRow = shtSearchItem.UsedRange.Rows(shtSearchItem.UsedRange.Rows.Count).Row

For CurrRowShtSearchItem = 2 To LastRow
    Set oRange = oDoc.Range
    With oRange.Find
        .Text = shtSearchItem.Cells(CurrRowShtSearchItem, 1).Text
        .MatchCase = False
        .MatchWholeWord = True
        While oRange.Find.Execute = True
            oRange.Select
            myPara = oDoc.Range(0, oWord.Selection.Paragraphs(1).Range.End).Paragraphs.Count

            CurrRowShtExtract = CurrRowShtExtract + 1

            shtExtract.Cells(CurrRowShtExtract, 1).Value = .Text
            shtExtract.Cells(CurrRowShtExtract, 2).Value = myPara
            shtExtract.Cells(CurrRowShtExtract, 3) = oDoc.Paragraphs(myPara).Range

            oRange.Collapse wdCollapseEnd

        Wend
    End With
Next CurrRowShtSearchItem

If WordNotOpen Then
    oWord.Quit
End If

'Release object references

Set oWord = Nothing
Set oDoc = Nothing

Exit Sub

Err_Handler:
MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " & Err.Number
If WordNotOpen Then
oWord.Quit
End If

End Sub

Further Reading:

Get specific text from Word document & paste into Excel

Macro to copy line by line from word to new excel document

VBA & Word

Range.Collapse method (Word)

Sample File for Download and Practice:

5 thoughts on “Extract Specific Data from MS Word into Excel with VBA

  1. Sir plz make a video on excel which include the following topics…
    1) add data. (this add function should work in such a way that two copies of data should be generated, one on the main sheet and another on the sheet in which i want too.
    2)delete data. (delete the data from main sheet only and not from the another sheet)
    Search data. (this function should search the data from the second backup sheet not from the main sheet.
    3)refresh data( just for update the data. )
    4)print data( last sheet should be printed.)
    5) clear data( this should clear the content of the selected part but not the formulas of that region.)

    I will be really very thankful to u sir…
    I will be waiting for my video…
    Thanks …
    Md shahid Hussain.

  2. Hi, this is really great!
    How can we modify this to extract from multiple MS Word documents at one go? Or extract from a specific folder with all the MS Word documents that we want to work on?

    Thank you.

  3. I have the correct library set up, no code lines missing, all variables are correct, I’m getting no additional debugging errors and my macro does nothing.
    Only thing the macro does is to successfully open up the Word doc where I have my content, and saved on my desktop for this specific exercise.

    What else is the issue perhaps?

  4. Dear Sir;
    what is the command because I tried it do each one spritely not as a companion
    for example
    i tried the way but it doesn’t work
    for example im searching for Kumar and his Tel no start with 92

    i have rows
    in a word
    Kumar anil tel no 92502
    Kumar bino tel no 92603
    Kumar ramish tel no 95012
    Dinesh ramish tel no 92500
    Vikram harmeet tel no 92604
    Kumar ajit tel no 92767

    so i need in the sheet1 to put the word and the number
    that i want to search for example Kumar and tel no start with 92
    so the result will be
    Kumar anil tel no 92502
    Kumar bino tel no 92603
    Kumar ajit tel no 92767

    i tried
    I changed
    .MatchWholeWord = False
    And I modify
    .Text = shtSearchItem.Cells(CurrRowShtSearchItem, 1) And shtSearchItem.Cells(CurrRowShtSearchItem, 2).Text

    but it is not work,
    please help

  5. Hi Sir,

    I want also to put the page number along with the paragraph number while extracting the data. Can you kindly suggest the modification that I need to do in your code to get it done?

Comments are closed.