Generate Word Documents (in Excel VBA) from a series of Document Templates

[gaganji143]

Hey all. I'll try to make this brief and simple.
I have

1. 40 or so boilerplate word documents with a series of fields (Name, address, etc) that need to be filled in. This is historically done manually, but it's repetitive and cumbersome.
2. A workbook where a user has filled a huge set of information about an individual.

I need

• A way to programmatically (from Excel VBA) open up these boilerplate documents, edit in the value of fields from various named ranges in the workbook, and save the filled in templates to a local folder.

If I were using VBA to programmatically edit particular values in a set of spreadsheets, I would edit all those spreadsheets to contain a set of named ranges which could be used during the auto-fill process, but I'm not aware of any 'named field' feature in a Word document.
How could I edit the documents, and create a VBA routine, so that I can open each document, look for a set of fields which might need to be filled in, and substitute a value?
For instance, something that works like:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">foreach document in set_of_templates
if document.FieldExists("Name")then document.Field("Name").value = strName
if document.FieldExists("Address")then document.Field("Name").value = strAddress
...

document.saveAs( thisWorkbook.Path &"\GeneratedDocs\ "& document.Name )
next document</code>Things I've considered:

• Mail merge - but this is insufficient because it requires opening each document manually and structuring the workbook as a data source, I kind of want the opposite. The templates are the data source and the workbook is iterating through them. Also, mail merge is for creating many identical documents using a table of different data. I have many documents all using the same data.
• Using placeholder text such as "#NAME#" and opening each document for a search and replace. This is the solution I would resort to if nothing more elegant is proposed.

 

[GazNicki]

Have you considered setting up sheet 2 to display the required information via VLookup instead?

I have a training matrix I populate a report for based on the unique ID of the staff. The user can either enter the ID or simply choose from a dropdown menu.

Once you have the vlookup working, it may be quicker to have the 40 boilerplate templates as sheets perhaps?