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.

Quick Links

Details of freelance programming. Go back to the page describing the availability of bespoke programming services.

Example 1 - An example of importing data into Excel using VBA.

Example 3 - An example of adding REST functionailty to an ASP application to make API calls.



© Chestysoft, 2018.

function createCookie(name,value,days) { if (days) { var date = new Date(); date.setTime(date.getTime()+(days*24*60*60*1000)); var expires = "; expires="+date.toGMTString(); } else var expires = ""; document.cookie = name+"="+value+expires+"; path=/"; } function readCookie(name) { var nameEQ = name + "="; var ca = document.cookie.split(';'); for(var i=0;i < ca.length;i++) { var c = ca[i]; while (c.charAt(0)==' ') c = c.substring(1,c.length); if (c.indexOf(nameEQ) == 0) return c.substring(nameEQ.length,c.length); } return null; } function eraseCookie(name) { createCookie(name,"",-1); } function AcceptCookies() { createCookie("cookie-site","true",120); createCookie("cookie-tracking","true",120); createCookie("cookie-remarketing","true",120); document.getElementById("overlay").style.display = "none"; } ]]>


This site uses cookies for functionality, traffic analysis and for targeted advertising. Click the Accept button to accept our Cookie Policy. The Cookie Policy page offers configuration for a reduced set of cookies for this site.