Spreadsheet → Word → PDF Workflow for Training Completion Packs
Create a controlled certificate, completion letter, and training record from one reviewed completion case and several approved Word templates.
Quick answer: separate the completion record from the document list
Keep one reviewed completion record in TrainingCase. Define each certificate, letter, record, or assessment document in DocumentRegister. Every approved register row names a different Word template, filename suffix, exact bookmark contract, and optional signature.
Use batch certificates when many rows use one standard certificate template. Use this guide when one completed course or trainee record must produce several different documents.
Define the one-row TrainingCase contract
A — PackID
Unique Windows-safe identifier for the completion pack.
B — TraineeID
Stable trainee identifier stored as text when leading zeros matter.
C — TraineeName
Reviewed name exactly as documents should display it.
D — CourseName
Approved course, program, or qualification title.
E — CompletionDateDisplay
Document-ready text rather than an Excel date serial.
F — ScoreDisplay
Optional reviewed score or result; required only by templates that list it.
G — ProviderName
Training provider or issuing organization.
H — InstructorName
Printed instructor or authorized signatory name.
I — PackStatus
Use exactly READY, HOLD, or DONE.
Use DocumentRegister for the different pack documents
DocumentKey
Unique purpose such as CERTIFICATE, COMPLETION_LETTER, or TRAINING_RECORD.
TemplateFile
Unique DOCX filename stored in Templates.
OutputSuffix
Unique safe suffix combined with PackID.
RequiredBookmarks
Exact semicolon-separated field contract, including PackID and TraineeName.
SignatureFile
PNG/JPG/JPEG filename only when SignatureImage is required.
DocumentStatus
Use exactly APPROVED or RETIRED.
PackID;TraineeID;TraineeName;CourseName;CompletionDateDisplay;ProviderName;InstructorName;SignatureImage
Make each Word template declare its data contract
Exact bookmark names
Use PackID, TraineeID, TraineeName, CourseName, CompletionDateDisplay, ScoreDisplay, ProviderName, InstructorName, and SignatureImage.
Uniform placeholder formatting
Keep one style inside each text bookmark and exclude nested fields, controls, images, and range markers.
Controlled signature
List SignatureImage and a signature filename together, or leave both absent.
Clean review state
Turn off Track Changes, resolve revisions across Word stories, and remove comments.
Move the completion pack through six gates
1. Review the training record
Confirm trainee identity, course, completion date, score, provider, instructor, and READY status.
2. Approve required documents
Mark only the templates needed for this pack as APPROVED.
3. Freeze inputs
Copy every approved template and unique signature into InputSnapshot.
4. Validate each template
Check its declared bookmarks, comments, and tracked revisions before filling it.
5. Stage and move pairs
Create nonempty DOCX/PDF pairs and move them into Completed without overwrite.
6. Reconcile and release
Confirm the whole register stayed unchanged, inspect all outputs, then release through an approved channel.
Free Excel VBA example: generate one guarded completion pack
Save the workbook as .xlsm, create TrainingCase and DocumentRegister, place templates in Templates, and keep optional signatures in Assets\Signatures. Run GenerateTrainingCompletionPack. The code snapshots inputs, validates each contract, rejects changed spreadsheet data, and keeps partial output for reconciliation.
Option Explicit
Private Const PACK_SHEET As String = "TrainingCase"
Private Const REGISTER_SHEET As String = "DocumentRegister"
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const DOC_APPROVED As String = "APPROVED"
Private Const DOC_RETIRED As String = "RETIRED"
Private Const WD_FORMAT_DOCX As Long = 12
Private Const WD_EXPORT_PDF As Long = 17
Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_COLLAPSE_START As Long = 1
Private Const OFFICE_TRUE As Long = -1
Private Const POINTS_PER_CM As Double = 28.3464567
Public Sub GenerateTrainingCompletionPack()
Dim wb As Workbook
Dim packWs As Worksheet
Dim registerWs As Worksheet
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
Dim packData As Variant
Dim registerData As Variant
Dim templateSnapshots As Object
Dim signatureSnapshots As Object
Dim documentKeys As Object
Dim templateFiles As Object
Dim outputSuffixes As Object
Dim requiredBookmarks As Object
Dim packId As String
Dim traineeId As String
Dim traineeName As String
Dim courseName As String
Dim completionDateDisplay As String
Dim scoreDisplay As String
Dim providerName As String
Dim instructorName As String
Dim packStatus As String
Dim documentKey As String
Dim templateFile As String
Dim outputSuffix As String
Dim bookmarkContract As String
Dim signatureFile As String
Dim documentStatus As String
Dim templateFolder As String
Dim signatureFolder As String
Dim outputRoot As String
Dim runFolder As String
Dim inputFolder As String
Dim templateSnapshotFolder As String
Dim signatureSnapshotFolder As String
Dim stagingFolder As String
Dim completedFolder As String
Dim sourcePath As String
Dim snapshotPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim outputStem As String
Dim lastRegisterRow As Long
Dim dataRow As Long
Dim excelRow As Long
Dim approvedCount As Long
Dim generatedCount As Long
Dim previousScreenUpdating As Boolean
Dim previousEnableEvents As Boolean
Dim stateCaptured As Boolean
Dim currentStep As String
Dim errorNumber As Long
Dim errorDescription As String
Dim finalStatusPending As Boolean
Dim statusRecoveryMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
previousScreenUpdating = Application.ScreenUpdating
previousEnableEvents = Application.EnableEvents
stateCaptured = True
Application.ScreenUpdating = False
Application.EnableEvents = False
currentStep = "checking the macro-enabled workbook"
RequireWritableMacroWorkbook wb
Set packWs = RequireWorksheet(wb, PACK_SHEET)
Set registerWs = RequireWorksheet(wb, REGISTER_SHEET)
If packWs.ProtectContents Or registerWs.ProtectContents Then
Err.Raise vbObjectError + 5100, , _
"TrainingCase and DocumentRegister must allow status updates."
End If
ValidatePackHeaders packWs
ValidateRegisterHeaders registerWs
RequireOnePackRow packWs
packData = packWs.Range("A2:I2").Value2
packId = ReadRequiredText(packData(1, 1), "PackID", 2)
traineeId = ReadRequiredText(packData(1, 2), "TraineeID", 2)
traineeName = ReadRequiredText(packData(1, 3), "TraineeName", 2)
courseName = ReadRequiredText(packData(1, 4), "CourseName", 2)
completionDateDisplay = ReadRequiredText( _
packData(1, 5), "CompletionDateDisplay", 2)
scoreDisplay = ReadOptionalText(packData(1, 6), "ScoreDisplay", 2)
providerName = ReadRequiredText(packData(1, 7), "ProviderName", 2)
instructorName = ReadRequiredText(packData(1, 8), "InstructorName", 2)
packStatus = ReadRequiredText(packData(1, 9), "PackStatus", 2)
ValidatePackStatus packStatus
If packStatus <> STATUS_READY Then
Err.Raise vbObjectError + 5101, , _
"TrainingCase row 2 must be exactly READY for generation."
End If
ValidateFileComponent packId, "PackID", 2
templateFolder = JoinPath(wb.Path, "Templates")
signatureFolder = JoinPath(JoinPath(wb.Path, "Assets"), "Signatures")
outputRoot = JoinPath(wb.Path, "GeneratedTrainingPacks")
RequireFolder templateFolder, "template folder"
lastRegisterRow = FindLastDataRow(registerWs, 6)
If lastRegisterRow < 2 Then
Err.Raise vbObjectError + 5102, , _
"DocumentRegister has no document rows."
End If
registerData = registerWs.Range("A2:F" & lastRegisterRow).Value2
Set documentKeys = NewTextDictionary(vbTextCompare)
Set templateFiles = NewTextDictionary(vbTextCompare)
Set outputSuffixes = NewTextDictionary(vbTextCompare)
currentStep = "preflighting the document register"
For dataRow = 1 To UBound(registerData, 1)
excelRow = dataRow + 1
If Not DataRowIsBlank(registerData, dataRow, 6) Then
documentStatus = ReadRequiredText( _
registerData(dataRow, 6), "DocumentStatus", excelRow)
ValidateDocumentStatus documentStatus, excelRow
If documentStatus = DOC_APPROVED Then
documentKey = ReadRequiredText( _
registerData(dataRow, 1), "DocumentKey", excelRow)
templateFile = ReadRequiredText( _
registerData(dataRow, 2), "TemplateFile", excelRow)
outputSuffix = ReadRequiredText( _
registerData(dataRow, 3), "OutputSuffix", excelRow)
bookmarkContract = ReadRequiredText( _
registerData(dataRow, 4), "RequiredBookmarks", excelRow)
signatureFile = ReadOptionalText( _
registerData(dataRow, 5), "SignatureFile", excelRow)
ValidateKey documentKey, "DocumentKey", excelRow
ValidateDocxFileName templateFile, excelRow
ValidateFileComponent outputSuffix, "OutputSuffix", excelRow
AddUnique documentKeys, documentKey, "DocumentKey", excelRow
AddUnique templateFiles, templateFile, "TemplateFile", excelRow
AddUnique outputSuffixes, outputSuffix, "OutputSuffix", excelRow
Set requiredBookmarks = ParseBookmarkContract( _
bookmarkContract, excelRow)
If requiredBookmarks.Exists("ScoreDisplay") And _
Len(scoreDisplay) = 0 Then
Err.Raise vbObjectError + 5159, , _
"ScoreDisplay is required by the template contract " & _
"on DocumentRegister row " & excelRow & "."
End If
ValidateSignatureContract requiredBookmarks, signatureFile, excelRow
sourcePath = JoinPath(templateFolder, templateFile)
RequireNonemptyFile sourcePath, _
"template on DocumentRegister row " & excelRow
If Len(signatureFile) > 0 Then
ValidateImageFileName signatureFile, excelRow
sourcePath = JoinPath(signatureFolder, signatureFile)
RequireNonemptyFile sourcePath, _
"signature on DocumentRegister row " & excelRow
End If
approvedCount = approvedCount + 1
End If
End If
Next dataRow
If approvedCount = 0 Then
Err.Raise vbObjectError + 5103, , _
"DocumentRegister has no rows marked exactly APPROVED."
End If
currentStep = "saving the preflighted workbook"
wb.Save
Set fso = CreateObject("Scripting.FileSystemObject")
EnsureFolder outputRoot
runFolder = CreateRunFolder(fso, outputRoot, packId)
inputFolder = JoinPath(runFolder, "InputSnapshot")
templateSnapshotFolder = JoinPath(inputFolder, "Templates")
signatureSnapshotFolder = JoinPath(inputFolder, "Signatures")
stagingFolder = JoinPath(runFolder, "Staging")
completedFolder = JoinPath(runFolder, "Completed")
fso.CreateFolder inputFolder
fso.CreateFolder templateSnapshotFolder
fso.CreateFolder signatureSnapshotFolder
fso.CreateFolder stagingFolder
fso.CreateFolder completedFolder
Set templateSnapshots = NewTextDictionary(vbTextCompare)
Set signatureSnapshots = NewTextDictionary(vbTextCompare)
currentStep = "creating the controlled input snapshot"
For dataRow = 1 To UBound(registerData, 1)
If CStr(registerData(dataRow, 6)) = DOC_APPROVED Then
templateFile = CStr(registerData(dataRow, 2))
sourcePath = JoinPath(templateFolder, templateFile)
snapshotPath = JoinPath(templateSnapshotFolder, templateFile)
CopyFileToSnapshot fso, sourcePath, snapshotPath, _
"template " & templateFile
templateSnapshots.Add templateFile, snapshotPath
signatureFile = CStr(registerData(dataRow, 5))
If Len(signatureFile) > 0 And _
Not signatureSnapshots.Exists(signatureFile) Then
sourcePath = JoinPath(signatureFolder, signatureFile)
snapshotPath = JoinPath(signatureSnapshotFolder, signatureFile)
CopyFileToSnapshot fso, sourcePath, snapshotPath, _
"signature " & signatureFile
signatureSnapshots.Add signatureFile, snapshotPath
End If
End If
Next dataRow
currentStep = "starting Microsoft Word Desktop"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = WD_ALERTS_NONE
wdApp.ScreenUpdating = False
For dataRow = 1 To UBound(registerData, 1)
excelRow = dataRow + 1
If CStr(registerData(dataRow, 6)) = DOC_APPROVED Then
currentStep = "rechecking the training pack and register row " & excelRow
RequireRangeUnchanged packWs.Range("A2:I2"), packData, 1, 9, _
"TrainingCase row 2"
RequireRangeUnchanged registerWs.Range("A" & excelRow & _
":F" & excelRow), registerData, dataRow, 6, _
"DocumentRegister row " & excelRow
documentKey = CStr(registerData(dataRow, 1))
templateFile = CStr(registerData(dataRow, 2))
outputSuffix = CStr(registerData(dataRow, 3))
bookmarkContract = CStr(registerData(dataRow, 4))
signatureFile = CStr(registerData(dataRow, 5))
Set requiredBookmarks = ParseBookmarkContract( _
bookmarkContract, excelRow)
outputStem = packId & "_" & outputSuffix
ValidateFileComponent outputStem, "combined output name", excelRow
tempDocxPath = JoinPath(stagingFolder, outputStem & ".docx")
tempPdfPath = JoinPath(stagingFolder, outputStem & ".pdf")
finalDocxPath = JoinPath(completedFolder, outputStem & ".docx")
finalPdfPath = JoinPath(completedFolder, outputStem & ".pdf")
RequireTargetAbsent tempDocxPath
RequireTargetAbsent tempPdfPath
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
currentStep = "generating " & documentKey
Set doc = wdApp.Documents.Open( _
templateSnapshots(templateFile), False, True, False)
ValidateTemplate doc, requiredBookmarks, templateFile
PopulateRequiredBookmarks doc, requiredBookmarks, _
packId, traineeId, traineeName, courseName, _
completionDateDisplay, scoreDisplay, providerName, _
instructorName, signatureFile, signatureSnapshots
ValidateResultBeforeSave doc, requiredBookmarks, documentKey
doc.SaveAs2 tempDocxPath, WD_FORMAT_DOCX
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_PDF, False, 0, _
0, 1, 1, 0, True, True, 0, True, True, False
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
RequireNonemptyFile tempDocxPath, "staged DOCX " & documentKey
RequireNonemptyFile tempPdfPath, "staged PDF " & documentKey
Name tempDocxPath As finalDocxPath
Name tempPdfPath As finalPdfPath
generatedCount = generatedCount + 1
End If
Next dataRow
currentStep = "confirming the complete pack and document register"
If FindLastDataRow(packWs, 9) <> 2 Then
Err.Raise vbObjectError + 5160, , _
"TrainingCase row count changed after preflight."
End If
If FindLastDataRow(registerWs, 6) <> lastRegisterRow Then
Err.Raise vbObjectError + 5161, , _
"DocumentRegister row count changed after preflight."
End If
RequireTableUnchanged packWs.Range("A2:I2"), packData, 1, 9, _
"TrainingCase"
RequireTableUnchanged registerWs.Range("A2:F" & lastRegisterRow), _
registerData, UBound(registerData, 1), 6, "DocumentRegister"
If generatedCount <> approvedCount Then
Err.Raise vbObjectError + 5162, , _
"Generated document count does not match the approved count."
End If
currentStep = "saving the generated status to TrainingCase"
finalStatusPending = True
packWs.Cells(2, 9).Value2 = STATUS_DONE
wb.Save
finalStatusPending = False
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
RestoreExcelState previousScreenUpdating, previousEnableEvents
stateCaptured = False
MsgBox "Training completion document pack completed." & vbCrLf & _
"Documents generated: " & generatedCount & vbCrLf & _
"Completed folder: " & completedFolder & vbCrLf & vbCrLf & _
"Review trainee identity, completion data, signatures, DOCX, and PDF before release.", _
vbInformation
CleanExit:
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If stateCaptured Then _
RestoreExcelState previousScreenUpdating, previousEnableEvents
On Error GoTo 0
Exit Sub
CleanFail:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If finalStatusPending And Not packWs Is Nothing Then
packWs.Cells(2, 9).Value2 = packStatus
statusRecoveryMessage = vbCrLf & _
"DONE was restored to " & packStatus & _
" in memory because the final workbook save was not confirmed."
End If
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If stateCaptured Then _
RestoreExcelState previousScreenUpdating, previousEnableEvents
On Error GoTo 0
MsgBox "Training pack 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." & _
statusRecoveryMessage, vbCritical
End Sub
Private Sub PopulateRequiredBookmarks(ByVal doc As Object, _
ByVal required As Object, _
ByVal packId As String, _
ByVal traineeId As String, _
ByVal traineeName As String, _
ByVal courseName As String, _
ByVal completionDate As String, _
ByVal scoreDisplay As String, _
ByVal providerName As String, _
ByVal instructorName As String, _
ByVal signatureFile As String, _
ByVal signatures As Object)
Dim token As Variant
For Each token In required.Keys
Select Case CStr(token)
Case "PackID": WriteTextBookmark doc, CStr(token), packId
Case "TraineeID": WriteTextBookmark doc, CStr(token), traineeId
Case "TraineeName": WriteTextBookmark doc, CStr(token), traineeName
Case "CourseName": WriteTextBookmark doc, CStr(token), courseName
Case "CompletionDateDisplay"
WriteTextBookmark doc, CStr(token), completionDate
Case "ScoreDisplay": WriteTextBookmark doc, CStr(token), scoreDisplay
Case "ProviderName": WriteTextBookmark doc, CStr(token), providerName
Case "InstructorName"
WriteTextBookmark doc, CStr(token), instructorName
Case "SignatureImage"
InsertSignatureBookmark doc, CStr(token), _
signatures(signatureFile)
End Select
Next token
End Sub
Private Sub ValidateTemplate(ByVal doc As Object, _
ByVal required As Object, _
ByVal templateFile As String)
RejectTrackedChanges doc, "template " & templateFile
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5104, , _
"Remove comments from template " & templateFile & "."
End If
ValidateBookmarkContract doc, required, templateFile
End Sub
Private Sub ValidateResultBeforeSave(ByVal doc As Object, _
ByVal required As Object, _
ByVal documentKey As String)
RejectTrackedChanges doc, "generated document " & documentKey
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5105, , _
"Generated document contains comments: " & documentKey
End If
ValidateBookmarkContract doc, required, documentKey
End Sub
Private Sub ValidateBookmarkContract(ByVal doc As Object, _
ByVal required As Object, _
ByVal documentLabel As String)
Dim token As Variant
Dim allowed As Object
Set allowed = AllowedBookmarkNames()
For Each token In required.Keys
If Not doc.Bookmarks.Exists(CStr(token)) Then
Err.Raise vbObjectError + 5106, , _
"Missing bookmark " & token & " in " & documentLabel & "."
End If
Next token
For Each token In allowed.Keys
If doc.Bookmarks.Exists(CStr(token)) And _
Not required.Exists(CStr(token)) Then
Err.Raise vbObjectError + 5107, , _
"Bookmark " & token & " exists but is not listed in " & _
"RequiredBookmarks for " & documentLabel & "."
End If
Next token
End Sub
Private Function ParseBookmarkContract(ByVal contractText As String, _
ByVal excelRow As Long) As Object
Dim result As Object
Dim allowed As Object
Dim tokens As Variant
Dim token As Variant
Dim tokenText As String
Set result = NewTextDictionary(vbBinaryCompare)
Set allowed = AllowedBookmarkNames()
tokens = Split(contractText, ";")
For Each token In tokens
tokenText = CStr(token)
If Len(tokenText) = 0 Or tokenText <> Trim$(tokenText) Then
Err.Raise vbObjectError + 5108, , _
"RequiredBookmarks has an empty or padded token on row " & _
excelRow & "."
End If
If Not allowed.Exists(tokenText) Then
Err.Raise vbObjectError + 5109, , _
"Unknown or incorrectly cased bookmark on row " & excelRow & _
": " & tokenText
End If
If result.Exists(tokenText) Then
Err.Raise vbObjectError + 5110, , _
"Duplicate bookmark token on row " & excelRow & ": " & tokenText
End If
result.Add tokenText, True
Next token
If Not result.Exists("PackID") Or Not result.Exists("TraineeName") Then
Err.Raise vbObjectError + 5111, , _
"Every approved template must require PackID and TraineeName."
End If
Set ParseBookmarkContract = result
End Function
Private Function AllowedBookmarkNames() As Object
Dim result As Object
Dim item As Variant
Set result = NewTextDictionary(vbBinaryCompare)
For Each item In Array("PackID", "TraineeID", "TraineeName", _
"CourseName", "CompletionDateDisplay", "ScoreDisplay", _
"ProviderName", "InstructorName", "SignatureImage")
result.Add CStr(item), True
Next item
Set AllowedBookmarkNames = result
End Function
Private Sub ValidateSignatureContract(ByVal required As Object, _
ByVal signatureFile As String, _
ByVal excelRow As Long)
If required.Exists("SignatureImage") And Len(signatureFile) = 0 Then
Err.Raise vbObjectError + 5112, , _
"SignatureFile is required when SignatureImage is listed on row " & _
excelRow & "."
End If
If Not required.Exists("SignatureImage") And Len(signatureFile) > 0 Then
Err.Raise vbObjectError + 5113, , _
"SignatureFile must be blank when SignatureImage is not listed " & _
"on row " & excelRow & "."
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim styleValue As Variant
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 + 5114, , _
"Text bookmark contains a nested object: " & bookmarkName
End If
RejectRangeMarkers targetRange, bookmarkName
styleValue = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = styleValue
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 5115, , _
"Bookmark was not restored: " & bookmarkName
End If
If StrComp(doc.Bookmarks(bookmarkName).Range.Text, replacementText, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5116, , _
"Bookmark value differs after replacement: " & bookmarkName
End If
End Sub
Private Sub InsertSignatureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal imagePath As String)
Dim targetRange As Object
Dim picture As Object
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 + 5117, , _
"Signature bookmark contains a nested object."
End If
RejectRangeMarkers targetRange, bookmarkName
targetRange.Text = ""
targetRange.Collapse WD_COLLAPSE_START
Set picture = doc.InlineShapes.AddPicture( _
imagePath, False, True, targetRange)
If picture.Width <= 0 Or picture.Height <= 0 Then
Err.Raise vbObjectError + 5118, , _
"Word inserted an invalid signature image."
End If
picture.LockAspectRatio = OFFICE_TRUE
If picture.Width > 4.5 * POINTS_PER_CM Then _
picture.Width = 4.5 * POINTS_PER_CM
If picture.Height > 1.8 * POINTS_PER_CM Then _
picture.Height = 1.8 * POINTS_PER_CM
doc.Bookmarks.Add bookmarkName, picture.Range
If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 1 Then
Err.Raise vbObjectError + 5119, , _
"Signature bookmark does not contain one embedded image."
End If
End Sub
Private Sub RejectRangeMarkers(ByVal targetRange As Object, _
ByVal bookmarkName As String)
If InStr(1, targetRange.Text, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, targetRange.Text, Chr$(7), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5120, , _
"Bookmark includes a paragraph or cell marker: " & bookmarkName
End If
End Sub
Private Sub RejectTrackedChanges(ByVal doc As Object, _
ByVal documentLabel As String)
If doc.TrackRevisions Then
Err.Raise vbObjectError + 5121, , _
"Turn off Track Changes in " & documentLabel & "."
End If
If CountRevisionsInAllStories(doc) > 0 Then
Err.Raise vbObjectError + 5122, , _
"Resolve tracked revisions in every story of " & _
documentLabel & "."
End If
End Sub
Private Function CountRevisionsInAllStories(ByVal doc As Object) As Long
Dim root As Object
Dim story As Object
Dim total As Long
For Each root In doc.StoryRanges
Set story = root
Do While Not story Is Nothing
total = total + story.Revisions.Count
Set story = story.NextStoryRange
Loop
Next root
CountRevisionsInAllStories = total
End Function
Private Sub ValidatePackHeaders(ByVal ws As Worksheet)
ValidateHeaders ws, Array("PackID", "TraineeID", "TraineeName", _
"CourseName", "CompletionDateDisplay", "ScoreDisplay", _
"ProviderName", "InstructorName", "PackStatus")
End Sub
Private Sub ValidateRegisterHeaders(ByVal ws As Worksheet)
ValidateHeaders ws, Array("DocumentKey", "TemplateFile", _
"OutputSuffix", "RequiredBookmarks", "SignatureFile", _
"DocumentStatus")
End Sub
Private Sub ValidateHeaders(ByVal ws As Worksheet, ByVal expected As Variant)
Dim columnIndex As Long
Dim valueText As Variant
For columnIndex = 1 To UBound(expected) + 1
valueText = ws.Cells(1, columnIndex).Value2
If IsError(valueText) Or VarType(valueText) <> vbString Then
Err.Raise vbObjectError + 5123, , _
"Header " & ws.Cells(1, columnIndex).Address(False, False) & _
" on " & ws.Name & " must contain exact text."
End If
If StrComp(CStr(valueText), CStr(expected(columnIndex - 1)), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5124, , _
"Expected header " & expected(columnIndex - 1) & _
" in column " & columnIndex & " on " & ws.Name & "."
End If
Next columnIndex
End Sub
Private Sub RequireOnePackRow(ByVal ws As Worksheet)
Dim lastRow As Long
lastRow = FindLastDataRow(ws, 9)
If lastRow < 2 Then
Err.Raise vbObjectError + 5125, , _
"TrainingCase row 2 is missing."
End If
If lastRow > 2 Then
Err.Raise vbObjectError + 5126, , _
"Contractor logic accepts one reviewed TrainingCase row per run."
End If
End Sub
Private Function FindLastDataRow(ByVal ws As Worksheet, _
ByVal lastColumn As Long) As Long
Dim lastCell As Range
Set lastCell = ws.Range(ws.Cells(1, 1), _
ws.Cells(ws.Rows.Count, lastColumn)).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, _
ByVal columnCount As Long) As Boolean
Dim columnIndex As Long
DataRowIsBlank = True
For columnIndex = 1 To columnCount
If IsError(data(dataRow, columnIndex)) Or _
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
ReadRequiredText = ReadOptionalText(rawValue, fieldName, excelRow)
If Len(ReadRequiredText) = 0 Then
Err.Raise vbObjectError + 5127, , _
fieldName & " is required on row " & excelRow & "."
End If
End Function
Private Function ReadOptionalText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
If IsEmpty(rawValue) Then
ReadOptionalText = ""
Exit Function
End If
If IsError(rawValue) Or VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 5128, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
ReadOptionalText = CStr(rawValue)
If ReadOptionalText <> Trim$(ReadOptionalText) Then
Err.Raise vbObjectError + 5129, , _
fieldName & " has outer whitespace on row " & excelRow & "."
End If
If ContainsControlCharacter(ReadOptionalText) Then
Err.Raise vbObjectError + 5130, , _
fieldName & " contains a control character on row " & excelRow & "."
End If
End Function
Private Sub ValidatePackStatus(ByVal statusText As String)
If statusText <> STATUS_READY And statusText <> STATUS_HOLD And _
statusText <> STATUS_DONE Then
Err.Raise vbObjectError + 5131, , _
"PackStatus must be exactly READY, HOLD, or DONE."
End If
End Sub
Private Sub ValidateDocumentStatus(ByVal statusText As String, _
ByVal excelRow As Long)
If statusText <> DOC_APPROVED And statusText <> DOC_RETIRED Then
Err.Raise vbObjectError + 5132, , _
"DocumentStatus must be exactly APPROVED or RETIRED on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateKey(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
If Len(valueText) > 80 Then
Err.Raise vbObjectError + 5133, , _
fieldName & " exceeds 80 characters on row " & excelRow & "."
End If
End Sub
Private Sub ValidateFileComponent(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
Dim item As Variant
Dim baseName As String
Dim lowerName As String
If Len(valueText) > 100 Then
Err.Raise vbObjectError + 5134, , _
fieldName & " exceeds 100 characters on row " & excelRow & "."
End If
For Each item In Array("<", ">", ":", Chr$(34), "/", "\", _
"|", "?", "*")
If InStr(1, valueText, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5135, , _
fieldName & " contains a forbidden Windows character on row " & _
excelRow & "."
End If
Next item
If Right$(valueText, 1) = "." Or Right$(valueText, 1) = " " Then
Err.Raise vbObjectError + 5136, , _
fieldName & " ends with a period or space on row " & excelRow & "."
End If
lowerName = LCase$(valueText)
If Right$(lowerName, 5) = ".docx" Or _
Right$(lowerName, 4) = ".pdf" Then
Err.Raise vbObjectError + 5137, , _
fieldName & " must not include .docx or .pdf on row " & excelRow & "."
End If
baseName = valueText
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(baseName) Then
Err.Raise vbObjectError + 5138, , _
fieldName & " uses a reserved Windows name on row " & excelRow & "."
End If
End Sub
Private Sub ValidateDocxFileName(ByVal fileName As String, _
ByVal excelRow As Long)
ValidateAssetFileName fileName, excelRow
If LCase$(Right$(fileName, 5)) <> ".docx" Then
Err.Raise vbObjectError + 5139, , _
"TemplateFile must end with .docx on row " & excelRow & "."
End If
End Sub
Private Sub ValidateImageFileName(ByVal fileName As String, _
ByVal excelRow As Long)
Dim lowerName As String
ValidateAssetFileName fileName, excelRow
lowerName = LCase$(fileName)
If Right$(lowerName, 4) <> ".png" And _
Right$(lowerName, 4) <> ".jpg" And _
Right$(lowerName, 5) <> ".jpeg" Then
Err.Raise vbObjectError + 5140, , _
"SignatureFile must be PNG, JPG, or JPEG on row " & excelRow & "."
End If
End Sub
Private Sub ValidateAssetFileName(ByVal fileName As String, _
ByVal excelRow As Long)
Dim item As Variant
For Each item In Array("<", ">", ":", Chr$(34), "/", "\", _
"|", "?", "*")
If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5141, , _
"Asset filename contains a path or wildcard on row " & _
excelRow & "."
End If
Next item
If Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
Err.Raise vbObjectError + 5142, , _
"Asset filename ends with a period or space on row " & excelRow & "."
End If
End Sub
Private Sub AddUnique(ByVal values As Object, ByVal valueText As String, _
ByVal fieldName As String, ByVal excelRow As Long)
If values.Exists(valueText) Then
Err.Raise vbObjectError + 5143, , _
"Duplicate " & fieldName & " on rows " & values(valueText) & _
" and " & excelRow & ": " & valueText
End If
values.Add valueText, excelRow
End Sub
Private Function NewTextDictionary(ByVal compareMode As Long) As Object
Dim result As Object
Set result = CreateObject("Scripting.Dictionary")
result.CompareMode = compareMode
Set NewTextDictionary = result
End Function
Private Sub RequireRangeUnchanged(ByVal liveRange As Range, _
ByRef snapshot As Variant, _
ByVal snapshotRow As Long, _
ByVal columnCount As Long, _
ByVal rangeLabel As String)
Dim liveData As Variant
Dim columnIndex As Long
liveData = liveRange.Value2
For columnIndex = 1 To columnCount
If Not ValuesExactlyEqual(snapshot(snapshotRow, columnIndex), _
liveData(1, columnIndex)) Then
Err.Raise vbObjectError + 5144, , _
rangeLabel & " changed after preflight. Start a new run."
End If
Next columnIndex
End Sub
Private Sub RequireTableUnchanged(ByVal liveRange As Range, _
ByRef snapshot As Variant, _
ByVal expectedRows As Long, _
ByVal expectedColumns As Long, _
ByVal tableLabel As String)
Dim liveData As Variant
Dim rowIndex As Long
Dim columnIndex As Long
If liveRange.Rows.Count <> expectedRows Or _
liveRange.Columns.Count <> expectedColumns Then
Err.Raise vbObjectError + 5163, , _
tableLabel & " dimensions changed after preflight."
End If
liveData = liveRange.Value2
For rowIndex = 1 To expectedRows
For columnIndex = 1 To expectedColumns
If Not ValuesExactlyEqual(snapshot(rowIndex, columnIndex), _
liveData(rowIndex, columnIndex)) Then
Err.Raise vbObjectError + 5164, , _
tableLabel & " changed after preflight at row " & _
(rowIndex + 1) & ", column " & columnIndex & "."
End If
Next columnIndex
Next rowIndex
End Sub
Private Function ValuesExactlyEqual(ByVal firstValue As Variant, _
ByVal secondValue As Variant) As Boolean
If IsError(firstValue) Or IsError(secondValue) Then Exit Function
If VarType(firstValue) <> VarType(secondValue) Then Exit Function
ValuesExactlyEqual = (StrComp(CStr(firstValue), CStr(secondValue), _
vbBinaryCompare) = 0)
End Function
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 failureText 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 + 5145, , _
"Snapshot size differs for " & fileLabel & "."
End If
If fso.GetFile(sourcePath).Size <> sourceSize Or _
fso.GetFile(sourcePath).DateLastModified <> sourceModified Then
Err.Raise vbObjectError + 5146, , _
"Source changed while snapshotting " & fileLabel & "."
End If
Exit Sub
CopyFailed:
failureText = Err.Description
Err.Raise vbObjectError + 5147, , _
"Could not snapshot " & fileLabel & ": " & failureText
End Sub
Private Function CreateRunFolder(ByVal fso As Object, _
ByVal outputRoot As String, _
ByVal packId As String) As String
Dim baseName As String
Dim candidate As String
Dim suffix As Long
baseName = "Run_" & packId & "_" & Format$(Now, "yyyymmdd_hhnnss")
candidate = JoinPath(outputRoot, baseName)
Do While fso.FolderExists(candidate)
suffix = suffix + 1
candidate = JoinPath(outputRoot, baseName & "_" & _
Format$(suffix, "000"))
Loop
fso.CreateFolder candidate
CreateRunFolder = candidate
End Function
Private Sub RequireWritableMacroWorkbook(ByVal wb As Workbook)
If Len(wb.Path) = 0 Or LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 5148, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 5149, , _
"The workbook is read-only; DONE cannot be saved."
End If
End Sub
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error GoTo MissingSheet
Set RequireWorksheet = wb.Worksheets(sheetName)
Exit Function
MissingSheet:
Err.Raise vbObjectError + 5150, , _
"Missing worksheet: " & sheetName
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 + 5151, , _
"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 + 5152, , _
"Missing " & folderLabel & ": " & folderPath
End If
End Sub
Private Sub RequireTargetAbsent(ByVal filePath As String)
If FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 5153, , _
"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 + 5154, , _
"Missing " & fileLabel & ": " & filePath
End If
On Error GoTo InspectFailed
If FileLen(filePath) <= 0 Then
Err.Raise vbObjectError + 5155, , _
"Empty " & fileLabel & ": " & filePath
End If
Exit Sub
InspectFailed:
Err.Raise vbObjectError + 5156, , _
"Could not inspect " & fileLabel & ": " & filePath & _
". " & Err.Description
End Sub
Private Function FileExistsStrict(ByVal filePath As String) As Boolean
On Error GoTo CheckFailed
FileExistsStrict = (Len(Dir$(filePath, _
vbNormal Or vbHidden Or vbSystem Or vbReadOnly)) > 0)
Exit Function
CheckFailed:
Err.Raise vbObjectError + 5157, , _
"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 = _
((GetAttr(folderPath) And vbDirectory) = vbDirectory)
End If
Exit Function
CheckFailed:
Err.Raise vbObjectError + 5158, , _
"Could not check folder: " & 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 index As Long
Dim code As Long
For index = 1 To Len(valueText)
code = AscW(Mid$(valueText, index, 1))
If code >= 0 And code <= 31 Then
ContainsControlCharacter = True
Exit Function
End If
Next index
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
Private Sub RestoreExcelState(ByVal screenUpdatingValue As Boolean, _
ByVal enableEventsValue As Boolean)
Application.EnableEvents = enableEventsValue
Application.ScreenUpdating = screenUpdatingValue
End SubThe example uses Word’s documented bookmark checks, embedded images, DOCX save, and PDF export.
Review the generated pack before release
Training evidence
Match PackID, TraineeID, name, course, completion date, score, provider, and approved documents.
Visual output
Inspect wrapping, branding, signatures, page breaks, DOCX, and PDF.
Authorization
Confirm the instructor or provider authorized any signature image and wording.
Reconciliation
Treat DONE as successful generation and workbook saving, then review every completed pair before release.
Know the limits of the example
No atomic pack commit
File moves and the final workbook save are separate operations.
No training-policy decision
The macro cannot decide whether course requirements were met.
No digital signature
An image does not authenticate a signer.
No automatic distribution
Review before emailing, uploading, or printing.
No roster batch
One TrainingCase row is accepted per run.
No silent data normalization
IDs, dates, scores, statuses, and contracts must already be approved text.
Use Contractor for one multi-document completion case
DocxForge Pro Contractor reuses one reviewed case across several different documents created from different Word templates. That is the training completion pack described here.
Use it for a certificate, completion letter, training record, and other distinct outputs for one case. Factory remains the separate many-row, one-template workflow.




Frequently asked questions
Is this the same as generating certificates for a whole roster?
No. This workflow creates several different documents for one reviewed training completion record. For many independent rows using one certificate template, use the batch certificates workflow.
What can a training completion pack contain?
An approved pack can contain a completion certificate, trainee letter, training record, assessment summary, or another document defined by its own Word template and bookmark contract.
Can ScoreDisplay be blank?
Yes, unless an approved document lists ScoreDisplay in RequiredBookmarks. If the template needs a score, the value must be present before generation.
Does an embedded instructor signature prove authorization?
No. A signature image is not a digital signature or proof of approval. Restrict access to assets and approve their use before release.
Can a failed run leave only part of the pack?
Yes. Each DOCX/PDF pair moves separately. The pack becomes DONE only after the full register remains unchanged, every approved document is generated, and the workbook saves.
Where does DocxForge Pro Contractor fit?
Contractor matches one reviewed completion record that must produce several different documents from different Word templates. Factory is for many independent rows using one standard template.