Open an Excel Spreadsheet from Word VBA and Import data to Table in Word


  • I have approx. ten thousans copies of the same form to be filled out for the client. Four cells in the table on my form need to be changed on each copy.  the data that is needed for one of the cells i must manually enter however the data for the other three of these cells in located in an excel spreadsheet.  is there anyway to use VBA in my Word Form to automatically open the excel spreadsheet, use the data i entered manually in the first cell of my word table to search the excel spreadsheet for the corresponding values and import them back into the other three cells in my table in word?

    for example:

    my table in word
    1. 215-AA-1234 (data entered manually)

    and the excel spreadsheet
    1          A                               B                               C                          D
    8          215-AA-1234              AC-9043                     Transmitter            5423698

    i want to write a VBA in word that can auto open the spread sheetin excel.  Use the data "215-AA-1234" from the table cell "1." to find the corresponding row "8" in the spreadsheet.  take the values for B, C & D from that row in spreadsheet and fill them into the cells "2, 3, & 4" from the word table.


    Friday, May 01, 2009 10:00 AM

All replies

  • Just to clarify - you have ten thousand Word documents and one Excel Workbook?
    Is there only one table on each Word document?
    Is all of the source data in the Workbook contained on one sheet?

    What you will be doing is opening the word doc, manually entering the data for the first column in the table, and then you would like to run a macro to populate the rest of the cells in the table with values from the workbook. - Is this correct?

    "The new phonebooks are here!"
    Tuesday, May 05, 2009 3:55 PM