How to Create Equipment Checklists with Photos and PDF Output
Turn reviewed equipment-check rows into consistent Word checklists and PDFs with exact PASS/FAIL logic, two controlled photos, and one approved template.
Quick answer: separate checklist logic from document generation
Use one EquipmentChecks row for one completed check event. Validate the exact result vocabulary first, require a consistent overall result, then fill one standard Word template and create a separate DOCX/PDF pair.
The macro can reject inconsistent values such as a failed emergency-stop check paired with OverallResult = PASS. It cannot decide which checks apply, whether an observed condition is acceptable, or whether the equipment may return to service.
Approve the checklist before creating the spreadsheet
Equipment class
Define the machine, asset, or device class covered by the checklist.
Authorized checks
Use approved inspection and maintenance procedures rather than copying the four sample items blindly.
Acceptance criteria
Document what PASS, FAIL, and N/A mean for each item and who may assign them.
Failure response
Define isolation, escalation, corrective action, and reinspection rules outside the macro.
Evidence requirement
Specify the two images, captions, retention period, and access controls required for the record.
Release authority
Name the person or role that reviews the checklist and authorizes the next operational step.
Create the exact EquipmentChecks contract in columns A–R
A — ChecklistID
Unique identifier for this check event.
B — EquipmentID
Stable asset identifier; it may repeat across different check dates.
C — EquipmentName
Reviewed asset name or model description.
D — Location
Document-ready facility, zone, room, or asset position.
E — CheckDateDisplay
Reviewed date text exactly as it should appear in Word.
F — InspectorName
Reviewed name; the macro does not verify competence or authorization.
G–J — Four check results
GuardingCheck, EmergencyStopCheck, LeakCheck, and LabelCheck use exactly PASS, FAIL, or N/A.
K — OverallResult
Use PASS when no applicable item fails; use FAIL when any item fails.
L — FollowUpText
Document-ready action text. Use an approved no-action statement when nothing is required.
M–N — Photo1File / Photo1Caption
First filename-only evidence image and reviewed caption.
O–P — Photo2File / Photo2Caption
Second, different filename-only evidence image and reviewed caption.
Q–R — OutputName / ChecklistStatus
Use a unique Windows-safe filename stem and exactly READY, HOLD, or DONE.
Make the result logic explicit and testable
Exact values
Lowercase, outer spaces, synonyms, blanks, and formula errors are rejected for READY rows.
At least one applicable check
A row with all four items marked N/A cannot produce a meaningful overall result.
Any FAIL means FAIL
One or more failed items require OverallResult = FAIL.
No FAIL means PASS
When at least one item applies and none fails, OverallResult must be PASS.
Follow-up stays separate
The text field records the reviewed response but does not automatically close, assign, or verify corrective work.
DONE stays separate
ChecklistStatus = DONE means generation completed; it does not replace OverallResult or release approval.
Prepare the photo folder and Word template
Controlled photo folder
Store reviewed PNG, JPG, or JPEG files in Assets\Photos and enter filenames only.
Distinct evidence files
Photo1File and Photo2File must differ for every READY checklist row.
Identity bookmarks
ChecklistID, EquipmentID, EquipmentName, Location, CheckDateDisplay, and InspectorName.
Result bookmarks
GuardingCheck, EmergencyStopCheck, LeakCheck, LabelCheck, OverallResult, and FollowUpText.
Photo bookmarks
Photo1, Photo1Caption, Photo2, and Photo2Caption.
Clean template state
Turn off Track Changes, resolve revisions in every story, remove comments, and keep bookmark ranges free from nested objects.
Run the equipment checklist batch through six gates
1. Review the row
Confirm the equipment identity, date, inspector, four results, follow-up text, photos, captions, and filename.
2. Preflight every READY record
Reject inconsistent results, duplicate ChecklistID values, duplicate outputs, missing photos, and unsafe filenames before Word starts.
3. Snapshot the approved inputs
Copy the template and every used photo into the run-specific InputSnapshot.
4. Recheck A–R
Stop if any value in the current row changes after the original preflight.
5. Build the pair
Fill bookmarks, embed both images, validate the Word result, then stage and move nonempty DOCX/PDF files.
6. Save and review
Save DONE after the pair exists, then complete the separate technical and operational release review.
Free Excel VBA example: generate equipment checklists
Save the workbook as .xlsm. Create EquipmentChecks with the exact A–R headers above. Put StandardEquipmentChecklist.docx in Templates, store approved images in Assets\Photos, and run GenerateEquipmentChecklistBatch.
Option Explicit
Private Const DATA_SHEET As String = "EquipmentChecks"
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#
Public Sub GenerateEquipmentChecklistBatch()
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 photoFolder As String
Dim outputRoot As String
Dim runFolder As String
Dim inputSnapshotFolder As String
Dim photoSnapshotFolder 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 photo1Paths() As String
Dim photo2Paths() 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 equipmentId As String
Dim equipmentName As String
Dim location As String
Dim checkDateDisplay As String
Dim inspectorName As String
Dim guardingCheck As String
Dim emergencyStopCheck As String
Dim leakCheck As String
Dim labelCheck As String
Dim overallResult As String
Dim followUpText As String
Dim photo1File As String
Dim photo1Caption As String
Dim photo2File As String
Dim photo2Caption As String
Dim outputName As String
Dim checklistStatus As String
Dim checklistIds As Object
Dim outputNames As Object
Dim photoSnapshots 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 EquipmentChecks sheet is protected against status updates."
End If
ValidateHeaders ws
lastRow = FindLastDataRow(ws)
If lastRow < 2 Then
Err.Raise vbObjectError + 5001, , _
"No equipment checklist records were found below row 1."
End If
templatePath = JoinPath(JoinPath(wb.Path, "Templates"), _
"StandardEquipmentChecklist.docx")
photoFolder = JoinPath(JoinPath(wb.Path, "Assets"), _
"Photos")
outputRoot = JoinPath(wb.Path, "GeneratedEquipmentChecklists")
RequireNonemptyFile templatePath, "equipment checklist template"
RequireFolder photoFolder, "photo asset folder"
data = ws.Range("A2:R" & lastRow).Value2
ReDim photo1Paths(1 To UBound(data, 1))
ReDim photo2Paths(1 To UBound(data, 1))
Set checklistIds = CreateObject("Scripting.Dictionary")
checklistIds.CompareMode = vbTextCompare
Set outputNames = CreateObject("Scripting.Dictionary")
outputNames.CompareMode = vbTextCompare
currentStep = "preflighting Excel equipment checklist rows"
For dataRow = 1 To UBound(data, 1)
excelRow = dataRow + 1
If Not DataRowIsBlank(data, dataRow) Then
checklistStatus = ReadRequiredText(data(dataRow, 18), _
"ChecklistStatus", excelRow)
ValidateStatus checklistStatus, excelRow
If StrComp(checklistStatus, READY_STATUS, _
vbBinaryCompare) = 0 Then
checklistId = ReadRequiredText(data(dataRow, 1), _
"ChecklistID", excelRow)
equipmentId = ReadRequiredText(data(dataRow, 2), _
"EquipmentID", excelRow)
equipmentName = ReadRequiredText(data(dataRow, 3), _
"EquipmentName", excelRow)
location = ReadRequiredText(data(dataRow, 4), _
"Location", excelRow)
checkDateDisplay = ReadRequiredText(data(dataRow, 5), _
"CheckDateDisplay", excelRow)
inspectorName = ReadRequiredText(data(dataRow, 6), _
"InspectorName", excelRow)
guardingCheck = ReadRequiredText(data(dataRow, 7), _
"GuardingCheck", excelRow)
emergencyStopCheck = ReadRequiredText(data(dataRow, 8), _
"EmergencyStopCheck", excelRow)
leakCheck = ReadRequiredText(data(dataRow, 9), _
"LeakCheck", excelRow)
labelCheck = ReadRequiredText(data(dataRow, 10), _
"LabelCheck", excelRow)
overallResult = ReadRequiredText(data(dataRow, 11), _
"OverallResult", excelRow)
followUpText = ReadRequiredText(data(dataRow, 12), _
"FollowUpText", excelRow)
photo1File = ReadRequiredText(data(dataRow, 13), _
"Photo1File", excelRow)
photo1Caption = ReadRequiredText(data(dataRow, 14), _
"Photo1Caption", excelRow)
photo2File = ReadRequiredText(data(dataRow, 15), _
"Photo2File", excelRow)
photo2Caption = ReadRequiredText(data(dataRow, 16), _
"Photo2Caption", excelRow)
outputName = ReadRequiredText(data(dataRow, 17), _
"OutputName", excelRow)
ValidateIdentifier checklistId, "ChecklistID", excelRow
ValidateIdentifier equipmentId, "EquipmentID", excelRow
ValidateCheckResult guardingCheck, "GuardingCheck", excelRow
ValidateCheckResult emergencyStopCheck, _
"EmergencyStopCheck", excelRow
ValidateCheckResult leakCheck, "LeakCheck", excelRow
ValidateCheckResult labelCheck, "LabelCheck", excelRow
ValidateOverallConsistency guardingCheck, _
emergencyStopCheck, leakCheck, labelCheck, _
overallResult, excelRow
If StrComp(photo1File, photo2File, vbTextCompare) = 0 Then
Err.Raise vbObjectError + 5055, , _
"Photo1File and Photo2File must be different on row " & _
excelRow & "."
End If
ValidateFileStem outputName, excelRow
ValidateImageFileName photo1File, "Photo1File", excelRow
ValidateImageFileName photo2File, "Photo2File", excelRow
photo1Paths(dataRow) = JoinPath(photoFolder, photo1File)
photo2Paths(dataRow) = JoinPath(photoFolder, photo2File)
RequireNonemptyFile photo1Paths(dataRow), _
"first photo for row " & excelRow
RequireNonemptyFile photo2Paths(dataRow), _
"second photo for row " & excelRow
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 equipment 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")
photoSnapshotFolder = JoinPath(inputSnapshotFolder, "Photos")
stagingFolder = JoinPath(runFolder, "Staging")
completedFolder = JoinPath(runFolder, "Completed")
fso.CreateFolder inputSnapshotFolder
fso.CreateFolder photoSnapshotFolder
fso.CreateFolder stagingFolder
fso.CreateFolder completedFolder
currentStep = "creating the controlled input snapshot"
templateSnapshotPath = JoinPath(inputSnapshotFolder, _
"StandardEquipmentChecklist.docx")
CopyFileToSnapshot fso, templatePath, templateSnapshotPath, _
"equipment checklist template"
Set photoSnapshots = CreateObject("Scripting.Dictionary")
photoSnapshots.CompareMode = vbTextCompare
For dataRow = 1 To UBound(data, 1)
checklistStatus = CStr(data(dataRow, 18))
If StrComp(checklistStatus, READY_STATUS, _
vbBinaryCompare) = 0 Then
photo1File = CStr(data(dataRow, 13))
If Not photoSnapshots.Exists(photo1File) Then
sourcePhotoPath = photo1Paths(dataRow)
snapshotPhotoPath = JoinPath( _
photoSnapshotFolder, photo1File)
CopyFileToSnapshot fso, sourcePhotoPath, _
snapshotPhotoPath, _
"photo " & photo1File
photoSnapshots.Add photo1File, _
snapshotPhotoPath
End If
photo1Paths(dataRow) = photoSnapshots(photo1File)
photo2File = CStr(data(dataRow, 15))
If Not photoSnapshots.Exists(photo2File) Then
sourcePhotoPath = photo2Paths(dataRow)
snapshotPhotoPath = JoinPath( _
photoSnapshotFolder, photo2File)
CopyFileToSnapshot fso, sourcePhotoPath, _
snapshotPhotoPath, _
"photo " & photo2File
photoSnapshots.Add photo2File, _
snapshotPhotoPath
End If
photo2Paths(dataRow) = photoSnapshots(photo2File)
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 equipment checklist template snapshot"
Set wdDoc = wdApp.Documents.Open( _
templateSnapshotPath, False, True, False)
ValidateEquipmentChecklistTemplate 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, 18))
If StrComp(checklistStatus, READY_STATUS, _
vbBinaryCompare) = 0 Then
currentStep = "rechecking Excel row " & excelRow
RequireRowUnchanged ws, excelRow, data, dataRow
checklistId = CStr(data(dataRow, 1))
equipmentId = CStr(data(dataRow, 2))
equipmentName = CStr(data(dataRow, 3))
location = CStr(data(dataRow, 4))
checkDateDisplay = CStr(data(dataRow, 5))
inspectorName = CStr(data(dataRow, 6))
guardingCheck = CStr(data(dataRow, 7))
emergencyStopCheck = CStr(data(dataRow, 8))
leakCheck = CStr(data(dataRow, 9))
labelCheck = CStr(data(dataRow, 10))
overallResult = CStr(data(dataRow, 11))
followUpText = CStr(data(dataRow, 12))
photo1Caption = CStr(data(dataRow, 14))
photo2Caption = CStr(data(dataRow, 16))
outputName = CStr(data(dataRow, 17))
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 equipment checklist row " & excelRow
Set wdDoc = wdApp.Documents.Open( _
templateSnapshotPath, False, True, False)
WriteRequiredBookmark wdDoc, "ChecklistID", checklistId
WriteRequiredBookmark wdDoc, "EquipmentID", equipmentId
WriteRequiredBookmark wdDoc, "EquipmentName", equipmentName
WriteRequiredBookmark wdDoc, "Location", location
WriteRequiredBookmark wdDoc, "CheckDateDisplay", _
checkDateDisplay
WriteRequiredBookmark wdDoc, "InspectorName", inspectorName
WriteRequiredBookmark wdDoc, "GuardingCheck", guardingCheck
WriteRequiredBookmark wdDoc, "EmergencyStopCheck", _
emergencyStopCheck
WriteRequiredBookmark wdDoc, "LeakCheck", leakCheck
WriteRequiredBookmark wdDoc, "LabelCheck", labelCheck
WriteRequiredBookmark wdDoc, "OverallResult", overallResult
WriteRequiredBookmark wdDoc, "FollowUpText", followUpText
InsertPhotoBookmark wdDoc, "Photo1", _
photo1Paths(dataRow)
WriteRequiredBookmark wdDoc, "Photo1Caption", photo1Caption
InsertPhotoBookmark wdDoc, "Photo2", _
photo2Paths(dataRow)
WriteRequiredBookmark wdDoc, "Photo2Caption", photo2Caption
ValidateEquipmentChecklistResultBeforeSave 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 equipment checklist row " & excelRow
statusRow = excelRow
statusSavePending = True
ws.Cells(excelRow, 18).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 "Equipment checklist batch completed." & vbCrLf & _
"Equipment checklists generated: " & generatedCount & vbCrLf & _
"Completed folder: " & completedFolder & vbCrLf & vbCrLf & _
"Review equipment checklist data, both photos, 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 photoSnapshots = 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, 18).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 photoSnapshots = Nothing
Set ws = Nothing
Set wb = Nothing
On Error GoTo 0
MsgBox "Equipment 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", "EquipmentID", "EquipmentName", _
"Location", "CheckDateDisplay", "InspectorName", _
"GuardingCheck", "EmergencyStopCheck", "LeakCheck", _
"LabelCheck", "OverallResult", "FollowUpText", _
"Photo1File", "Photo1Caption", "Photo2File", _
"Photo2Caption", "OutputName", "ChecklistStatus")
For columnIndex = 1 To 18
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:R").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 18
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, , _
"ChecklistStatus must be exactly READY, HOLD, or DONE on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateCheckResult(ByVal resultText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
If StrComp(resultText, "PASS", vbBinaryCompare) <> 0 And _
StrComp(resultText, "FAIL", vbBinaryCompare) <> 0 And _
StrComp(resultText, "N/A", vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5054, , _
fieldName & " must be exactly PASS, FAIL, or N/A on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateOverallConsistency(ByVal guardingCheck As String, _
ByVal emergencyStopCheck As String, _
ByVal leakCheck As String, _
ByVal labelCheck As String, _
ByVal overallResult As String, _
ByVal excelRow As Long)
Dim failedCount As Long
Dim applicableCount As Long
failedCount = CountExactValue(Array(guardingCheck, _
emergencyStopCheck, leakCheck, labelCheck), "FAIL")
applicableCount = 4 - CountExactValue(Array(guardingCheck, _
emergencyStopCheck, leakCheck, labelCheck), "N/A")
If applicableCount = 0 Then
Err.Raise vbObjectError + 5056, , _
"At least one equipment check must be applicable on row " & _
excelRow & "."
End If
If failedCount > 0 Then
If StrComp(overallResult, "FAIL", vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5057, , _
"OverallResult must be FAIL when any check is FAIL on row " & _
excelRow & "."
End If
ElseIf StrComp(overallResult, "PASS", vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5058, , _
"OverallResult must be PASS when no check is FAIL on row " & _
excelRow & "."
End If
End Sub
Private Function CountExactValue(ByVal values As Variant, _
ByVal expectedValue As String) As Long
Dim item As Variant
For Each item In values
If StrComp(CStr(item), expectedValue, vbBinaryCompare) = 0 Then
CountExactValue = CountExactValue + 1
End If
Next item
End Function
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 ValidateEquipmentChecklistTemplate(ByVal doc As Object)
RejectTrackedChanges doc, "equipment checklist template snapshot"
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5026, , _
"Remove comments from StandardEquipmentChecklist.docx."
End If
RequireBookmark doc, "ChecklistID"
RequireBookmark doc, "EquipmentID"
RequireBookmark doc, "EquipmentName"
RequireBookmark doc, "Location"
RequireBookmark doc, "CheckDateDisplay"
RequireBookmark doc, "InspectorName"
RequireBookmark doc, "GuardingCheck"
RequireBookmark doc, "EmergencyStopCheck"
RequireBookmark doc, "LeakCheck"
RequireBookmark doc, "LabelCheck"
RequireBookmark doc, "OverallResult"
RequireBookmark doc, "FollowUpText"
RequireBookmark doc, "Photo1"
RequireBookmark doc, "Photo1Caption"
RequireBookmark doc, "Photo2"
RequireBookmark doc, "Photo2Caption"
End Sub
Private Sub ValidateEquipmentChecklistResultBeforeSave(ByVal doc As Object)
RejectTrackedChanges doc, "generated equipment checklist"
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5050, , _
"The generated equipment checklist contains a comment before saving."
End If
RequireBookmark doc, "ChecklistID"
RequireBookmark doc, "EquipmentID"
RequireBookmark doc, "EquipmentName"
RequireBookmark doc, "Location"
RequireBookmark doc, "CheckDateDisplay"
RequireBookmark doc, "InspectorName"
RequireBookmark doc, "GuardingCheck"
RequireBookmark doc, "EmergencyStopCheck"
RequireBookmark doc, "LeakCheck"
RequireBookmark doc, "LabelCheck"
RequireBookmark doc, "OverallResult"
RequireBookmark doc, "FollowUpText"
RequireBookmark doc, "Photo1"
RequireBookmark doc, "Photo1Caption"
RequireBookmark doc, "Photo2"
RequireBookmark doc, "Photo2Caption"
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 InsertPhotoBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal photoPath 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, , _
"Photo 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, , _
"Photo bookmark includes a paragraph or cell marker."
End If
targetRange.Text = ""
targetRange.Collapse WORD_COLLAPSE_START
Set picture = doc.InlineShapes.AddPicture( _
photoPath, False, True, targetRange)
If picture.Width <= 0 Or picture.Height <= 0 Then
Err.Raise vbObjectError + 5034, , _
"Word inserted an invalid photo."
End If
picture.LockAspectRatio = OFFICE_TRUE
maximumWidth = MAX_PHOTO_WIDTH_CM * POINTS_PER_CM
maximumHeight = MAX_PHOTO_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, , _
"Photo bookmark was not restored."
End If
If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 1 Then
Err.Raise vbObjectError + 5036, , _
"Photo 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 18
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 Word’s documented bookmark checks, embedded picture insertion, DOCX saving, and PDF export.
Review the generated checklist before release
Record identity
Match ChecklistID, EquipmentID, equipment name, location, date, inspector, and output filename.
Check results
Confirm each PASS, FAIL, or N/A value against the approved procedure and recorded observation.
Overall result
Verify the macro’s consistency rule and the qualified reviewer’s actual operational decision.
Follow-up
Check the action, responsible owner, due date, isolation state, and reinspection requirement in the surrounding process.
Photos and captions
Confirm both images belong to the asset and event and that the captions describe what is visible.
DOCX and PDF
Inspect tables, wrapping, photo scaling, page breaks, headers, footers, and every exported page.
Keep the macro inside its technical limits
Not a safety standard
The four sample checks are not complete instructions for any equipment class.
No authorization check
The macro does not verify inspector training, competence, signature, or release authority.
No sensor or maintenance integration
It does not read machine telemetry, work orders, calibration records, or lockout state.
No image authentication
Filename, size, copying, and insertion checks do not prove provenance or content.
No atomic file-and-status transaction
Completed files and the Excel DONE save remain separate operations after a failure.
Fixed layout only
Open-ended checklist items or variable photo sets require another data model and template architecture.
Use Factory for repeated equipment checklist records
DocxForge Pro Factory applies one standard Word template to many independent Excel rows and creates a separate document for each row.
Every EquipmentChecks row contains a complete reviewed checklist event, all READY rows use the same approved template, and each event becomes its own output file.


Frequently asked questions
Are the four equipment checks suitable for every machine?
No. Guarding, emergency stop, leaks, and labels are only a technical example. A qualified owner must define the correct checklist, acceptance criteria, frequency, and authorization rules for each equipment class.
How does the macro keep OverallResult consistent?
Each check must be exactly PASS, FAIL, or N/A. If any applicable check is FAIL, OverallResult must be FAIL; otherwise it must be PASS. At least one check must be applicable.
Can the same EquipmentID appear in several rows?
Yes. Equipment can be checked repeatedly over time. ChecklistID and OutputName must remain unique for every READY checklist event.
Does DONE mean the equipment is safe to operate?
No. DONE records successful document generation and workbook saving. It is not an engineering judgment, maintenance release, regulatory approval, or authorization to operate equipment.
Why does the example require two different photos?
The fixed Word layout expects two distinct evidence images and captions. A workflow with optional or variable photo counts needs a different data model and template design.
Is this a Factory workflow?
Yes. Many independent EquipmentChecks rows use one standard Word checklist template, producing one separate DOCX/PDF pair per READY row.