How to Access Word Tables Using Excel VBA

Word and Excel are both applications in the Microsoft Office suite. Word is a word processing application and Excel a spreadsheet application. Knowing how to access Microsoft Word tables from Microsoft Excel can be useful when working with Word tables. Visual Basic for Applications (VBA) is a programming language that's easy to learn and use, and in a few steps you can write VBA code to access a Word table from Excel.

Instructions

1

Start Microsoft Office Word, select the "Insert" menu and click "Table". Select a table with two columns and two rows.

Click the cell in the top left and type "Column1", press "Tab" to move to the second column and type "Column2".

Click the first cell in row number two and type "Datq1". Press "Tab" to move to the second column and type "Data2".

Save the document in "C:\" as "myTableDoc.doc."

2

Start Microsoft Office Excel, select the "Developer" tab then click "Visual Basic." Click the "Insert" menu and select "Module."

Select the "Tools" menu and click "References...". Check the box next to "Microsoft Word Object Library." Click "OK."

3

Type the following to create the "getWordTableData" subroutine:

Private Sub getWordTableData()

Press "Enter."

4

Type the following to create new variables:

Dim clmnCnt As Integer

Dim rowCnt As Integer

5

Type the following to create Word objects:

Set wrdApp = CreateObject("Word.Application")

Set wrdDoc = wrdApp.Documents.Open("C:\myTableDoc.doc")

6

Type the following to loop through the table in "myTableDoc.doc" and display its contents through a message box:

For clmnCnt = 1 To wrdDoc.Tables(1).Columns.Count

For rowCnt = 1 To wrdDoc.Tables(1).Rows.Count

MsgBox wrdDoc.Tables(1).Cell(rowCnt, clmnCnt).Range.Text

Next rowCnt

Next clmnCnt

7

Type the following to release your variable from memory:

Set wrdApp = Nothing

Set wrdDoc = Nothing

Press "F5" to run your subroutine.