VBA: Word To Excel Importer

The method below outlines a way of efficiently loading a information from a large number of formatted Word documents into a single Excel spreadsheet. This is useful if you have hundreds of forms that have been completed and you want to have all the responses in a single data table.

Part 1: Setting up MS Word form

For this to work you must set up form fields (grey boxes) in your word document. You do this using the Developer tab (search for text form fields for more info)

Part 2: Lock MS Word document

Make sure the document is restricted so people can only fill in these fields.

Part 3: Save documents in a single folder

Once all the Microsoft Word documents have been completed by the relevant parties save them in a single folder.

Part 4: Run the macro

Open up this linked Excel spreadsheet and follow the instructions. You need to tell it the folder where your Word documents are saved and the format they are saved in (.doc or .docx). Then press the button and off you go. Make sure you have macros enabled and that MS Word Objects are configured as outlined in the instructions on the spreadsheet. The contents of the form fields will then appear at the bottom of the spreadsheet.