How to Extract Data From Word Tables Using Excel VBA

Written by darrin koltow
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Extract Data From Word Tables Using Excel VBA
VBA automation provides a means for accessing Word data from Excel. (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.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 1

    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.

  2. 2

    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.

  3. 3

    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", _

        ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False,
    
        PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    
        WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    
        wdOpenFormatAuto
    

    x = appWD.ActiveDocument.Tables(1).Rows(1).Cells(1)

    MsgBox (x)

    appWD.Quit

    End Sub

    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.

  4. 4

    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.

  5. 5

    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.

  6. 6

    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.

  7. 7

    Replace the statement beginning with "x = appWD" with this statement:

    x = appWD.ActiveDocument.Tables(1).Rows(someRow).Cells(someColumn)

  8. 8

    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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.