Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pushing and Poking data from a Word Form to Excel (Word 2003)

    We have a client who has created a Microsoft Word form template to capture risk investment information from managers in our organization (see attachment).

    Managers will fill this document in and return it to this client.

    The client would then like to "push or poke" the form data from the word template into an Excel spreadsheet.

    The excel spreadsheet will have a pre-defined format and specific form fields or bookmarks from the word document will populate the excel spreadsheet.

    My question - what is the best way to do this?

    The word form template will have some 60 fields. Not all of them will be pushed into the excel spreadsheet but there must be some kind of mapping or linkage between specfic fields / bookmarks and cell location references in the excel spreadsheet.

    Thanks again for any insight on this one.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pushing and Poking data from a Word Form to Excel (Word 2003)

    If you want to copy the contents of specific form field to specific cells in Excel, you must be able to refer to those form fields. Some form fields in your template have an associated bookmark, but many don't. You will have to assign a bookmark name to each form field whose contents you want to copy to Excel. It's best to assign meaningful names, this will make the code more readable.

    You can use Automation to open an Excel workbook in VBA code from Word and to populate it with values from the active document. See WendellB's tutorial Automation 101 for a general introduction to Automation.

    Code could look like this:

    Dim xlApp As New Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWsh As Excel.Worksheet
    Set xlWbk = xlApp.Workbooks.Open("C:ExcelMyWorkbook.xls")
    Set xlWsh = xlWbk.Worksheets(1)
    xlWsh.Range("C1") = ActiveDocument.FormFields("Title").Result
    xlWsh.Range("C2") = ActiveDocument.FormFields("External").Result
    ...
    xlWbk.Close SaveChanges:=True
    xlApp.Quit
    Set xlWsh = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

    You'll need to set a reference to the Microsoft Excel 11.0 Object Library in Tools | References... in the Visual Basic Editor.

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Pushing and Poking data from a Word Form to Excel (Word 2003)

    Although you can automate this to pull particular fields and ignore others with a huge amount of code there is an easier way. I think that the best way to do this is by exporting the completed data ONLY and then importing that into a row in your excel database.

    To get you started, complete a form in Word and then go into Tools > Options > Save and turn on the option to 'Save data only for forms'. When you save the file you will get a comma separated text file. This file can be imported into a spreadsheet easily (eg change the filename to .csv and double click)

    Although the csv doesn't map the specific fieldname to the data, you should be able to dip into the text file to select the right fields by their column position.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pushing and Poking data from a Word Form to Excel (Word 2003)

    Thanks so very much Hans.

    I have downloaded the Automation tutorial and the great auto2000.chm help file. The code snippets in this help file are exactly what I need to get started!

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •