Word forms have long been a valuable tool for collecting data, and while Word 2007 introduced the concept of Content Controls, there remains much to commend the old protected form with its legacy form fields.
For some time now this page had featured a set of macros that provided a means to extract the data from forms - especially a collection of similar forms returned by e-mail. Prompted by user feedback, I have used these old macros as a start point for the add-in now featured on this page.
The add-in will cater both for individual forms and for a collection of forms either saved to a Windows filing system folder or attached to e-mail messages. The add-in will handle forms that contain legacy form fields or content controls - but please, not in the same document!
The add-in was developed and tested with Office 2016, and makes use of Word, Excel and Outlook. It will however work equally well with Office 2007 (subject to provisos mentioned earlier), 2010 and later versions of those products. Where there's the option to use any one of these 2010 gives by far the best results.
The zip file (link at the bottom of the page) contains the add-in template for manual installation in the Word start-up folder, and an executable installer which will install the template in the Word startup folder and remove any older version. The installation should be made with Word closed.
When Word is started, correct installation will add a group to the Add-Ins tab of the ribbon featuring two buttons.
The right button provides access to a drop-down menu
The add-in stores a number of default values in the Windows registry. Given the range of options that the add-in provides for handling forms, a choice had to be made where to start, and I chose to set the startup option to process the active document.
The first time the application is run, and each time until the check box at the bottom of the application is unchecked, a disclaimer text is presented. Please read the text!
Click 'Continue to Configuration Settings' to complete the process.
At the top of the dialog are option buttons to select the type of field used in the form document(s) followed by options to select the three types of process available:
The above illustration shows the first option, the following illustrations show the alternatives:
Note that when the Extract Data from e-mailed attachments is selected, a further tabbed option becomes available.
When e-mailed forms prove to be incomplete the process will return those incomplete forms to the sender. The extra tabbed page provides the option to include a personalised covering text message. A standard message is included as a default, with no signature added.
Using the following simple form as an example:
The following illustration shows the results of the save to Excel. The field names are added at the top - here the default field names Text1 to Text6.
As the process is run as a background task, a process indicator is used to keep track of proceedings:
It was discovered during the re-development of the add-in that not only are content controls processed much faster than legacy form fields, but that Word versions after 2010 are very slow at processing form fields, yet there will be many business users who have sets of protected legacy forms for use in their commercial activities.
It can be very time consuming to redevelop a form with legacy form fields to content controls, so this process is designed to overcome that.
The process employs a simple user form
In order to convert the forms, any form protection must be removed. If the form is protected, the dialog expands for an optional password to be entered
The password is masked. If the password is incorrect the process ends.
Note the reference to 'global editors'. These relate to protecting forms with content controls and are described in the help file which can be accessed by clicking the ? button. This also links to further information about the use of content controls in forms on Greg's web site.
A progress bar, similar to the one described for the main process is used to indicate the status of the conversion process. This should only take a few seconds.
This process works with both content controls and legacy form fields and is intended to ensure that each field is uniquely named. In the case of a legacy form, this also means having a unique bookmark associated with the field (bookmarks of course must be unique).
In the case of content controls, the process also affords additional functions in respect of field tagging, to allow a common tag to be applied to a selection of fields, or individual tags.
Both versions will by default validate the names against Microsoft Access reserved keywords, should you wish to later import the Excel data into an Access table.
Having collected the data you probably want to do something with it, for example, create a report or letter.
For multiple letters, the Excel data file could be used as a datasource for a mail merge.
For users who want to create a report from a single form, I have added the facility to extract the data from each form field or content control in the current document and add the data to a new document created from a template of your choice, in the form of document variables named from the fields.
The Report template defaults to the Normal template, but you can use any other template if you prefer.
As an 'aide memoire' the document variables are also included as document variable fields at the end of the report document (as shown in the next illustration). If the named docvariables already exist in the template, the fields are not added at the end of the report, but the variables used in the report and any associated fields are updated as shown in the report example below.
When forms are batch processed, either from e-mailed messages or from filed documents, the process is logged in a Word document and the results presented on completion.
Word 2007 and later versions do not provide easy access to the legacy form fields and the protect/unprotect button. You could simply add the 'Lock' button from the All Commands group to the QAT (Quick Access Toolbar) but if you are working regularly with protected forms I would recommend that you investigate the forms toolbar add-in from Greg Maxey's web site. which reproduces the familiar forms toolbar from older Word versions in a readily accessible format.