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 |