Pushing or Poking Word form data to Excel

[zeke]

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.

[Norie]

The only way I think something like this could be done would be using VBA.

This could be run from a button, or triggered by a document event like Close.

What the code would actually be would depend on exactly what, how and where you want the data transferred into Excel.

There could also be a problem using bookmarks as they will be deleted if text is entered in the document.

[fumei]

Excuse me, but why would bookmarks be "deleted if text is entered in the document"? There is no need for any deletions.

OK.
but there must be some kind of mapping or linkage between specfic fields / bookmarks and cell location references in the excel spreadsheet.Is this linkage required to be in both directions? In other words, if a change is made in the Excel file, does it need to be linked BACK to the Word file?

Or, are you meaning a link in terms of data, from the Word field to a specific cell in Excel. If so, then simply build a code that takes the value of your formfield (which ever ones you want), and put it into the cell in Excel.

You WILL need to name your formfields though. Most of your formfields do NOT have a name. I take it you did a copy and paste for some.

[Norie]

Gerry

What happens when you goto one of the bookmarks and type something?

It's deleted.

The OP doesn't actually have any formfields.

[fumei]

1. "goto one of the bookmarks and type something? It's deleted." Maybe so...but you ABSOLUTELY do not have to let that happen. You CAN go to a bookmark and not have it deleted. I write to bookmarks all the time, repeatedly, without deleting the bookmark.

2. Huh???? The document is completely formfields. They are ALL formfields. Are we looking at the same document?

[zeke]

Linkage is only in "1" direction. From a specific word form field to a specific excel row / column cell reference.

Others seem to indicate that using bookmarks will "not" work to reference these form fields - is this correct?



Excuse me, but why would bookmarks be "deleted if text is entered in the document"? There is no need for any deletions.

OK.Is this linkage required to be in both directions? In other words, if a change is made in the Excel file, does it need to be linked BACK to the Word file?

Or, are you meaning a link in terms of data, from the Word field to a specific cell in Excel. If so, then simply build a code that takes the value of your formfield (which ever ones you want), and put it into the cell in Excel.

You WILL need to name your formfields though. Most of your formfields do NOT have a name. I take it you did a copy and paste for some.

[Norie]

2. Huh???? The document is completely formfields. They are ALL formfields. Are we looking at the same document?
Gerry

Sorry about that, was getting a bit confused by the OP's initial mention of bookmarks and didn't check for form fields.:footinmout

It's been a while since I've done this sort of thing but you're right about the bookmarks too, I'm sure that's what I used previously.

[fumei]

Others seem to indicate that using bookmarks will "not" work to reference these form fields - is this correct? Let's get something clear.

Formfields are listed as bookmarks. You can see them in the Insert > Bookmark dialog. Information can be retrieved out of a formfield using the Bookmarks collection.

Information can ALSO be retrieved out of them (and easier) using the Formfields collection.

So. You CAN reference them using Bookmarks, but it is bette rto reference them as Formfields - which is what they are.

Again though...NAME them. All of them.

If you are running the code from Word, then you make an instance of Excel; retrieve the result of the formfield into a variable
Dim strClientImpact As String
strClientImpact = ActiveDocument.Formfields("ClientImpact").Result; then pass the variable to Excel