A VBA macro to dynamically create a certificate in Word, taking data from Excel

This is an example of a real project requirement. It does not go into detail due to customer confidentiality issues and the purpose is to show how a macro can solve the problem not to demonstrate the coding techniques.

The requirement was to produce certificates showing completion of a training course where student and course details were stored in an Excel workbook. It could almost be done using Word's standard Mail Merge functionality, except the certificates needed to be saved as separate documents using their number as a file name.

A Word document was produced as a template with bookmarks to act as placeholders for the text which was to be added from the Excel file.

The solution was to:

1. Open the Excel file containing the data.
2. Loop through all the records (rows) in the data file.
3. Open the Word template.
4. For each field in the record, the name (column heading) matched the bookmark name in the Word template. Replace the bookmark text with the field value.
5. Save the resulting document using the certificate number as the file name.

The macro can be reused as required. The cost effectiveness of a solution like this depends on the number of times it will be run.