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: