June 24, 2014

How to Import Data from Word Tables into Excel with VBA

We can import data from Word tables into Excel with VBA quickly and easily. The code involves accessing the word document, identifying and capturing the tables and then importing the table data step by step using the row and column properties of the word table. Once we access each row and column of the Word table using a ‘for’ nested loop, we can place the data into our Excel worksheet cells using the cells property. The great advantage of adopting such a strategy could be the ease of use in entering data in Word because most people in offices today are comfortable working in MS Word. Once the data is imported into Excel we can perform complicated calculations and analysis quickly and easily. Watch the training video below (54 MB) and then study the code step by step to understand the ease with which data can be extracted from MS Word tables into an MS Excel worksheet:

Watch the video on YouTube.

Sub importTableDataWord()
‘We declare object variables for Word Application and document
Dim WdApp As Object, wddoc As Object
‘Declare a string variable to access our Word document
Dim strDocName As String
‘Error handling
On Error Resume Next
‘Activate Word it is already open
Set WdApp = GetObject(, “Word.Application”)
If Err.Number = 429 Then
‘Create a Word application if Word is not already open
Set WdApp = CreateObject(“Word.Application”)
End If
WdApp.Visible = True

strDocName = “C:\our-inventory\inventory.docx”
‘Check relevant directory for relevant document
‘If not found then inform the user and close program
If Dir(strDocName) = “” Then
MsgBox “The file ” & strDocName & vbCrLf & _
“was not found in the folder path” & vbCrLf & _
“C:\our-inventory\.”, _
vbExclamation, _
“Sorry, that document name does not exist.”
Exit Sub
End If


Set wddoc = WdApp.Documents(strDocName)

If wddoc Is Nothing Then Set wddoc = WdApp.Documents.Open(strDocName)
‘define variables to access the tables in the word document
Dim Tble As Integer
Dim rowWd As Long
Dim colWd As Integer
Dim x As Long, y As Long
x = 1
y = 1
With wddoc
Tble = wddoc.tables.Count
If Tble = 0 Then

MsgBox “No Tables found in the Word document”, vbExclamation, “No Tables to Import”
Exit Sub
End If
‘start the looping process to access tables and their rows, columns
For i = 1 To Tble
With .tables(i)
For rowWd = 1 To .Rows.Count
For colWd = 1 To .Columns.Count
Cells(x, y) = WorksheetFunction.Clean(.cell(rowWd, colWd).Range.Text)
‘Access next column
y = y + 1

Next colWd
‘go to next row and start from column 1
y = 1
x = x + 1

Next rowWd
End With

End With
‘we don’t need to save the word document
wddoc.Close Savechanges:=False
‘we quit Word
‘We finally release system memory allocated to the 2 object variables
Set wddoc = Nothing
Set WdApp = Nothing
End Sub

Also have a look at this interesting video:

Excel like calculations in word

Download sample file by clicking on the Excel icon:

13 thoughts on “How to Import Data from Word Tables into Excel with VBA

  1. Thanks for the code, worked a treat extracting large tables of data from multiple word documents

  2. great, but the big question is, how can i take the text with its style(bold, italic , etc.), not just as plain data.

  3. Big question too: how to create a loop that would process the code for all the documents in a specific folder?

  4. This is really a helpful tutorial. But I receive msgbox “No Tables to Import” although my word file contains 3 tables. I cannot find out the root cause. Any suggestion?

  5. I have hundreds of word files. Each of these word files is in the format of a table. I need to extract certain information from each of these word files and paste it in excel in separate columns.

    Example of word file:

    heading1 sensor information need to be clarified
    dateofgather 21-11-2016
    group1 002 1.2 3.4
    group2 101 4.2 5.1
    comments need to be re calibrated and redone

    In this above example, all of this, is in the form a table in a single word document. All the words files have the same format but the values shown above keep changing. I need to copy these values into a single excel spreadsheet (in separate columns) and do it for all the word files.

    For this, I need to use a VBA script in Excel, thereby creating a macro, which would enable me to extract all the information I need from all these word files, placed inside a single folder, into a single excel spreadsheet.

    Please let me know if this can be done because this is really urgent. Thanks to all of you for your suggestions and help.

  6. I typed the Macro, it’s importing some data but is not organizing in columns. My file has “N” tables always with same fields on each table (about 30 Fields per Table).

  7. i have applied this code but the error msg dialog appears “that there is no tables in word document”
    thorough this VBA code i wan to copy data from word document this consists of more than 1000 pages.

  8. I have a Word Form where I need to export data to an Excel spreadsheet. I need the VBA to run in the Word document. Could you help?

Comments are closed.