Transfering data fields from Word to Excel (2007)

[chamdan]

Enclosed is a word document, which I nee to transfer all integrated fields that are actually bookmarks into a row in an Excel workbook/Worksheet. Each of the fields in sequence from top to the last one in the document have to be transferred into a column but same row using a macro that will be triggered once the last field is inputted.

Of course using VBA.

Never did it before but wishes some would help me achieve it.

Thanks in advance!



Chuck

[macropod]

Hi Chuck,

I note that your document uses a combination of formfields and table cells, some of which contain bookmarks. The data extraction would be far easier if you used formfields for all of the fields that require data extraction. As it stands, it is unlikely that any of the data would be enclosed within the relevant bookmarks, which are themselves prone to accidental deletion.

[fumei]

That and

Each of the fields in sequence from top to the last one in the document

is a little tricky, as bookmarks are listed alphabetically, which means if you really DO want a directionality (top to bottom) you have to explicitly name them.

However, macropod has a very good point. It would be very easy to put in content that would NOT be in the intended bookmark, and yes it is easy to delete them accidentally. Formfields are fairly robust data containers.

[chamdan]

Thanks for replying and proposing a better solution but even with form field I would be scratching my head to resolve it as I am novice in VBA and would certainly change all the fields to form field to suit what you could offer me as a soultion by giving me a macro that will do the required task.

Thanks again in advance for your help.

Chuck

[macropod]

Hi Chuck,

For some code to transfer form content from Word to Excel, see:
http://www.vbaexpress.com/forum/show...el-spreadsheet
Note that, although the primary macro there is for exporting content control data, my last post in that thread shows how to modify the code to work with formfields instead. The other important difference is that the code there is intended to be added to an Excel workbook and run on demand, rather than when a particular formfield is updated.

The problem with adding the code to a Word document (or its template) and triggering its execution from Word is that you then need to have extra checks to make sure all the required fields have been completed; otherwise, simply triggering the macro could lead to empty or partially-complete records in Excel. Even with that controlled you could still end up with duplicate records in Excel. Plus, even without the above considerations, the code to do this from Word is considerably more complicated, due to the need to check things like whether the destination workbook is already in use.

[fumei]

Paul may not agree with me - although his last post seems to have a similar thought - but GENERALLY it is better to pull things from Word (that is, the code is run from Excel) into Excel, than it is to push things (that is the code is run from Word) into Excel.

[chamdan]

Thanks Paul!

I will certainly have a look at the link you sent me and will get back to you and should it work propoerly as wanted I will consider it as solved and will update mt thread.

Cheers,

Chuck

[chamdan]

Thanks Paul!

The link you sent me works perfectly.

Cheers!

Chuck