Pushing or Poking Word form data to an Excel spreadsheet

[JohnF]

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

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 2003 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.

 

[Jezebel]

1. Get a reference to Excel and open the workbook --

Dim pxlApp as Excel.Application
Dim pxlBook as Excel.Workbook

Set pxlApp = Excel.Application
Set pxlBook = pxlApp.Wordbooks.Open(FileName:=....)


2. Iterate the formfields in the document and insert the values into Excel.
Dim pFormField as Word.Formfield

For each pFormField in ActiveDocument.FormFields
pxlBook.Sheets([datasheet name]).Cells(Row,Column) = pFormField.Result
Next

How you set the row and column obviously depends on how you set up the
workbook. If each form becomes a new row in the workbook, name the target
columns using the same names as used for the Word formfields. Check
worksheet UsedRange property to get the last used row, then add one.