How to Extract Data From Word Tables Using Excel VBA
binary world image by Attila Toro from Fotolia.com
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.
- 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.
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.
- Create a new document in Excel and then press "Alt-F11" to enter the Visual Basic integrated development environment (IDE).
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.
Darrin Koltow wrote about computer software until graphics programs reawakened his lifelong passion of becoming a master designer and draftsman. He has now committed to acquiring the training for a position designing characters, creatures and environments for video games, movies and other entertainment media.