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
Err.Clear
‘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

WdApp.Activate

Set wddoc = WdApp.Documents(strDocName)

If wddoc Is Nothing Then Set wddoc = WdApp.Documents.Open(strDocName)
wddoc.Activate
‘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

Next
End With
‘we don’t need to save the word document
wddoc.Close Savechanges:=False
‘we quit Word
WdApp.Quit
‘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:

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

  1. Ayham

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

    Reply
  2. Zoe

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

    Reply
  3. Vi

    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?

    Reply
  4. Sanket Bhattacharya

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *