Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Import Data from Word Table to Excel sheet

Today's lesson is about how to import data from word table to an Excel sheet.

Here is a sample table in a word document.


Once we run the macro, It will give below result.




Below is an example code to import first table of the word document to an Excel sheet.

Remember to add a reference to the Word-library.


Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

Set wrdDoc = wrdApp.Documents.Open(ThisWorkbook.Path & "\My document.doc")
'Use below line if document is already open.
'Set wrdDoc = Documents("My document.doc")

With wrdDoc

    N_Of_tbles = .Tables.Count
 
    If N_Of_tbles = 0 Then
        MsgBox "There are no tables in word document"
    End If
 
    Set wrdTbl = .Tables(1)
 
    ColCount = wrdTbl.Columns.Count
    RowCount = wrdTbl.Rows.Count
 

    ' Loop through each row of the table
    For i = 1 To RowCount
        'Loop through each column of that row
        For j = 1 To ColCount
            'This gives you the cell contents
            Worksheets("sheet1").Cells(i, j) = wrdTbl.Cell(i, j).Range.Text
        Next j
    Next i
End With

Set wrdDoc = Nothing
Set wrdApp = Nothing

MsgBox "completed"

Here is a brief explanation about the code.

You can set the visibility of the word application by assigning true or false to wrdApp.Visible
As we have set that value true in our example it will show the Word application.

Set wrdDoc = wrdApp.Documents.Open(ThisWorkbook.Path & "\My document.doc")

This will open a word file "My document.doc" which is in the same folder as the Excel file which has this code. If your file is in a different location like D:\My files\My document.doc, Then you can open the file by following code.

Set wrdDoc = wrdApp.Documents.Open("D:\My files\My document.doc")

In above, our word document is in closed state. What if our word document is already open. If so we can use below code.

Set wrdDoc = Documents("My document.doc")


Tables.Count

This will tell you how many tables are in our word document.

Set wrdTbl = .Tables(1)

Here we only import data from 1st table.
But if we want to import data from all tables, then we can use for loop to import from all of them.

wrdTbl.Columns.Count
wrdTbl.Rows.Count




above will count the number of columns and the rows of the table respectively.

And following part will put data of each cell of Word table to relavent Excel cell.

For i = 1 To RowCount
    For j = 1 To ColCount
        Worksheets("sheet1").Cells(i, j) = wrdTbl.Cell(i, j).Range.Text
    Next j
Nex i

Contact Form

Name

Email *

Message *

Popular Posts