Reading a Word Table in Excel VBA

The following procedure reads the entire content of the Word table shown in next Figure and stores it in an Excel worksheet. Make sure the Word document is not open in Word before running this VBA code:

Sub ReadWordTable()
    Dim appWord As Word.Application
    Dim document As Word.Document
    Dim table As Word.Table
    Dim i As Integer
    Dim k As Integer
    Dim cellText As String
    Dim numberValue As Double
    Dim dateValue As Date
    ThisWorkbook.Worksheets("Sheet2").Activate
    ' Create Word application object
    Set appWord = CreateObject("Word.Application")
    ' Open the Word document
    Set document = appWord.Documents.Add(ThisWorkbook.Path & "\table.docx")
    ' Reference the first table in the document
    Set table = document.Tables(1)
    ' Loop through rows and columns
    For i = 1 To table.Rows.Count
        For k = 1 To table.Columns.Count
            ' Get the text of the cell, including cell end characters
            cellText = table.Cell(i, k).Range.Text
            ' Remove the two end-of-cell characters
            cellText = Left(cellText, Len(cellText) - 2)
            ' Determine the data type and write accordingly
            If IsNumeric(cellText) Then
                If InStr(cellText, ".") > 0 Then
                    dateValue = CDate(cellText)
                    Cells(i + 10, k).Value = dateValue
                Else
                    numberValue = CDbl(cellText)
                    Cells(i + 10, k).Value = numberValue
                End If
            Else
                Cells(i + 10, k).Value = cellText
            End If
        Next k
    Next i
    ' Close document and quit Word
    document.Close
    appWord.Quit
    Set table = Nothing
    Set document = Nothing
    Set appWord = Nothing
End Sub

The result of this import is shown in Figure.

Explanation:

• The variable table references the first Word.Table object in the document by accessing Tables(1).
• The properties Rows and Columns provide the collections of rows and columns in the table. The Count property gives the total number, which is used as the limit for the loops.
• The text of the entire content of a cell is accessed via Cell(row, column).Range.Text.
• Each cell’s text includes two end-of-cell characters; these are removed by using the string functions Left() and Len() to trim the last two characters.
• The data type of the remaining text is determined, as described in Section 8.4.1, « Converting Strings. » Based on the result, the value is converted to a number, date, or left as a string.
• The processed data is written into the Excel worksheet, starting from row 11 (offset by +10), with rows and columns corresponding to those in the Word table.