Transfer Word form data to Excel worksheet

[jhbmac]

Good morning everyone,

I have a project where a group of very non-technical people have the need to go paperless for a report that has approximately 75 fields. The field data will ultimately be inserted into a database. I have created an MS Word 2011 form that contains the fields and am trying to find an easier/less user interactive method to get the data to an Excel worksheet. An example I found used a VBA macro attached to the last field of the form using "execute on exit". The example uses MDAC ADODB.Connection and SQL structuring for Insert into "datasource". Since I cannot guarantee the OS, nor the version of Office being used I cannot guarantee MDAC would be available. I need to keep all of the "techieness" left behind the scenes. Ultimately I would like to build a set of instructions for the day to day use that says "create the new document", "fill in the data", "save the document", "email the following files to ……".

I haven't written any VB in over 15 years and an extremely small amount of VBA during the time I was writing VB. I know there has to be an easier way than using MDAC, but am unsure which is the easiest. The day to day users will not have the technical abilities to muddle their way through the creation of a text version of the Word document and then import the form data as CSV to Excel.

Any ideas?

Thanks in advance.

Jeff

[macropod]

Rather than having each document trying to push data to an Excel workbook that may be in use, you would do better to pull the data into Excel as and when required. For code to do that, see: http://www.vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet
In that thread, I have posted code for exporting data from content controls. As I don't know what Word version you're working with, I don't know whether you'll be using those or formfields. However, my last post in that thread also shows how to modify the code to work with formfields instead of content controls.

Note: You should not use content controls and formfields in the same document.