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.
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.
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.
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.
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.
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 FunctionThe 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.
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.


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.