How to Prepare Audit Checklists and Signed PDFs from Excel
Turn independent audit rows into consistent Word checklists and PDFs with one approved template, an optional evidence photo, and a separately authorized signature image.
Quick answer: generate the checklist first and control signing separately
Use one complete AuditChecklists row for one output. Review the audit area, checklist title, date, reviewer, result, summary, optional evidence photo, signature authorization, output name, and status before marking the row READY. The macro fills one standard Word template and creates a separate DOCX/PDF pair.
This example may embed a pre-approved signature image. If the organization requires certificate-backed signing, tamper evidence, signer authentication, or an e-signature workflow, apply that process after generation with the approved signing system.
Separate signature appearance from signature assurance
Signature image
A PNG, JPG, or JPEG may show a handwritten mark in the document. It does not create cryptographic proof.
Digital signature
A certificate-backed digital signature can provide authenticity and integrity checks that an inserted picture cannot.
Authorization
The spreadsheet flag records an approved decision supplied by the operator; the macro cannot verify the signer’s authority.
Signing order
Freeze and review the generated content before any separate digital-signature or e-signature process begins.
Storage
Restrict access to approved signature images and keep their use, release, replacement, and revocation procedures outside the batch.
Release evidence
Record the actual approver, signing method, date, destination, and retained evidence required by policy.
Create the exact AuditChecklists contract in columns A–N
A — ChecklistID
Unique checklist identifier stored as text when leading zeros matter.
B — AuditArea
Reviewed process, control area, department, site, or system being checked.
C — ChecklistTitle
Document-ready title for this checklist record.
D — ReviewDateDisplay
Reviewed date text exactly as it should appear in Word.
E — ReviewerName
Reviewed name; the macro does not verify identity or authority.
F — ChecklistResult
Use exactly PASS, FOLLOW-UP, or FAIL.
G — ChecklistSummary
Required reviewed narrative. Excel line breaks become separate Word paragraphs.
H–I — Evidence photo pair
Optional EvidencePhotoFile and EvidencePhotoCaption. Supply both or leave both blank.
J–L — Signature contract
SignatureAuthorized must be YES or NO. YES requires SignatureFile and SignatureDateDisplay; NO requires both blank.
M — OutputName
Unique Windows-safe filename stem without .docx or .pdf.
N — ChecklistStatus
Use exactly READY, HOLD, or DONE. Only READY rows are generated.
Document-ready text
Store IDs, dates, names, results, captions, image filenames, and output names as reviewed text.
Control evidence and signature images before the batch
Filename only
Store names such as AUD-0042-evidence.jpg, not full paths, URLs, wildcards, mapped drives, or traversal sequences.
Approved folder
When a row uses an image, place it in restricted Assets\ApprovedImages beside the workbook.
Evidence pair
An evidence filename requires a caption. A caption without the matching file is rejected.
Signature gate
YES requires the approved signature image and displayed signing date. NO forces both values to remain blank.
Run snapshot
Every used image and the approved template are copied into a run-specific InputSnapshot.
Assurance limit
Existence, extension, file size, copying, and embedding do not validate image content, consent, identity, or provenance.
Build one Word template with fixed bookmarks
Checklist identity
ChecklistID, AuditArea, ChecklistTitle, and ReviewDateDisplay.
Reviewer and result
ReviewerName and ChecklistResult.
Narrative
ChecklistSummary in a range designed for several reviewed paragraphs.
Evidence slot
EvidencePhoto and EvidencePhotoCaption. Both are cleared when the Excel pair is blank.
Signature slot
ReviewerSignature and SignatureDateDisplay. Both are cleared when SignatureAuthorized is NO.
Clean review state
Turn off Track Changes, resolve revisions in every story, remove comments, and keep bookmark ranges free of nested objects.
Test unsigned and image-signed rows, portrait and landscape evidence, long summaries, PASS/FOLLOW-UP/FAIL results, table wrapping, page breaks, headers, footers, and every exported PDF page.
Run the audit-checklist batch through six gates
1. Approve each row
Confirm every document-ready value, result, summary, optional evidence pair, signature authorization, OutputName, and ChecklistStatus.
2. Preflight the batch
Reject duplicate IDs, duplicate output names, invalid result values, unsafe filenames, incomplete image contracts, and missing files before Word starts.
3. Snapshot approved inputs
Copy the template and each used image into the run folder and stop if a source changes during copying.
4. Recheck the row
Compare all A–N values with the original in-memory snapshot immediately before generating that checklist.
5. Stage the pair
Fill text bookmarks, insert or clear image slots, save DOCX, export PDF, and move both nonempty files into Completed.
6. Review and sign
Save DONE after generation, then perform the separate factual review and any required certificate-backed signing or e-signature workflow.
Free Excel VBA example: generate audit checklists and PDFs
Save the workbook as .xlsm. Create AuditChecklists with the exact A–N headers above. Put StandardAuditChecklist.docx in Templates. Store any approved evidence and signature images in Assets\ApprovedImages. Run GenerateAuditChecklistBatch.
Option Explicit
Private Const DATA_SHEET As String = "AuditChecklists"
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_PHOTO_WIDTH_CM As Double = 12#
Private Const MAX_PHOTO_HEIGHT_CM As Double = 8#
Private Const MAX_SIGNATURE_WIDTH_CM As Double = 7#
Private Const MAX_SIGNATURE_HEIGHT_CM As Double = 3#
Public Sub GenerateAuditChecklistBatch()
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 imageFolder As String
Dim outputRoot As String
Dim runFolder As String
Dim inputSnapshotFolder As String
Dim imageSnapshotFolder As String
Dim templateSnapshotPath As String
Dim sourcePhotoPath As String
Dim snapshotPhotoPath 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 evidencePhotoPaths() As String
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 checklistId As String
Dim auditArea As String
Dim checklistTitle As String
Dim reviewDateDisplay As String
Dim reviewerName As String
Dim checklistResult As String
Dim checklistSummary As String
Dim evidencePhotoFile As String
Dim evidencePhotoCaption As String
Dim signatureAuthorized As String
Dim signatureFile As String
Dim signatureDateDisplay As String
Dim outputName As String
Dim checklistStatus As String
Dim checklistIds As Object
Dim outputNames As Object
Dim imageSnapshots 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
Dim statusSavePending As Boolean
Dim statusRow As Long
Dim statusRecoveryMessage 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 AuditChecklists sheet is protected against status updates."
End If
ValidateHeaders ws
lastRow = FindLastDataRow(ws)
If lastRow < 2 Then
Err.Raise vbObjectError + 5001, , _
"No audit checklist records were found below row 1."
End If
templatePath = JoinPath(JoinPath(wb.Path, "Templates"), _
"StandardAuditChecklist.docx")
imageFolder = JoinPath(JoinPath(wb.Path, "Assets"), _
"ApprovedImages")
outputRoot = JoinPath(wb.Path, "GeneratedAuditChecklists")
RequireNonemptyFile templatePath, "audit checklist template"
data = ws.Range("A2:N" & lastRow).Value2
ReDim evidencePhotoPaths(1 To UBound(data, 1))
ReDim signaturePaths(1 To UBound(data, 1))
Set checklistIds = CreateObject("Scripting.Dictionary")
checklistIds.CompareMode = vbTextCompare
Set outputNames = CreateObject("Scripting.Dictionary")
outputNames.CompareMode = vbTextCompare
currentStep = "preflighting Excel audit checklist rows"
For dataRow = 1 To UBound(data, 1)
excelRow = dataRow + 1
If Not DataRowIsBlank(data, dataRow) Then
checklistStatus = ReadRequiredText(data(dataRow, 14), _
"ChecklistStatus", excelRow)
ValidateStatus checklistStatus, excelRow
If StrComp(checklistStatus, READY_STATUS, _
vbBinaryCompare) = 0 Then
checklistId = ReadRequiredText(data(dataRow, 1), _
"ChecklistID", excelRow)
auditArea = ReadRequiredText(data(dataRow, 2), _
"AuditArea", excelRow)
checklistTitle = ReadRequiredText(data(dataRow, 3), _
"ChecklistTitle", excelRow)
reviewDateDisplay = ReadRequiredText(data(dataRow, 4), _
"ReviewDateDisplay", excelRow)
reviewerName = ReadRequiredText(data(dataRow, 5), _
"ReviewerName", excelRow)
checklistResult = ReadRequiredText(data(dataRow, 6), _
"ChecklistResult", excelRow)
checklistSummary = ReadRequiredMultilineText(data(dataRow, 7), _
"ChecklistSummary", excelRow)
evidencePhotoFile = ReadOptionalText(data(dataRow, 8), _
"EvidencePhotoFile", excelRow)
evidencePhotoCaption = ReadOptionalText(data(dataRow, 9), _
"EvidencePhotoCaption", excelRow)
signatureAuthorized = ReadRequiredText(data(dataRow, 10), _
"SignatureAuthorized", excelRow)
signatureFile = ReadOptionalText(data(dataRow, 11), _
"SignatureFile", excelRow)
signatureDateDisplay = ReadOptionalText(data(dataRow, 12), _
"SignatureDateDisplay", excelRow)
outputName = ReadRequiredText(data(dataRow, 13), _
"OutputName", excelRow)
ValidateIdentifier checklistId, "ChecklistID", excelRow
ValidateChecklistResult checklistResult, excelRow
ValidateOptionalPhotoPair evidencePhotoFile, evidencePhotoCaption, _
"EvidencePhoto", excelRow
ValidateSignatureContract signatureAuthorized, signatureFile, _
signatureDateDisplay, excelRow
If Len(evidencePhotoFile) > 0 And Len(signatureFile) > 0 And _
StrComp(evidencePhotoFile, signatureFile, vbTextCompare) = 0 Then
Err.Raise vbObjectError + 5055, , _
"EvidencePhotoFile and SignatureFile must be different on row " & _
excelRow & "."
End If
ValidateFileStem outputName, excelRow
If Len(evidencePhotoFile) > 0 Then
ValidateImageFileName evidencePhotoFile, "EvidencePhotoFile", excelRow
evidencePhotoPaths(dataRow) = JoinPath(imageFolder, evidencePhotoFile)
RequireNonemptyFile evidencePhotoPaths(dataRow), _
"evidence photo for row " & excelRow
End If
If Len(signatureFile) > 0 Then
ValidateImageFileName signatureFile, "SignatureFile", excelRow
signaturePaths(dataRow) = JoinPath(imageFolder, signatureFile)
RequireNonemptyFile signaturePaths(dataRow), _
"approved signature image for row " & excelRow
End If
AddUniqueValue checklistIds, checklistId, _
"ChecklistID", excelRow
AddUniqueValue outputNames, outputName, _
"OutputName", excelRow
readyCount = readyCount + 1
End If
End If
Next dataRow
If readyCount = 0 Then
Err.Raise vbObjectError + 5002, , _
"No audit checklist 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")
imageSnapshotFolder = JoinPath(inputSnapshotFolder, "ApprovedImages")
stagingFolder = JoinPath(runFolder, "Staging")
completedFolder = JoinPath(runFolder, "Completed")
fso.CreateFolder inputSnapshotFolder
fso.CreateFolder imageSnapshotFolder
fso.CreateFolder stagingFolder
fso.CreateFolder completedFolder
currentStep = "creating the controlled input snapshot"
templateSnapshotPath = JoinPath(inputSnapshotFolder, _
"StandardAuditChecklist.docx")
CopyFileToSnapshot fso, templatePath, templateSnapshotPath, _
"audit checklist template"
Set imageSnapshots = CreateObject("Scripting.Dictionary")
imageSnapshots.CompareMode = vbTextCompare
For dataRow = 1 To UBound(data, 1)
checklistStatus = CStr(data(dataRow, 14))
If StrComp(checklistStatus, READY_STATUS, _
vbBinaryCompare) = 0 Then
evidencePhotoFile = CStr(data(dataRow, 8))
If Len(evidencePhotoFile) > 0 And _
Not imageSnapshots.Exists(evidencePhotoFile) Then
sourcePhotoPath = evidencePhotoPaths(dataRow)
snapshotPhotoPath = JoinPath( _
imageSnapshotFolder, evidencePhotoFile)
CopyFileToSnapshot fso, sourcePhotoPath, _
snapshotPhotoPath, _
"evidence photo " & evidencePhotoFile
imageSnapshots.Add evidencePhotoFile, _
snapshotPhotoPath
End If
If Len(evidencePhotoFile) > 0 Then _
evidencePhotoPaths(dataRow) = imageSnapshots(evidencePhotoFile)
signatureFile = CStr(data(dataRow, 11))
If Len(signatureFile) > 0 And _
Not imageSnapshots.Exists(signatureFile) Then
sourcePhotoPath = signaturePaths(dataRow)
snapshotPhotoPath = JoinPath( _
imageSnapshotFolder, signatureFile)
CopyFileToSnapshot fso, sourcePhotoPath, _
snapshotPhotoPath, _
"approved signature image " & signatureFile
imageSnapshots.Add signatureFile, _
snapshotPhotoPath
End If
If Len(signatureFile) > 0 Then _
signaturePaths(dataRow) = imageSnapshots(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 audit checklist template snapshot"
Set wdDoc = wdApp.Documents.Open( _
templateSnapshotPath, False, True, False)
ValidateAuditChecklistTemplate wdDoc
wdDoc.Close WORD_DO_NOT_SAVE_CHANGES
Set wdDoc = Nothing
For dataRow = 1 To UBound(data, 1)
excelRow = dataRow + 1
checklistStatus = CStr(data(dataRow, 14))
If StrComp(checklistStatus, READY_STATUS, _
vbBinaryCompare) = 0 Then
currentStep = "rechecking Excel row " & excelRow
RequireRowUnchanged ws, excelRow, data, dataRow
checklistId = CStr(data(dataRow, 1))
auditArea = CStr(data(dataRow, 2))
checklistTitle = CStr(data(dataRow, 3))
reviewDateDisplay = CStr(data(dataRow, 4))
reviewerName = CStr(data(dataRow, 5))
checklistResult = CStr(data(dataRow, 6))
checklistSummary = NormalizeMultilineText(CStr(data(dataRow, 7)))
evidencePhotoFile = CStr(data(dataRow, 8))
evidencePhotoCaption = CStr(data(dataRow, 9))
signatureAuthorized = CStr(data(dataRow, 10))
signatureFile = CStr(data(dataRow, 11))
signatureDateDisplay = CStr(data(dataRow, 12))
outputName = CStr(data(dataRow, 13))
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 audit checklist row " & excelRow
Set wdDoc = wdApp.Documents.Open( _
templateSnapshotPath, False, True, False)
WriteRequiredBookmark wdDoc, "ChecklistID", checklistId
WriteRequiredBookmark wdDoc, "AuditArea", auditArea
WriteRequiredBookmark wdDoc, "ChecklistTitle", checklistTitle
WriteRequiredBookmark wdDoc, "ReviewDateDisplay", _
reviewDateDisplay
WriteRequiredBookmark wdDoc, "ReviewerName", reviewerName
WriteRequiredBookmark wdDoc, "ChecklistResult", checklistResult
WriteRequiredBookmark wdDoc, "ChecklistSummary", checklistSummary
InsertOptionalImageBookmark wdDoc, "EvidencePhoto", _
evidencePhotoPaths(dataRow)
WriteRequiredBookmark wdDoc, "EvidencePhotoCaption", evidencePhotoCaption
InsertOptionalImageBookmark wdDoc, "ReviewerSignature", _
signaturePaths(dataRow)
WriteRequiredBookmark wdDoc, "SignatureDateDisplay", signatureDateDisplay
ValidateAuditChecklistResultBeforeSave 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
currentStep = "saving DONE for audit checklist row " & excelRow
statusRow = excelRow
statusSavePending = True
ws.Cells(excelRow, 14).Value2 = DONE_STATUS
wb.Save
statusSavePending = False
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 "Audit checklist batch completed." & vbCrLf & _
"Audit checklists generated: " & generatedCount & vbCrLf & _
"Completed folder: " & completedFolder & vbCrLf & vbCrLf & _
"Review checklist data, evidence photo, signature authorization, " & _
"DOCX, and PDF 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 imageSnapshots = 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 statusSavePending And Not ws Is Nothing Then
ws.Cells(statusRow, 14).Value2 = checklistStatus
statusRecoveryMessage = vbCrLf & _
"DONE was restored to " & checklistStatus & _
" in memory because the workbook save was not confirmed."
End If
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 imageSnapshots = Nothing
Set ws = Nothing
Set wb = Nothing
On Error GoTo 0
MsgBox "Audit checklist 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 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("ChecklistID", "AuditArea", "ChecklistTitle", _
"ReviewDateDisplay", "ReviewerName", "ChecklistResult", _
"ChecklistSummary", "EvidencePhotoFile", "EvidencePhotoCaption", _
"SignatureAuthorized", "SignatureFile", "SignatureDateDisplay", _
"OutputName", "ChecklistStatus")
For columnIndex = 1 To 14
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:N").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 14
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 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) Then
Err.Raise vbObjectError + 5056, , _
fieldName & " contains an Excel error on row " & excelRow & "."
End If
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 5057, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
ReadOptionalText = CStr(rawValue)
If ReadOptionalText <> Trim$(ReadOptionalText) Then
Err.Raise vbObjectError + 5058, , _
fieldName & " has outer whitespace on row " & excelRow & "."
End If
If ContainsControlCharacter(ReadOptionalText) Then
Err.Raise vbObjectError + 5059, , _
fieldName & " contains a control character on row " & excelRow & "."
End If
End Function
Private Function ReadRequiredMultilineText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
Dim valueText As String
Dim position As Long
Dim characterCode As Long
If IsEmpty(rawValue) Or IsError(rawValue) Then
Err.Raise vbObjectError + 5060, , _
fieldName & " must contain reviewed text on row " & excelRow & "."
End If
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 5061, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
valueText = CStr(rawValue)
valueText = Replace(valueText, vbCrLf, vbLf)
valueText = Replace(valueText, vbCr, vbLf)
If Len(valueText) = 0 Then
Err.Raise vbObjectError + 5062, , _
fieldName & " is blank on row " & excelRow & "."
End If
If valueText <> Trim$(valueText) Or _
Left$(valueText, 1) = vbLf Or Right$(valueText, 1) = vbLf Then
Err.Raise vbObjectError + 5063, , _
fieldName & " has outer whitespace on row " & excelRow & "."
End If
For position = 1 To Len(valueText)
characterCode = AscW(Mid$(valueText, position, 1))
If characterCode >= 0 And characterCode <= 31 And _
characterCode <> 10 Then
Err.Raise vbObjectError + 5064, , _
fieldName & " contains an unsupported control character on row " & _
excelRow & "."
End If
Next position
ReadRequiredMultilineText = NormalizeMultilineText(valueText)
End Function
Private Function NormalizeMultilineText(ByVal valueText As String) As String
valueText = Replace(valueText, vbCrLf, vbLf)
valueText = Replace(valueText, vbCr, vbLf)
NormalizeMultilineText = Replace(valueText, vbLf, vbCr)
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, , _
"ChecklistStatus must be exactly READY, HOLD, or DONE on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateChecklistResult(ByVal resultText As String, _
ByVal excelRow As Long)
If StrComp(resultText, "PASS", vbBinaryCompare) <> 0 And _
StrComp(resultText, "FOLLOW-UP", vbBinaryCompare) <> 0 And _
StrComp(resultText, "FAIL", vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5054, , _
"ChecklistResult must be exactly PASS, FOLLOW-UP, or FAIL on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateSignatureContract(ByVal authorizationText As String, _
ByVal signatureFile As String, _
ByVal signatureDateDisplay As String, _
ByVal excelRow As Long)
If authorizationText <> "YES" And authorizationText <> "NO" Then
Err.Raise vbObjectError + 5069, , _
"SignatureAuthorized must be exactly YES or NO on row " & _
excelRow & "."
End If
If authorizationText = "YES" Then
If Len(signatureFile) = 0 Or Len(signatureDateDisplay) = 0 Then
Err.Raise vbObjectError + 5070, , _
"YES requires SignatureFile and SignatureDateDisplay on row " & _
excelRow & "."
End If
ElseIf Len(signatureFile) > 0 Or Len(signatureDateDisplay) > 0 Then
Err.Raise vbObjectError + 5071, , _
"SignatureFile and SignatureDateDisplay must be blank when " & _
"SignatureAuthorized is NO on row " & excelRow & "."
End If
End Sub
Private Sub ValidateOptionalPhotoPair(ByVal photoFile As String, _
ByVal photoCaption As String, _
ByVal photoLabel As String, _
ByVal excelRow As Long)
If Len(photoFile) = 0 And Len(photoCaption) > 0 Then
Err.Raise vbObjectError + 5065, , _
photoLabel & "Caption requires " & photoLabel & _
"File on row " & excelRow & "."
End If
If Len(photoFile) > 0 And Len(photoCaption) = 0 Then
Err.Raise vbObjectError + 5066, , _
photoLabel & "File requires " & photoLabel & _
"Caption 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 fieldName As String, _
ByVal excelRow As Long)
Dim extensionPosition As Long
Dim extensionText As String
Dim baseName 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, , _
fieldName & " 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, , _
fieldName & " cannot end with a period or space on row " & _
excelRow & "."
End If
extensionPosition = InStrRev(fileName, ".")
If extensionPosition <= 1 Then
Err.Raise vbObjectError + 5019, , _
fieldName & " 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, , _
fieldName & " must use PNG, JPG, or JPEG on row " & excelRow & "."
End If
baseName = Left$(fileName, extensionPosition - 1)
If IsReservedWindowsName(baseName) Then
Err.Raise vbObjectError + 5053, , _
fieldName & " uses a reserved Windows name 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 ValidateAuditChecklistTemplate(ByVal doc As Object)
RejectTrackedChanges doc, "audit checklist template snapshot"
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5026, , _
"Remove comments from StandardAuditChecklist.docx."
End If
RequireBookmark doc, "ChecklistID"
RequireBookmark doc, "AuditArea"
RequireBookmark doc, "ChecklistTitle"
RequireBookmark doc, "ReviewDateDisplay"
RequireBookmark doc, "ReviewerName"
RequireBookmark doc, "ChecklistResult"
RequireBookmark doc, "ChecklistSummary"
RequireBookmark doc, "EvidencePhoto"
RequireBookmark doc, "EvidencePhotoCaption"
RequireBookmark doc, "ReviewerSignature"
RequireBookmark doc, "SignatureDateDisplay"
End Sub
Private Sub ValidateAuditChecklistResultBeforeSave(ByVal doc As Object)
RejectTrackedChanges doc, "generated audit checklist"
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5050, , _
"The generated audit checklist contains a comment before saving."
End If
RequireBookmark doc, "ChecklistID"
RequireBookmark doc, "AuditArea"
RequireBookmark doc, "ChecklistTitle"
RequireBookmark doc, "ReviewDateDisplay"
RequireBookmark doc, "ReviewerName"
RequireBookmark doc, "ChecklistResult"
RequireBookmark doc, "ChecklistSummary"
RequireBookmark doc, "EvidencePhoto"
RequireBookmark doc, "EvidencePhotoCaption"
RequireBookmark doc, "ReviewerSignature"
RequireBookmark doc, "SignatureDateDisplay"
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 InsertOptionalImageBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal imagePath 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, , _
"Image bookmark contains a nested object: " & bookmarkName
End If
If InStr(1, targetRange.Text, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, targetRange.Text, Chr$(7), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5033, , _
"Image bookmark includes a paragraph or cell marker: " & _
bookmarkName
End If
targetRange.Text = ""
targetRange.Collapse WORD_COLLAPSE_START
If Len(imagePath) = 0 Then
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 5067, , _
"Empty image bookmark was not restored: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 0 Then
Err.Raise vbObjectError + 5068, , _
"Empty image bookmark contains an embedded picture."
End If
Exit Sub
End If
Set picture = doc.InlineShapes.AddPicture( _
imagePath, False, True, targetRange)
If picture.Width <= 0 Or picture.Height <= 0 Then
Err.Raise vbObjectError + 5034, , _
"Word inserted an invalid image."
End If
picture.LockAspectRatio = OFFICE_TRUE
If bookmarkName = "ReviewerSignature" Then
maximumWidth = MAX_SIGNATURE_WIDTH_CM * POINTS_PER_CM
maximumHeight = MAX_SIGNATURE_HEIGHT_CM * POINTS_PER_CM
Else
maximumWidth = MAX_PHOTO_WIDTH_CM * POINTS_PER_CM
maximumHeight = MAX_PHOTO_HEIGHT_CM * POINTS_PER_CM
End If
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, , _
"Image bookmark was not restored: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 1 Then
Err.Raise vbObjectError + 5036, , _
"Image bookmark does not contain one embedded picture: " & _
bookmarkName
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 14
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 FunctionMicrosoft distinguishes a signature picture from a digital signature. The example also uses Word’s documented embedded picture insertion, DOCX saving, and PDF export.
Review the generated checklist before release or signing
Checklist identity
Match ChecklistID, audit area, title, review date, reviewer, and output filename to the approved row.
Result
Confirm PASS, FOLLOW-UP, or FAIL reflects the authorized audit conclusion and supporting work.
Narrative
Compare every paragraph with the approved source and check that required exceptions and follow-up are present.
Evidence
Verify the optional photograph belongs to the checklist and its caption does not make an unsupported claim.
Signature
Confirm authorization, image identity, displayed date, and the required signing method. Do not infer digital assurance from appearance.
Word and PDF
Inspect image scaling, paragraph flow, tables, page breaks, headers, footers, and every PDF page before release.
Know what this workflow does not establish
No audit judgment
The macro cannot choose the correct ChecklistResult or determine compliance, effectiveness, sufficiency, or acceptance.
No signer authentication
SignatureAuthorized is supplied data. The VBA cannot verify identity, consent, authority, or certificate status.
No digital signature
Embedding an image does not create certificate-backed authenticity, integrity protection, or tamper evidence.
No atomic transaction
Moving DOCX/PDF files and saving DONE are separate operations. Retained run folders require reconciliation after a failure.
No concurrency guarantee
Run-specific folders reduce collisions, but shared storage and simultaneous writers require separate controls.
No records program
Approval evidence, e-signatures, delivery, access logs, retention, legal holds, and disposal remain separate workflows.
Use Factory for standardized audit checklists
DocxForge Pro Factory applies one approved Word template to many independent Excel rows and creates a separate document for every selected row.
Each checklist row is complete before generation, every READY row uses the same template, and each result becomes its own file. DocxForge Pro generates the document; signature authorization and digital-signature assurance remain separate controls.


Frequently asked questions
Is this a Factory workflow?
Yes. Each independent AuditChecklists row uses the same StandardAuditChecklist.docx template and produces its own DOCX/PDF pair.
Does the VBA create a digital signature?
No. It can embed an approved image of a handwritten signature when SignatureAuthorized is YES. An image is not a certificate-backed digital signature and does not prove signer identity, document integrity, or authorization.
What happens when SignatureAuthorized is NO?
SignatureFile and SignatureDateDisplay must both be blank. The macro clears the signature image and date bookmarks in the generated checklist.
Can ChecklistSummary contain multiple paragraphs?
Yes. Excel line breaks are normalized into Word paragraphs. Tabs and other control characters remain blocked to protect the fixed template structure.
Does DONE mean the audit checklist passed?
No. DONE confirms that the DOCX/PDF pair was generated and the workbook save succeeded. The separate ChecklistResult value and the release decision still require authorized human review.
Can the macro overwrite an existing audit checklist?
It rejects existing staging and completed targets inside its run folder and detects duplicate output names during preflight. Shared storage and concurrent writers still require separate controls.