How to Extract Data From Word Tables Using Excel VBA
Visual Basic for Applications (VBA), Microsoft's scripting language for Excel and Word, lets users of these programs write scripts that speed up common tasks like accessing table data. Besides giving Excel users the ability to manage Excel table data, VBA also provides a means for accessing tables in Word documents.
VBA developers refer to this ability of one Office program to run another as "automation." One advantage that automating Word data extraction provides is saving time. Without automation, Excel users might need to use more time-consuming methods for bringing data into Excel from Word.
Create a new document in Word by pressing "Control-N." Click the "Insert" menu and then click the "Table" icon in the "Table" panel. Click any cell icon to make a table and then type data into at least the top row's leftmost cell. Save the document as "C:\WordTableData.doc" and then close Word. This step creates sample data that you'll extract with Excel.
Create a new document in Excel and then press "Alt-F11" to enter the Visual Basic integrated development environment (IDE). The IDE provides the means for manually entering VBA programs, in contrast to recording macros within Excel.
Click "Insert" and then "Module" to create a new module for entering program code. Type the following code anywhere inside the new module:
Public Sub access Table()
Set appWD = CreateObject("Word.Application")
appWD.Documents.Open Filename:="C:\WordTableData.doc", _
x = appWD.ActiveDocument.Tables(1).Rows(1).Cells(1)
This subroutine creates a hidden instance of Microsoft Word, then uses it to open a Word document and get the value of a cell in the document's first table.
Click the IDE's "References" item under the "Tools" menu and then scroll down the list of objects until you see "Microsoft Word Object Library." Place a check in the box to the left of this item and then close the "References dialogue box." To run, your code needs to link to Word's virtual objects (for example, tables). The "References" dialogue box lets you create that link.
Click anywhere inside the "accessTable" subroutine you wrote in Step 3 and then press "F5" to run the subroutine. After a brief pause, Excel will display a dialogue box with the data you entered in the Word document's table in Step 1.
Type the following new code after the "Public Sub" statement in the "accessTable" subroutine:
dim some Row, some Column
some Row = inputbox("Enter the row you'd like to pull data from.")
some Column = inputbox("Enter the column you'd like to pull data from.")
These new statements, and the one in the next step, allow you to choose which table cell to extract data from.
Replace the statement beginning with "x = appWD" with this statement:
x = appWD.ActiveDocument.Tables(1).Rows(someRow).Cells(someColumn)
Run the subroutine as you did in Step 5. Respond to the prompts for the row and column. Your revised program will extract the table data from the cell you specified and display it in a dialogue box.