Document Generation

How to Create Batch Certificates from Excel and Word Templates

Turn approved Excel rows into individually named certificate DOCX/PDF pairs from one controlled Word template—without copying, renaming, or retyping each certificate by hand.

One Excel row
One Word template
One signature asset
DOCX + PDF
Treat certificate IDs, names, dates, signatures, filenames, and release status as controlled data—not informal text pasted into Word.

Quick answer: use one row, one template, and one release status

Create a Certificates sheet with one independent recipient per row. Keep the certificate layout, static logo, typography, and fixed wording in Templates\CertificateTemplate.docx. Mark only approved rows READY, generate them through a fresh read-only opening of the template, review the resulting DOCX/PDF pair, and release it under your normal approval process.

Scope of this workflow

Use it for training, membership, attendance, completion, compliance, or event certificates that share one standard layout. The narrower school workflow—student certificates plus student letters—belongs in the separate school certificates and letters guide.

Define the Excel certificate contract before opening Word

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create a sheet named Certificates and use these exact headers in row 1.

A — CertificateID

Stable business identifier stored as text. It must be unique among READY rows; format the column as Text before importing IDs with leading zeros.

B — RecipientName

The reviewed recipient name exactly as it should appear on the certificate. Do not silently trim or normalize it in the generation step.

C — ProgramName

The course, award, membership, program, or completion title shown in the document.

D — CompletionDateDisplay

A document-ready text value such as 18 November 2026, not an Excel date serial that depends on number formatting.

E — IssuerName

The approved organization, department, instructor, or issuing authority printed on the certificate.

F — SignatureFile

A PNG, JPG, or JPEG filename from Assets\Signatures. Use a filename only—never a full path or wildcard.

G — OutputName

A unique Windows-safe filename stem. Do not include .docx, .pdf, path separators, trailing periods, or reserved device names.

H — CertificateStatus

Use exactly READY, HOLD, or DONE. Only READY rows enter the current batch.

Example row

CERT-0042 | Jordan Lee | Workplace Safety | 18 November 2026 | Northwind Training | director.png | Certificate_CERT-0042 | READY

Build one approved Word certificate template

Put the template at Templates\CertificateTemplate.docx. Use six required Word bookmarks and keep every text bookmark uniformly formatted so replacement does not need to preserve mixed character formatting inside one placeholder.

CertificateID

Place the traceable identifier where a reviewer can compare it with Excel and the output filename.

RecipientName

Reserve enough space for realistic long names and test line wrapping before approving the template.

ProgramName

Test the longest expected program or award title rather than designing around a short sample.

CompletionDateDisplay

The bookmark receives the already formatted date text from Excel.

IssuerName

Keep the authority name separate from the signature image so both can be reviewed.

SignatureImage

Use an otherwise empty bookmark for one approved image. The macro embeds it and shrinks it to fit without enlarging a small source.

Static versus variable artwork

Embed the common logo, borders, seals, and background design directly in the template. Use a row-level image only when the approved signature really varies by certificate. An image is not a digital signature or proof of authorization.

Run the batch through six release gates

1. Approve source rows

Resolve spelling, identity, program, date, issuer, signature, and output-name questions before changing a row to READY.

2. Preflight the whole batch

Check exact headers, data types, statuses, duplicate IDs, duplicate output names, signature files, template location, and workbook write access before Word starts.

3. Freeze the run inputs

Copy the approved template and every unique signature used by READY rows into the run’s InputSnapshot, then validate and generate only from those copies.

4. Generate from a fresh snapshot copy

Open a new read-only instance of the template snapshot for every row, write the five text bookmarks, embed the snapshotted signature, and save staged DOCX/PDF files.

5. Move the verified pair

Confirm both staged files exist and are nonempty, then move the pair into the run’s Completed folder without overwriting prior output.

6. Review before release

Inspect recipient identity, certificate ID, wrapping, signature authorization, logo, fonts, pagination, PDF appearance, and the saved Excel status.

Free Excel VBA example: generate guarded certificate pairs

Place the code in a standard Excel VBA module and run GenerateCertificateBatch. The example is intentionally strict: every READY value must be stored as text, the workbook must be writable, the template and signature assets must exist, and duplicate IDs or output names stop the batch before Word starts.

Preflight and input snapshot

The macro checks the full batch, saves the workbook, creates a unique run folder, and copies the approved template plus each unique required signature into InputSnapshot before Word starts.

Fresh snapshot template per certificate

Each READY row opens the controlled template copy read-only, restores every bookmark after replacement, embeds its controlled signature copy, and creates both output formats.

Strict row recheck

Immediately before generation, the macro compares the live A–H row with the preflight snapshot and stops if any value or type changed.

Retained recovery evidence

The error path closes Word but does not delete the input snapshot, previously completed files, or the current run folder. Review retained evidence before deciding what to rerun.

Excel VBA — batch certificates from one Word templateStatic example
Option Explicit

Private Const DATA_SHEET As String = "Certificates"
Private Const READY_STATUS As String = "READY"
Private Const HOLD_STATUS As String = "HOLD"
Private Const DONE_STATUS As String = "DONE"
Private Const WORD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WORD_EXPORT_FORMAT_PDF As Long = 17
Private Const WORD_EXPORT_OPTIMIZE_FOR_PRINT As Long = 0
Private Const WORD_EXPORT_ALL_DOCUMENT As Long = 0
Private Const WORD_EXPORT_DOCUMENT_CONTENT As Long = 0
Private Const WORD_EXPORT_CREATE_NO_BOOKMARKS As Long = 0
Private Const WORD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WORD_ALERTS_NONE As Long = 0
Private Const WORD_COLLAPSE_START As Long = 1
Private Const OFFICE_TRUE As Long = -1
Private Const POINTS_PER_CM As Double = 28.3464567
Private Const MAX_SIGNATURE_WIDTH_CM As Double = 4.5
Private Const MAX_SIGNATURE_HEIGHT_CM As Double = 1.8

Public Sub GenerateCertificateBatch()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim fso As Object
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim templatePath As String
    Dim signatureFolder As String
    Dim outputRoot As String
    Dim runFolder As String
    Dim inputSnapshotFolder As String
    Dim signatureSnapshotFolder As String
    Dim templateSnapshotPath As String
    Dim sourceSignaturePath As String
    Dim snapshotSignaturePath As String
    Dim stagingFolder As String
    Dim completedFolder As String
    Dim tempDocxPath As String
    Dim tempPdfPath As String
    Dim finalDocxPath As String
    Dim finalPdfPath As String
    Dim data As Variant
    Dim signaturePaths() As String
    Dim lastRow As Long
    Dim dataRow As Long
    Dim excelRow As Long
    Dim readyCount As Long
    Dim generatedCount As Long
    Dim certificateId As String
    Dim recipientName As String
    Dim programName As String
    Dim completionDateDisplay As String
    Dim issuerName As String
    Dim signatureFile As String
    Dim outputName As String
    Dim certificateStatus As String
    Dim certificateIds As Object
    Dim outputNames As Object
    Dim signatureSnapshots As Object
    Dim previousScreenUpdating As Boolean
    Dim previousEnableEvents As Boolean
    Dim appStateCaptured As Boolean
    Dim currentStep As String
    Dim errorNumber As Long
    Dim errorDescription As String

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    previousScreenUpdating = Application.ScreenUpdating
    previousEnableEvents = Application.EnableEvents
    appStateCaptured = True
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    currentStep = "checking the macro-enabled workbook"
    RequireWritableMacroWorkbook wb
    Set ws = wb.Worksheets(DATA_SHEET)
    If ws.ProtectContents Then
        Err.Raise vbObjectError + 5000, , _
            "The Certificates sheet is protected against status updates."
    End If
    ValidateHeaders ws
    lastRow = FindLastDataRow(ws)
    If lastRow < 2 Then
        Err.Raise vbObjectError + 5001, , _
            "No certificate records were found below row 1."
    End If

    templatePath = JoinPath(JoinPath(wb.Path, "Templates"), _
        "CertificateTemplate.docx")
    signatureFolder = JoinPath(JoinPath(wb.Path, "Assets"), _
        "Signatures")
    outputRoot = JoinPath(wb.Path, "GeneratedCertificates")
    RequireNonemptyFile templatePath, "certificate template"
    RequireFolder signatureFolder, "signature asset folder"

    data = ws.Range("A2:H" & lastRow).Value2
    ReDim signaturePaths(1 To UBound(data, 1))
    Set certificateIds = CreateObject("Scripting.Dictionary")
    certificateIds.CompareMode = vbTextCompare
    Set outputNames = CreateObject("Scripting.Dictionary")
    outputNames.CompareMode = vbTextCompare

    currentStep = "preflighting Excel certificate rows"
    For dataRow = 1 To UBound(data, 1)
        excelRow = dataRow + 1
        If Not DataRowIsBlank(data, dataRow) Then
            certificateStatus = ReadRequiredText(data(dataRow, 8), _
                "CertificateStatus", excelRow)
            ValidateStatus certificateStatus, excelRow

            If StrComp(certificateStatus, READY_STATUS, _
               vbBinaryCompare) = 0 Then
                certificateId = ReadRequiredText(data(dataRow, 1), _
                    "CertificateID", excelRow)
                recipientName = ReadRequiredText(data(dataRow, 2), _
                    "RecipientName", excelRow)
                programName = ReadRequiredText(data(dataRow, 3), _
                    "ProgramName", excelRow)
                completionDateDisplay = ReadRequiredText(data(dataRow, 4), _
                    "CompletionDateDisplay", excelRow)
                issuerName = ReadRequiredText(data(dataRow, 5), _
                    "IssuerName", excelRow)
                signatureFile = ReadRequiredText(data(dataRow, 6), _
                    "SignatureFile", excelRow)
                outputName = ReadRequiredText(data(dataRow, 7), _
                    "OutputName", excelRow)

                ValidateIdentifier certificateId, "CertificateID", excelRow
                ValidateFileStem outputName, excelRow
                ValidateImageFileName signatureFile, excelRow
                signaturePaths(dataRow) = JoinPath(signatureFolder, signatureFile)
                RequireNonemptyFile signaturePaths(dataRow), _
                    "signature image for row " & excelRow

                AddUniqueValue certificateIds, certificateId, _
                    "CertificateID", excelRow
                AddUniqueValue outputNames, outputName, _
                    "OutputName", excelRow
                readyCount = readyCount + 1
            End If
        End If
    Next dataRow

    If readyCount = 0 Then
        Err.Raise vbObjectError + 5002, , _
            "No certificate rows are marked exactly READY."
    End If

    currentStep = "saving the preflighted Excel workbook"
    wb.Save

    Set fso = CreateObject("Scripting.FileSystemObject")
    EnsureFolder outputRoot
    runFolder = CreateRunFolder(fso, outputRoot)
    inputSnapshotFolder = JoinPath(runFolder, "InputSnapshot")
    signatureSnapshotFolder = JoinPath(inputSnapshotFolder, "Signatures")
    stagingFolder = JoinPath(runFolder, "Staging")
    completedFolder = JoinPath(runFolder, "Completed")
    fso.CreateFolder inputSnapshotFolder
    fso.CreateFolder signatureSnapshotFolder
    fso.CreateFolder stagingFolder
    fso.CreateFolder completedFolder

    currentStep = "creating the controlled input snapshot"
    templateSnapshotPath = JoinPath(inputSnapshotFolder, _
        "CertificateTemplate.docx")
    CopyFileToSnapshot fso, templatePath, templateSnapshotPath, _
        "certificate template"
    Set signatureSnapshots = CreateObject("Scripting.Dictionary")
    signatureSnapshots.CompareMode = vbTextCompare
    For dataRow = 1 To UBound(data, 1)
        certificateStatus = CStr(data(dataRow, 8))
        If StrComp(certificateStatus, READY_STATUS, _
           vbBinaryCompare) = 0 Then
            signatureFile = CStr(data(dataRow, 6))
            If Not signatureSnapshots.Exists(signatureFile) Then
                sourceSignaturePath = signaturePaths(dataRow)
                snapshotSignaturePath = JoinPath( _
                    signatureSnapshotFolder, signatureFile)
                CopyFileToSnapshot fso, sourceSignaturePath, _
                    snapshotSignaturePath, _
                    "signature image " & signatureFile
                signatureSnapshots.Add signatureFile, _
                    snapshotSignaturePath
            End If
            signaturePaths(dataRow) = signatureSnapshots(signatureFile)
        End If
    Next dataRow

    currentStep = "starting Microsoft Word Desktop"
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False
    wdApp.DisplayAlerts = WORD_ALERTS_NONE
    wdApp.ScreenUpdating = False

    currentStep = "validating the certificate template snapshot"
    Set wdDoc = wdApp.Documents.Open( _
        templateSnapshotPath, False, True, False)
    ValidateCertificateTemplate wdDoc
    wdDoc.Close WORD_DO_NOT_SAVE_CHANGES
    Set wdDoc = Nothing

    For dataRow = 1 To UBound(data, 1)
        excelRow = dataRow + 1
        certificateStatus = CStr(data(dataRow, 8))
        If StrComp(certificateStatus, READY_STATUS, _
           vbBinaryCompare) = 0 Then
            currentStep = "rechecking Excel row " & excelRow
            RequireRowUnchanged ws, excelRow, data, dataRow

            certificateId = CStr(data(dataRow, 1))
            recipientName = CStr(data(dataRow, 2))
            programName = CStr(data(dataRow, 3))
            completionDateDisplay = CStr(data(dataRow, 4))
            issuerName = CStr(data(dataRow, 5))
            outputName = CStr(data(dataRow, 7))

            tempDocxPath = JoinPath(stagingFolder, outputName & ".docx")
            tempPdfPath = JoinPath(stagingFolder, outputName & ".pdf")
            finalDocxPath = JoinPath(completedFolder, outputName & ".docx")
            finalPdfPath = JoinPath(completedFolder, outputName & ".pdf")
            RequireTargetAbsent tempDocxPath
            RequireTargetAbsent tempPdfPath
            RequireTargetAbsent finalDocxPath
            RequireTargetAbsent finalPdfPath

            currentStep = "generating certificate row " & excelRow
            Set wdDoc = wdApp.Documents.Open( _
                templateSnapshotPath, False, True, False)
            WriteRequiredBookmark wdDoc, "CertificateID", certificateId
            WriteRequiredBookmark wdDoc, "RecipientName", recipientName
            WriteRequiredBookmark wdDoc, "ProgramName", programName
            WriteRequiredBookmark wdDoc, "CompletionDateDisplay", _
                completionDateDisplay
            WriteRequiredBookmark wdDoc, "IssuerName", issuerName
            InsertSignatureBookmark wdDoc, "SignatureImage", _
                signaturePaths(dataRow)
            ValidateCertificateResultBeforeSave wdDoc

            wdDoc.SaveAs2 tempDocxPath, WORD_FORMAT_XML_DOCUMENT
            wdDoc.ExportAsFixedFormat _
                tempPdfPath, _
                WORD_EXPORT_FORMAT_PDF, _
                False, _
                WORD_EXPORT_OPTIMIZE_FOR_PRINT, _
                WORD_EXPORT_ALL_DOCUMENT, _
                1, _
                1, _
                WORD_EXPORT_DOCUMENT_CONTENT, _
                True, _
                True, _
                WORD_EXPORT_CREATE_NO_BOOKMARKS, _
                True, _
                True, _
                False
            wdDoc.Close WORD_DO_NOT_SAVE_CHANGES
            Set wdDoc = Nothing

            RequireNonemptyFile tempDocxPath, _
                "staged DOCX for row " & excelRow
            RequireNonemptyFile tempPdfPath, _
                "staged PDF for row " & excelRow
            RequireTargetAbsent finalDocxPath
            RequireTargetAbsent finalPdfPath
            Name tempDocxPath As finalDocxPath
            Name tempPdfPath As finalPdfPath

            ws.Cells(excelRow, 8).Value2 = DONE_STATUS
            wb.Save
            generatedCount = generatedCount + 1
        End If
    Next dataRow

    wdApp.NormalTemplate.Saved = True
    wdApp.Quit WORD_DO_NOT_SAVE_CHANGES
    Set wdApp = Nothing

    Application.EnableEvents = previousEnableEvents
    Application.ScreenUpdating = previousScreenUpdating
    appStateCaptured = False

    MsgBox "Certificate batch completed." & vbCrLf & _
        "Certificates generated: " & generatedCount & vbCrLf & _
        "Completed folder: " & completedFolder & vbCrLf & vbCrLf & _
        "Review the DOCX/PDF files and signature authorization before release.", _
        vbInformation

CleanExit:
    On Error Resume Next
    If Not wdDoc Is Nothing Then _
        wdDoc.Close WORD_DO_NOT_SAVE_CHANGES
    If Not wdApp Is Nothing Then
        wdApp.NormalTemplate.Saved = True
        wdApp.Quit WORD_DO_NOT_SAVE_CHANGES
    End If
    If appStateCaptured Then
        Application.EnableEvents = previousEnableEvents
        Application.ScreenUpdating = previousScreenUpdating
    End If
    Set wdDoc = Nothing
    Set wdApp = Nothing
    Set fso = Nothing
    Set signatureSnapshots = Nothing
    Set ws = Nothing
    Set wb = Nothing
    On Error GoTo 0
    Exit Sub

CleanFail:
    errorNumber = Err.Number
    errorDescription = Err.Description
    On Error Resume Next
    If Not wdDoc Is Nothing Then _
        wdDoc.Close WORD_DO_NOT_SAVE_CHANGES
    If Not wdApp Is Nothing Then
        wdApp.NormalTemplate.Saved = True
        wdApp.Quit WORD_DO_NOT_SAVE_CHANGES
    End If
    If appStateCaptured Then
        Application.EnableEvents = previousEnableEvents
        Application.ScreenUpdating = previousScreenUpdating
    End If
    Set wdDoc = Nothing
    Set wdApp = Nothing
    Set fso = Nothing
    Set signatureSnapshots = Nothing
    Set ws = Nothing
    Set wb = Nothing
    On Error GoTo 0

    MsgBox "Certificate generation stopped while " & currentStep & "." & _
        vbCrLf & "Error " & errorNumber & ": " & errorDescription & _
        vbCrLf & IIf(Len(runFolder) > 0, _
            "Inspect the retained run folder: " & runFolder, _
            "No run folder was created.") & vbCrLf & _
        "Existing completed files were not deleted.", vbCritical
End Sub

Private Sub CopyFileToSnapshot(ByVal fso As Object, _
                               ByVal sourcePath As String, _
                               ByVal snapshotPath As String, _
                               ByVal fileLabel As String)
    Dim sourceSize As Variant
    Dim sourceModified As Date
    Dim errorDescription As String

    RequireNonemptyFile sourcePath, fileLabel
    RequireTargetAbsent snapshotPath
    On Error GoTo CopyFailed
    sourceSize = fso.GetFile(sourcePath).Size
    sourceModified = fso.GetFile(sourcePath).DateLastModified
    fso.CopyFile sourcePath, snapshotPath, False
    RequireNonemptyFile snapshotPath, fileLabel & " snapshot"
    If fso.GetFile(snapshotPath).Size <> sourceSize Then
        Err.Raise vbObjectError + 5046, , _
            "Snapshot size differs from the source " & fileLabel & "."
    End If
    If fso.GetFile(sourcePath).Size <> sourceSize Or _
       fso.GetFile(sourcePath).DateLastModified <> sourceModified Then
        Err.Raise vbObjectError + 5047, , _
            "The source " & fileLabel & _
            " changed while its run snapshot was being created."
    End If
    Exit Sub
CopyFailed:
    errorDescription = Err.Description
    Err.Raise vbObjectError + 5048, , _
        "Could not create the controlled " & fileLabel & _
        " snapshot: " & errorDescription
End Sub

Private Sub RequireWritableMacroWorkbook(ByVal wb As Workbook)
    If Len(wb.Path) = 0 Then
        Err.Raise vbObjectError + 5003, , _
            "Save the Excel workbook before generation."
    End If
    If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
        Err.Raise vbObjectError + 5004, , _
            "Save the workbook as an Excel Macro-Enabled Workbook (.xlsm)."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 5005, , _
            "The Excel workbook is read-only; DONE statuses cannot be saved."
    End If
End Sub

Private Sub ValidateHeaders(ByVal ws As Worksheet)
    Dim expected As Variant
    Dim columnIndex As Long
    Dim actualText As String

    expected = Array("CertificateID", "RecipientName", "ProgramName", _
        "CompletionDateDisplay", "IssuerName", "SignatureFile", _
        "OutputName", "CertificateStatus")
    For columnIndex = 1 To 8
        If IsError(ws.Cells(1, columnIndex).Value2) Then
            Err.Raise vbObjectError + 5006, , _
                "Header cell " & ws.Cells(1, columnIndex).Address(False, False) & _
                " contains an Excel error."
        End If
        If VarType(ws.Cells(1, columnIndex).Value2) <> vbString Then
            Err.Raise vbObjectError + 5007, , _
                "Header cell " & ws.Cells(1, columnIndex).Address(False, False) & _
                " must contain exact text."
        End If
        actualText = CStr(ws.Cells(1, columnIndex).Value2)
        If StrComp(actualText, CStr(expected(columnIndex - 1)), _
           vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 5008, , _
                "Expected header " & expected(columnIndex - 1) & _
                " in column " & columnIndex & ", found " & actualText & "."
        End If
    Next columnIndex
End Sub

Private Function FindLastDataRow(ByVal ws As Worksheet) As Long
    Dim lastCell As Range

    Set lastCell = ws.Range("A:H").Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
    If lastCell Is Nothing Then
        FindLastDataRow = 0
    Else
        FindLastDataRow = lastCell.Row
    End If
End Function

Private Function DataRowIsBlank(ByRef data As Variant, _
                                ByVal dataRow As Long) As Boolean
    Dim columnIndex As Long

    DataRowIsBlank = True
    For columnIndex = 1 To 8
        If IsError(data(dataRow, columnIndex)) Then
            DataRowIsBlank = False
            Exit Function
        End If
        If Len(CStr(data(dataRow, columnIndex))) > 0 Then
            DataRowIsBlank = False
            Exit Function
        End If
    Next columnIndex
End Function

Private Function ReadRequiredText(ByVal rawValue As Variant, _
                                  ByVal fieldName As String, _
                                  ByVal excelRow As Long) As String
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 5009, , _
            fieldName & " contains an Excel error on row " & excelRow & "."
    End If
    If VarType(rawValue) <> vbString Then
        Err.Raise vbObjectError + 5010, , _
            fieldName & " must be stored as text on row " & excelRow & "."
    End If
    ReadRequiredText = CStr(rawValue)
    If Len(ReadRequiredText) = 0 Then
        Err.Raise vbObjectError + 5011, , _
            fieldName & " is blank on row " & excelRow & "."
    End If
    If ReadRequiredText <> Trim$(ReadRequiredText) Then
        Err.Raise vbObjectError + 5012, , _
            fieldName & " has outer whitespace on row " & excelRow & "."
    End If
    If ContainsControlCharacter(ReadRequiredText) Then
        Err.Raise vbObjectError + 5013, , _
            fieldName & " contains a control character on row " & excelRow & "."
    End If
End Function

Private Sub ValidateStatus(ByVal statusText As String, _
                           ByVal excelRow As Long)
    If StrComp(statusText, READY_STATUS, vbBinaryCompare) <> 0 And _
       StrComp(statusText, HOLD_STATUS, vbBinaryCompare) <> 0 And _
       StrComp(statusText, DONE_STATUS, vbBinaryCompare) <> 0 Then
        Err.Raise vbObjectError + 5014, , _
            "CertificateStatus must be exactly READY, HOLD, or DONE on row " & _
            excelRow & "."
    End If
End Sub

Private Sub ValidateIdentifier(ByVal identifierText As String, _
                               ByVal fieldName As String, _
                               ByVal excelRow As Long)
    If Len(identifierText) > 80 Then
        Err.Raise vbObjectError + 5015, , _
            fieldName & " exceeds 80 characters on row " & excelRow & "."
    End If
End Sub

Private Sub AddUniqueValue(ByVal valuesSeen As Object, _
                           ByVal valueText As String, _
                           ByVal fieldName As String, _
                           ByVal excelRow As Long)
    If valuesSeen.Exists(valueText) Then
        Err.Raise vbObjectError + 5016, , _
            "Duplicate " & fieldName & " on rows " & _
            valuesSeen(valueText) & " and " & excelRow & ": " & valueText
    End If
    valuesSeen.Add valueText, excelRow
End Sub

Private Sub ValidateImageFileName(ByVal fileName As String, _
                                  ByVal excelRow As Long)
    Dim extensionPosition As Long
    Dim extensionText As String
    Dim forbidden As Variant
    Dim item As Variant

    forbidden = Array("<", ">", ":", Chr$(34), "/", "\", "|", "?", "*")
    For Each item In forbidden
        If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
            Err.Raise vbObjectError + 5017, , _
                "SignatureFile contains a forbidden path character on row " & _
                excelRow & "."
        End If
    Next item
    If Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
        Err.Raise vbObjectError + 5018, , _
            "SignatureFile cannot end with a period or space on row " & _
            excelRow & "."
    End If
    extensionPosition = InStrRev(fileName, ".")
    If extensionPosition <= 1 Then
        Err.Raise vbObjectError + 5019, , _
            "SignatureFile needs a PNG, JPG, or JPEG extension on row " & _
            excelRow & "."
    End If
    extensionText = UCase$(Mid$(fileName, extensionPosition + 1))
    If extensionText <> "PNG" And extensionText <> "JPG" And _
       extensionText <> "JPEG" Then
        Err.Raise vbObjectError + 5020, , _
            "SignatureFile must use PNG, JPG, or JPEG on row " & excelRow & "."
    End If
End Sub

Private Sub ValidateFileStem(ByVal fileStem As String, _
                             ByVal excelRow As Long)
    Dim invalidCharacters As Variant
    Dim invalidCharacter As Variant
    Dim baseName As String
    Dim lowerName As String

    If Len(fileStem) > 120 Then
        Err.Raise vbObjectError + 5021, , _
            "OutputName exceeds 120 characters on row " & excelRow & "."
    End If
    lowerName = LCase$(fileStem)
    If Right$(lowerName, 5) = ".docx" Or _
       Right$(lowerName, 4) = ".pdf" Then
        Err.Raise vbObjectError + 5049, , _
            "OutputName must not include a .docx or .pdf extension on row " & _
            excelRow & "."
    End If
    invalidCharacters = Array("<", ">", ":", Chr$(34), "/", "\", _
        "|", "?", "*")
    For Each invalidCharacter In invalidCharacters
        If InStr(1, fileStem, CStr(invalidCharacter), vbBinaryCompare) > 0 Then
            Err.Raise vbObjectError + 5022, , _
                "OutputName contains a forbidden Windows character on row " & _
                excelRow & "."
        End If
    Next invalidCharacter
    If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
        Err.Raise vbObjectError + 5023, , _
            "OutputName cannot end with a period or space on row " & _
            excelRow & "."
    End If
    baseName = fileStem
    If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
        baseName = Left$(baseName, InStr(1, baseName, ".", _
            vbBinaryCompare) - 1)
    End If
    If IsReservedWindowsName(baseName) Then
        Err.Raise vbObjectError + 5024, , _
            "OutputName uses a reserved Windows name on row " & excelRow & "."
    End If
End Sub

Private Sub ValidateCertificateTemplate(ByVal doc As Object)
    RejectTrackedChanges doc, "certificate template snapshot"
    If doc.Comments.Count > 0 Then
        Err.Raise vbObjectError + 5026, , _
            "Remove comments from CertificateTemplate.docx."
    End If
    RequireBookmark doc, "CertificateID"
    RequireBookmark doc, "RecipientName"
    RequireBookmark doc, "ProgramName"
    RequireBookmark doc, "CompletionDateDisplay"
    RequireBookmark doc, "IssuerName"
    RequireBookmark doc, "SignatureImage"
End Sub

Private Sub ValidateCertificateResultBeforeSave(ByVal doc As Object)
    RejectTrackedChanges doc, "generated certificate"
    If doc.Comments.Count > 0 Then
        Err.Raise vbObjectError + 5050, , _
            "The generated certificate contains a comment before saving."
    End If
    RequireBookmark doc, "CertificateID"
    RequireBookmark doc, "RecipientName"
    RequireBookmark doc, "ProgramName"
    RequireBookmark doc, "CompletionDateDisplay"
    RequireBookmark doc, "IssuerName"
    RequireBookmark doc, "SignatureImage"
End Sub

Private Sub RejectTrackedChanges(ByVal doc As Object, _
                                 ByVal documentLabel As String)
    If doc.TrackRevisions Then
        Err.Raise vbObjectError + 5051, , _
            "Turn off Track Changes in the " & documentLabel & "."
    End If
    If CountRevisionsInAllStories(doc) > 0 Then
        Err.Raise vbObjectError + 5052, , _
            "Resolve tracked revisions in every story of the " & _
            documentLabel & "."
    End If
End Sub

Private Function CountRevisionsInAllStories(ByVal doc As Object) As Long
    Dim storyRoot As Object
    Dim currentStory As Object
    Dim revisionCount As Long

    For Each storyRoot In doc.StoryRanges
        Set currentStory = storyRoot
        Do While Not currentStory Is Nothing
            revisionCount = revisionCount + currentStory.Revisions.Count
            Set currentStory = currentStory.NextStoryRange
        Loop
    Next storyRoot
    CountRevisionsInAllStories = revisionCount
End Function

Private Sub RequireBookmark(ByVal doc As Object, _
                            ByVal bookmarkName As String)
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 5027, , _
            "Missing required Word bookmark: " & bookmarkName
    End If
End Sub

Private Sub WriteRequiredBookmark(ByVal doc As Object, _
                                  ByVal bookmarkName As String, _
                                  ByVal replacementText As String)
    Dim targetRange As Object
    Dim styleValue As Variant

    RequireBookmark doc, bookmarkName
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    If targetRange.Fields.Count > 0 Or _
       targetRange.ContentControls.Count > 0 Or _
       targetRange.InlineShapes.Count > 0 Then
        Err.Raise vbObjectError + 5028, , _
            "Text bookmark contains a field, content control, or image: " & _
            bookmarkName
    End If
    If InStr(1, targetRange.Text, vbCr, vbBinaryCompare) > 0 Or _
       InStr(1, targetRange.Text, Chr$(7), vbBinaryCompare) > 0 Then
        Err.Raise vbObjectError + 5029, , _
            "Text bookmark includes a paragraph or cell marker: " & bookmarkName
    End If
    styleValue = targetRange.Style
    targetRange.Text = replacementText
    targetRange.Style = styleValue
    doc.Bookmarks.Add bookmarkName, targetRange
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 5030, , _
            "Bookmark was not restored: " & bookmarkName
    End If
    If StrComp(CStr(doc.Bookmarks(bookmarkName).Range.Text), _
       replacementText, vbBinaryCompare) <> 0 Then
        Err.Raise vbObjectError + 5031, , _
            "Bookmark value differs after replacement: " & bookmarkName
    End If
End Sub

Private Sub InsertSignatureBookmark(ByVal doc As Object, _
                                    ByVal bookmarkName As String, _
                                    ByVal signaturePath As String)
    Dim targetRange As Object
    Dim picture As Object
    Dim maximumWidth As Double
    Dim maximumHeight As Double

    RequireBookmark doc, bookmarkName
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    If targetRange.Fields.Count > 0 Or _
       targetRange.ContentControls.Count > 0 Or _
       targetRange.InlineShapes.Count > 0 Then
        Err.Raise vbObjectError + 5032, , _
            "SignatureImage bookmark contains a nested object."
    End If
    If InStr(1, targetRange.Text, vbCr, vbBinaryCompare) > 0 Or _
       InStr(1, targetRange.Text, Chr$(7), vbBinaryCompare) > 0 Then
        Err.Raise vbObjectError + 5033, , _
            "SignatureImage bookmark includes a paragraph or cell marker."
    End If

    targetRange.Text = ""
    targetRange.Collapse WORD_COLLAPSE_START
    Set picture = doc.InlineShapes.AddPicture( _
        signaturePath, False, True, targetRange)
    If picture.Width <= 0 Or picture.Height <= 0 Then
        Err.Raise vbObjectError + 5034, , _
            "Word inserted an invalid signature image."
    End If
    picture.LockAspectRatio = OFFICE_TRUE
    maximumWidth = MAX_SIGNATURE_WIDTH_CM * POINTS_PER_CM
    maximumHeight = MAX_SIGNATURE_HEIGHT_CM * POINTS_PER_CM
    If picture.Width > maximumWidth Then picture.Width = maximumWidth
    If picture.Height > maximumHeight Then picture.Height = maximumHeight
    doc.Bookmarks.Add bookmarkName, picture.Range
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 5035, , _
            "SignatureImage bookmark was not restored."
    End If
    If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 1 Then
        Err.Raise vbObjectError + 5036, , _
            "SignatureImage bookmark does not contain one embedded picture."
    End If
End Sub

Private Sub RequireRowUnchanged(ByVal ws As Worksheet, _
                                ByVal excelRow As Long, _
                                ByRef data As Variant, _
                                ByVal dataRow As Long)
    Dim columnIndex As Long
    Dim currentValue As Variant
    Dim originalValue As Variant

    For columnIndex = 1 To 8
        currentValue = ws.Cells(excelRow, columnIndex).Value2
        originalValue = data(dataRow, columnIndex)
        If Not VariantValuesExactlyEqual(originalValue, currentValue) Then
            Err.Raise vbObjectError + 5037, , _
                "Excel row " & excelRow & " changed after preflight. " & _
                "Run the batch again."
        End If
    Next columnIndex
End Sub

Private Function VariantValuesExactlyEqual(ByVal firstValue As Variant, _
                                            ByVal secondValue As Variant) As Boolean
    If IsError(firstValue) Or IsError(secondValue) Then
        Exit Function
    End If
    If VarType(firstValue) <> VarType(secondValue) Then Exit Function
    VariantValuesExactlyEqual = _
        (StrComp(CStr(firstValue), CStr(secondValue), vbBinaryCompare) = 0)
End Function

Private Function CreateRunFolder(ByVal fso As Object, _
                                 ByVal outputRoot As String) As String
    Dim baseFolder As String
    Dim candidate As String
    Dim suffix As Long

    baseFolder = "Run_" & Format$(Now, "yyyymmdd_hhnnss")
    candidate = JoinPath(outputRoot, baseFolder)
    Do While fso.FolderExists(candidate)
        suffix = suffix + 1
        candidate = JoinPath(outputRoot, baseFolder & "_" & _
            Format$(suffix, "000"))
    Loop
    fso.CreateFolder candidate
    CreateRunFolder = candidate
End Function

Private Sub EnsureFolder(ByVal folderPath As String)
    If FolderExistsStrict(folderPath) Then Exit Sub
    On Error GoTo CreateFailed
    MkDir folderPath
    Exit Sub
CreateFailed:
    Err.Raise vbObjectError + 5038, , _
        "Could not create folder: " & folderPath & ". " & Err.Description
End Sub

Private Sub RequireFolder(ByVal folderPath As String, _
                          ByVal folderLabel As String)
    If Not FolderExistsStrict(folderPath) Then
        Err.Raise vbObjectError + 5039, , _
            "Missing " & folderLabel & ": " & folderPath
    End If
End Sub

Private Sub RequireTargetAbsent(ByVal filePath As String)
    If FileExistsStrict(filePath) Then
        Err.Raise vbObjectError + 5040, , _
            "Refusing to overwrite existing file: " & filePath
    End If
End Sub

Private Sub RequireNonemptyFile(ByVal filePath As String, _
                                ByVal fileLabel As String)
    If Not FileExistsStrict(filePath) Then
        Err.Raise vbObjectError + 5041, , _
            "Missing " & fileLabel & ": " & filePath
    End If
    On Error GoTo SizeFailed
    If FileLen(filePath) <= 0 Then
        Err.Raise vbObjectError + 5042, , _
            "Empty " & fileLabel & ": " & filePath
    End If
    Exit Sub
SizeFailed:
    If Err.Number >= vbObjectError + 5041 And _
       Err.Number <= vbObjectError + 5042 Then
        Err.Raise Err.Number, , Err.Description
    End If
    Err.Raise vbObjectError + 5043, , _
        "Could not inspect " & fileLabel & ": " & filePath & _
        ". " & Err.Description
End Sub

Private Function FileExistsStrict(ByVal filePath As String) As Boolean
    Dim resultText As String

    On Error GoTo CheckFailed
    resultText = Dir$(filePath, vbNormal Or vbHidden Or vbSystem Or vbReadOnly)
    FileExistsStrict = (Len(resultText) > 0)
    Exit Function
CheckFailed:
    Err.Raise vbObjectError + 5044, , _
        "Could not check file path: " & filePath & ". " & Err.Description
End Function

Private Function FolderExistsStrict(ByVal folderPath As String) As Boolean
    Dim resultText As String

    On Error GoTo CheckFailed
    resultText = Dir$(folderPath, vbDirectory)
    If Len(resultText) = 0 Then
        FolderExistsStrict = False
    Else
        FolderExistsStrict = _
            ((GetAttr(folderPath) And vbDirectory) = vbDirectory)
    End If
    Exit Function
CheckFailed:
    Err.Raise vbObjectError + 5045, , _
        "Could not check folder path: " & folderPath & ". " & Err.Description
End Function

Private Function JoinPath(ByVal parentPath As String, _
                          ByVal childName As String) As String
    If Right$(parentPath, 1) = "\" Then
        JoinPath = parentPath & childName
    Else
        JoinPath = parentPath & "\" & childName
    End If
End Function

Private Function ContainsControlCharacter(ByVal valueText As String) As Boolean
    Dim characterIndex As Long
    Dim characterCode As Long

    For characterIndex = 1 To Len(valueText)
        characterCode = AscW(Mid$(valueText, characterIndex, 1))
        If characterCode >= 0 And characterCode <= 31 Then
            ContainsControlCharacter = True
            Exit Function
        End If
    Next characterIndex
End Function

Private Function IsReservedWindowsName(ByVal baseName As String) As Boolean
    Dim upperName As String
    Dim suffixText As String
    Dim suffixCode As Long

    upperName = UCase$(baseName)
    Select Case upperName
        Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
            IsReservedWindowsName = True
            Exit Function
    End Select
    If Len(upperName) >= 4 Then
        If Left$(upperName, 3) = "COM" Or Left$(upperName, 3) = "LPT" Then
            suffixText = Mid$(upperName, 4)
            If Len(suffixText) = 1 Then
                suffixCode = AscW(suffixText)
                If (suffixCode >= AscW("1") And suffixCode <= AscW("9")) Or _
                   suffixText = ChrW$(&HB9) Or _
                   suffixText = ChrW$(&HB2) Or _
                   suffixText = ChrW$(&HB3) Then
                    IsReservedWindowsName = True
                End If
            End If
        End If
    End If
End Function

The example uses documented Word methods to confirm a bookmark with Bookmarks.Exists, embed a local image with InlineShapes.AddPicture, save an explicit DOCX with Document.SaveAs2, and create PDF with ExportAsFixedFormat.

Review certificates as controlled outputs

Content review

Compare the output with Excel: certificate ID, recipient, program, display date, issuer, filename, and intended signature.

Visual review

Check long-name wrapping, line breaks, border alignment, logo quality, signature size, fonts, and PDF rendering at normal zoom and print size.

Authorization review

Confirm the operator was permitted to use the signature asset for this batch and that the certificate release was approved.

Reconciliation review

Match files in Completed against DONE rows. If the workbook save failed after file moves, correct the status deliberately rather than blindly rerunning the row.

Know the limits before treating the batch as final

No atomic pair commit

DOCX move, PDF move, status update, and workbook save are separate operations. A crash can leave a partial state that requires reconciliation.

No cryptographic signing

The macro embeds a raster image. It does not authenticate a signer, apply a certificate-based digital signature, or prove consent.

No automatic approval

DONE records successful generation and workbook saving; it does not replace visual, identity, authorization, or release review.

No mixed-format placeholder guarantee

Text replacement restores the bookmark style. Design each bookmark with one uniform style instead of mixing character formatting inside it.

No rollback of completed files

On error, the example retains prior results for inspection. It deliberately avoids broad automatic deletion that could remove unrelated files.

No school case-pack logic

This is one-certificate-per-row Factory logic. It does not create a student’s certificate plus several different letters from different templates.

Use Factory for the repeatable certificate batch

DocxForge Pro Factory matches this article: many independent Excel rows use one standard Word template, and each selected row creates a separate output. The source data and document generation remain local on the Windows workstation with Microsoft Word Desktop.

Where Factory fits—and where it does not

Use Factory when every selected row produces the same certificate type. Do not force this workflow into Contractor case-pack logic; Contractor is for one reviewed case that needs several different documents from different templates.

Factory: review rows, then apply one certificate template

Review the selected Excel records first, then confirm the standard Word template used for the certificate batch.

DocxForge Pro Factory Excel Data screen for reviewing certificate rows
Factory Excel Data: review the independent certificate records selected for generation.
DocxForge Pro Factory Documents screen for one standard certificate template
Factory Documents: apply one standard Word certificate template to the selected rows.
Generate repeatable certificate batches with FactoryTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Many Excel rowsOne Word templateSeparate outputsLocal processing
Start Free 7-Day Trial

Frequently asked questions

Can one Excel row create one separate certificate?

Yes. In this workflow, every row marked exactly READY is applied to the same approved Word template and produces its own DOCX and PDF pair.

Should certificate dates be stored as Excel dates or display text?

Use a document-ready text column named CompletionDateDisplay. This prevents a date serial or workstation-specific format from appearing in the certificate. Store CertificateID as text too when leading zeros matter.

Can the logo stay inside the Word template?

Yes. A logo shared by every certificate should normally be embedded directly in the approved template. The example inserts only the row-selected signature image at the SignatureImage bookmark.

Does a signature image prove signer authorization?

No. An embedded image is not a digital signature, identity proof, or evidence that the named signer approved this batch. Control access to signature assets and obtain authorization before release.

Does the VBA example provide an atomic DOCX/PDF transaction?

No. It validates and stages both files before moving them to Completed, but two filesystem moves and the later Excel status save are not one atomic transaction. After an interrupted run, reconcile the completed files with the Excel statuses before rerunning rows.

Where does DocxForge Pro Factory fit?

Factory fits this exact pattern: many independent Excel rows, one standard Word template, and a separate output for each selected row. This article does not use the Contractor case-pack pattern, which is for several different documents from different templates for one case.

Move from manual certificates to a controlled Factory batchUse one approved Word template for selected Excel rows. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Factory workflowEditable DOCXFinal PDFLocal Word Desktop
Start Free 7-Day Trial