VBA & Macro Problems

How to Create Multiple PDF Certificates in Excel Without Word Mail Merge

Maira Mehtab Sep 22, 2026 871 views

Question details

The user wants to generate personalized PDF certificates in bulk directly from an Excel spreadsheet using a VBA macro, avoiding the traditional Word Mail Merge process.

Product: Excel
Device & OS: not provided
Scenario: Generating multiple personalized PDF certificates for team members using spreadsheet data (names, dates, comments) and an integrated template.
Observed behavior: The goal is to automatically output individual PDF files named after each person using a self-contained, Excel-only solution.

Before you start

Ensure that you have enabled the Developer tab in Excel to access VBA features and that macros are allowed to run in your Trust Center settings.

Solution 1Recommended

Generate PDF Certificates using an Excel VBA Macro

Set up a data sheet and a template sheet within a single workbook, then run a VBA macro to loop through the data, populate the template, and export individual PDFs.

This method keeps the entire workflow within Excel. By avoiding Word Mail Merge, you create a standalone workbook that is much easier to share with team members.

Set up the Data Sheet

Create a worksheet named 'Data'. Add columns for the person's Name, Date, and Comment. In a specific cell (e.g., F2), enter the destination folder path where the PDFs should be saved.

Design the Certificate Template

Create a second worksheet named 'Certificate'. Design your certificate layout here. The VBA macro will dynamically update specific cells in this sheet with data from the 'Data' sheet.

Open the VBA Editor

Press ALT + F11 to open the Visual Basic for Applications (VBA) Editor. Click 'Insert' > 'Module' to create a blank script window.

Write the Loop and Export Script

Write a VBA script that loops through each row of the 'Data' sheet. For each row, the script should copy the Name, Date, and Comment to the respective cells in the 'Certificate' sheet, then use the ExportAsFixedFormat method to save the 'Certificate' sheet as xlTypePDF.

Set Dynamic File Names and Run

In your code, set the export filename to combine the destination folder path, the person's Name from the current row, and the suffix '-Certificate.pdf'. Close the editor and run the macro to generate the files.

Macro-Enabled Workbook: Remember to save your file as an Excel Macro-Enabled Workbook (.xlsm) so your VBA code is preserved for future use.

Efficient Spreadsheet Management

Generate Bulk Certificates with WPS Spreadsheet VBA

WPS Office offers a powerful built-in VBA editor in its professional and macro-enabled versions, along with seamless native PDF exporting capabilities. This makes it perfectly equipped to handle bulk certificate generation directly from your spreadsheets.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your prepared .xlsm workbook containing the data and template sheets.
2. Access the VBA Editor: Navigate to the 'Developer' or 'Tools' tab and click on 'VBA Editor' to access your macro.
3. Run the Macro: Execute the certificate generation macro to instantly produce and save bulk PDFs to your destination folder.

Fully compatible with Microsoft Excel macro (.xlsm) filesBuilt-in, high-quality PDF conversion toolsLightweight application that runs smoothly on almost any deviceAll-in-one suite for document, spreadsheet, and presentation management

microsoft office alternative - wps office

Frequently Asked Questions

Why use Excel VBA instead of Word Mail Merge for certificates?

Using Excel VBA keeps the entire process within a single file. This makes it significantly easier to share with team members, as they only need to open one workbook, paste their data, and run the macro without managing links between separate Word and Excel documents.

How do I save an Excel sheet as a PDF using VBA?

You can save a sheet as a PDF by calling the ExportAsFixedFormat method in your VBA code. Use the syntax `SheetName.ExportAsFixedFormat Type:=xlTypePDF, Filename:=YourFilePath` to execute the export.

Can I customize the PDF file names generated by the macro?

Yes, within your VBA loop, you can dynamically build the file path string by concatenating the destination folder, the person's name extracted from the data row, and your desired text string, such as '-Certificate.pdf'.

What should I do if the macro fails to save the PDFs?

First, verify that the destination folder path specified in your data sheet actually exists and ends with a backslash (\). Additionally, check that you have write permissions to that folder and that macros are fully enabled in your spreadsheet's security settings.

Excel & Data Problems