Saving Word "form fields" in an Excel spreadsheet

[AMG]

I want to save my Word form fields in an Excel spreadsheet so that each time
someone inputs data and then hits SaveAs, the data is placed in the next line
in an Excel spreadsheet. I want to keep this data by date and then by month
so that my spreadsheets are labeld 0604_CMD_Reports, 0605_CMD_Reports, etc.
How do I get it to start a new spreadsheet every month and then keep all the
month's data in it's own file? The data would be all text files and numbers
which would not need any further manipulation.

 

[Jezebel]

You've got a couple of options depending on how much work you want to do vs.
how much coding.

First, experiment with the 'Save data only for forms' setting on Tools >
Options > Save. With this checked, your document saves the form field data
(and nothing else) in CSV format, which you can import directly into Excel.
While this won't automate your process, it doesn't need any code either,
which might make for less total effort.

To automate the process --

1. Get a reference to the workbook: create the filename from the current
date; check if the file exists already, open it if yes, create it if no.

2. Work out the row into which to add the data (eg UsedRange.Row +
UsedRange.Rows.Count + 1)

3. Read the formfields and insert them into the worksheet.


It makes life easier if the worksheet has defined names for the data
columns: then your Word VBA code is independent of the spreadsheet layout.
Use the same names for the Word formfields; then you can simply iterate the
formfields and use the name to determine worksheet column.