How to Automate Home Inspection Reports with Excel, Word, and PDF
Treat one home inspection as one Contractor case: review its property data, findings, and photos, then create the detailed report and related case documents from separate Word templates.
Quick answer: model the inspection as one Contractor case
Keep property-level details on one Inspections row and each observation on its own Findings row, linked by InspectionID. Review that case once, generate its detailed report, then reuse the same case data in other documents built from separate Word templates.
Property record
Stores the stable inspection ID, client, address, inspection date, inspector, and generation status.
Finding records
Stores one section, finding title, severity, narrative, optional photo path, and optional caption per row.
Detailed template
Owns the cover, fixed language, styles, headers, footers, and the anchor where repeated findings begin.
Case document set
Create the detailed report, repair summary, client cover document, or other case outputs from separate controlled templates.
This guide follows Contractor logic: one property case, detailed findings and photos, and potentially several documents from different templates. The row-based home inspection workflow follows Factory logic: many complete Excel rows, one standard template, and one output per row.
If the complete report already fits fixed columns on one row, start with the simpler one-row-to-one-document workflow.
Use two sheets instead of forcing every photo into one row
A wide sheet with Photo1, Photo2, Photo3 and Finding1, Finding2, Finding3 becomes difficult to validate and extend. A parent-and-detail structure keeps every finding explicit.
Inspections: InspectionID
Use a unique text ID as the relationship key and output filename. Format the column as Text before importing IDs when leading zeros matter.
Inspections: case fields
Store ClientName, PropertyAddress, InspectionDateDisplay, InspectorName, and ReportStatus in exact documented columns.
Findings: relationship
Repeat the matching InspectionID on every related finding row. The macro uses a case-insensitive exact comparison.
Findings: report content
Store SectionName, FindingTitle, Severity, FindingText, PhotoPath, and PhotoCaption as reviewed, document-ready values.
Relative photo paths
Use paths relative to the workbook's Photos folder, such as HI-0042\roof-flashing.jpg. Do not store drive letters or UNC paths in this example.
READY is only a gate
The macro requires ReportStatus = READY. That cell does not authenticate the inspector, prove approval, or replace a release record.
Prepare the detailed Word template
Put DetailedInspectionTemplate.docx in Templates beside the workbook. The VBA example uses this template for the main detailed report. In a Contractor workflow, keep every additional document type in its own Word template.
Five fixed-field bookmarks
Add InspectionID, ClientName, PropertyAddress, InspectionDate, and InspectorName where the case-level values belong.
One findings anchor
Add FindingsStart at the exact location where the first generated finding block should begin.
Built-in Word styles
The example uses Heading 2, Normal, and Caption by their built-in IDs, avoiding dependence on localized style names.
Variable-length testing
Test long narratives, portrait and landscape photos, page breaks, headers, footers, tables, and the final page.
The reusable template guide covers styles, placeholders, and variable-length testing in more detail.
Free VBA fallback: one detailed inspection with repeated findings
The macro validates the selected inspection, exact headers, all related finding rows, every referenced photo, the template, required bookmarks, output names, and existing files before Word saves anything. It embeds photos as inline shapes, shrinks oversized images without enlarging small ones, and keeps their aspect ratio.
Save the workbook as .xlsm. Create Inspections with columns A–F: InspectionID, ClientName, PropertyAddress, InspectionDateDisplay, InspectorName, ReportStatus. Create Findings with columns A–G: InspectionID, SectionName, FindingTitle, Severity, FindingText, PhotoPath, PhotoCaption.
Use Templates\DetailedInspectionTemplate.docx and place referenced images below Photos. The macro creates InspectionReports and writes Inspection_<InspectionID>.docx plus optional PDF.
Synthetic pilot data
Use one artificial property record and at least three finding rows: one text-only finding, one landscape photo, and one portrait photo. Do not begin with a client address or real inspection evidence.
Select the HI-0042 row on Inspections. The result should contain all and only the findings linked to that ID, with photos embedded, and save as InspectionReports\Inspection_HI-0042.docx and InspectionReports\Inspection_HI-0042.pdf. A second run must refuse to overwrite them.
Option Explicit
Private Const wdFormatXMLDocument As Long = 12
Private Const wdExportFormatPDF As Long = 17
Private Const wdExportOptimizeForPrint As Long = 0
Private Const wdDoNotSaveChanges As Long = 0
Private Const wdAlertsNone As Long = 0
Private Const wdCollapseEnd As Long = 0
Private Const wdStyleNormal As Long = -1
Private Const wdStyleHeading2 As Long = -3
Private Const wdStyleCaption As Long = -35
Private Const msoTrue As Long = -1
Private Const MAX_PHOTO_WIDTH_POINTS As Double = 453.54
Private Const MAX_PHOTO_HEIGHT_POINTS As Double = 311.81
Public Sub GenerateDetailedInspectionReport()
Const INSPECTIONS_SHEET As String = "Inspections"
Const FINDINGS_SHEET As String = "Findings"
Const TEMPLATE_SUBFOLDER As String = "Templates"
Const TEMPLATE_FILE As String = "DetailedInspectionTemplate.docx"
Const PHOTO_SUBFOLDER As String = "Photos"
Const OUTPUT_SUBFOLDER As String = "InspectionReports"
Const REQUIRED_STATUS As String = "READY"
Const CREATE_PDF As Boolean = True
Dim wsInspections As Worksheet
Dim wsFindings As Worksheet
Dim fso As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim lastCell As Range
Dim findingsData As Variant
Dim matchingRows As Collection
Dim findingsCursor As Object
Dim selectedRow As Long
Dim lastFindingRow As Long
Dim dataRow As Long
Dim findingNumber As Long
Dim findingIndex As Variant
Dim findingsStartPosition As Long
Dim inspectionId As String
Dim clientName As String
Dim propertyAddress As String
Dim inspectionDateText As String
Dim inspectorName As String
Dim reportStatus As String
Dim sectionName As String
Dim findingTitle As String
Dim severity As String
Dim findingText As String
Dim photoRelativePath As String
Dim photoCaption As String
Dim photoFullPath As String
Dim templatePath As String
Dim photoRoot As String
Dim outputFolder As String
Dim fileStem As String
Dim docxPath As String
Dim pdfPath As String
Dim currentStep As String
Dim previousScreenUpdating As Boolean
Dim screenStateCaptured As Boolean
Dim errorNumber As Long
Dim errorDescription As String
On Error GoTo CleanFail
previousScreenUpdating = Application.ScreenUpdating
screenStateCaptured = True
Application.ScreenUpdating = False
currentStep = "checking the workbook"
If Len(ThisWorkbook.Path) = 0 Then
Err.Raise vbObjectError + 2300, , _
"Save this Excel workbook before running the macro."
End If
Set wsInspections = ThisWorkbook.Worksheets(INSPECTIONS_SHEET)
Set wsFindings = ThisWorkbook.Worksheets(FINDINGS_SHEET)
If Not (ActiveWorkbook Is ThisWorkbook) Then
Err.Raise vbObjectError + 2301, , _
"Activate the macro workbook and select the inspection row."
End If
If Not (ActiveSheet Is wsInspections) Then
Err.Raise vbObjectError + 2302, , _
"Select a data row on the Inspections sheet."
End If
selectedRow = ActiveCell.Row
If selectedRow < 2 Then
Err.Raise vbObjectError + 2303, , _
"Select an inspection row below the header row."
End If
RequireHeader wsInspections, 1, "InspectionID"
RequireHeader wsInspections, 2, "ClientName"
RequireHeader wsInspections, 3, "PropertyAddress"
RequireHeader wsInspections, 4, "InspectionDateDisplay"
RequireHeader wsInspections, 5, "InspectorName"
RequireHeader wsInspections, 6, "ReportStatus"
RequireHeader wsFindings, 1, "InspectionID"
RequireHeader wsFindings, 2, "SectionName"
RequireHeader wsFindings, 3, "FindingTitle"
RequireHeader wsFindings, 4, "Severity"
RequireHeader wsFindings, 5, "FindingText"
RequireHeader wsFindings, 6, "PhotoPath"
RequireHeader wsFindings, 7, "PhotoCaption"
currentStep = "validating inspection row " & selectedRow
inspectionId = RequiredCellText(wsInspections.Cells(selectedRow, 1), _
"InspectionID", selectedRow)
clientName = RequiredCellText(wsInspections.Cells(selectedRow, 2), _
"ClientName", selectedRow)
propertyAddress = RequiredCellText(wsInspections.Cells(selectedRow, 3), _
"PropertyAddress", selectedRow)
inspectionDateText = RequiredCellText(wsInspections.Cells(selectedRow, 4), _
"InspectionDateDisplay", selectedRow)
inspectorName = RequiredCellText(wsInspections.Cells(selectedRow, 5), _
"InspectorName", selectedRow)
reportStatus = RequiredCellText(wsInspections.Cells(selectedRow, 6), _
"ReportStatus", selectedRow)
If StrComp(reportStatus, REQUIRED_STATUS, vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2304, , _
"Inspection row " & selectedRow & " is not marked READY."
End If
fileStem = SafeWindowsName(inspectionId)
If Len(fileStem) = 0 Then
Err.Raise vbObjectError + 2305, , _
"InspectionID cannot create a safe Windows filename."
End If
Set fso = CreateObject("Scripting.FileSystemObject")
Set matchingRows = New Collection
templatePath = ThisWorkbook.Path & "\" & TEMPLATE_SUBFOLDER & _
"\" & TEMPLATE_FILE
photoRoot = ThisWorkbook.Path & "\" & PHOTO_SUBFOLDER
outputFolder = ThisWorkbook.Path & "\" & OUTPUT_SUBFOLDER
docxPath = outputFolder & "\Inspection_" & fileStem & ".docx"
pdfPath = outputFolder & "\Inspection_" & fileStem & ".pdf"
If Not fso.FileExists(templatePath) Then
Err.Raise vbObjectError + 2306, , _
"Word template not found: " & templatePath
End If
Set lastCell = wsFindings.Range("A:G").Find( _
What:="*", _
After:=wsFindings.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 2307, , _
"No finding rows were found on the Findings sheet."
End If
If lastCell.Row < 2 Then
Err.Raise vbObjectError + 2307, , _
"No finding rows were found on the Findings sheet."
End If
lastFindingRow = lastCell.Row
findingsData = wsFindings.Range("A2:G" & lastFindingRow).Value2
'Preflight every finding and photo before Word starts.
For dataRow = 1 To UBound(findingsData, 1)
If StrComp(OptionalArrayText(findingsData(dataRow, 1), _
"InspectionID", dataRow + 1), inspectionId, _
vbTextCompare) = 0 Then
currentStep = "validating finding row " & (dataRow + 1)
sectionName = RequiredArrayText(findingsData(dataRow, 2), _
"SectionName", dataRow + 1)
findingTitle = RequiredArrayText(findingsData(dataRow, 3), _
"FindingTitle", dataRow + 1)
severity = RequiredArrayText(findingsData(dataRow, 4), _
"Severity", dataRow + 1)
findingText = RequiredArrayText(findingsData(dataRow, 5), _
"FindingText", dataRow + 1)
photoRelativePath = OptionalArrayText(findingsData(dataRow, 6), _
"PhotoPath", dataRow + 1)
photoCaption = OptionalArrayText(findingsData(dataRow, 7), _
"PhotoCaption", dataRow + 1)
If Len(photoRelativePath) > 0 Then
photoFullPath = ResolvePhotoPath(fso, photoRoot, _
photoRelativePath, dataRow + 1)
If Not fso.FileExists(photoFullPath) Then
Err.Raise vbObjectError + 2308, , _
"Photo not found on Findings row " & (dataRow + 1) & _
": " & photoFullPath
End If
If fso.GetFile(photoFullPath).Size = 0 Then
Err.Raise vbObjectError + 2309, , _
"Photo is empty on Findings row " & (dataRow + 1) & _
": " & photoFullPath
End If
ElseIf Len(photoCaption) > 0 Then
Err.Raise vbObjectError + 2310, , _
"PhotoCaption has no PhotoPath on Findings row " & _
dataRow + 1 & "."
End If
matchingRows.Add dataRow
End If
Next dataRow
If matchingRows.Count = 0 Then
Err.Raise vbObjectError + 2311, , _
"No findings match InspectionID " & inspectionId & "."
End If
EnsureFolder fso, outputFolder
If fso.FileExists(docxPath) Then
Err.Raise vbObjectError + 2312, , _
"DOCX output already exists: " & docxPath
End If
If CREATE_PDF And fso.FileExists(pdfPath) Then
Err.Raise vbObjectError + 2313, , _
"PDF output already exists: " & pdfPath
End If
currentStep = "starting Microsoft Word"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone
wdApp.ScreenUpdating = False
currentStep = "opening the detailed inspection template"
Set wdDoc = wdApp.Documents.Open(templatePath, False, True, False)
RequireBookmark wdDoc, "InspectionID"
RequireBookmark wdDoc, "ClientName"
RequireBookmark wdDoc, "PropertyAddress"
RequireBookmark wdDoc, "InspectionDate"
RequireBookmark wdDoc, "InspectorName"
RequireBookmark wdDoc, "FindingsStart"
WriteRequiredBookmark wdDoc, "InspectionID", inspectionId
WriteRequiredBookmark wdDoc, "ClientName", clientName
WriteRequiredBookmark wdDoc, "PropertyAddress", propertyAddress
WriteRequiredBookmark wdDoc, "InspectionDate", inspectionDateText
WriteRequiredBookmark wdDoc, "InspectorName", inspectorName
Set findingsCursor = wdDoc.Bookmarks("FindingsStart").Range
findingsStartPosition = findingsCursor.Start
findingsCursor.Text = ""
findingsCursor.SetRange findingsStartPosition, findingsStartPosition
findingNumber = 0
For Each findingIndex In matchingRows
dataRow = CLng(findingIndex)
findingNumber = findingNumber + 1
currentStep = "writing finding " & findingNumber & _
" of " & matchingRows.Count
sectionName = Trim$(CStr(findingsData(dataRow, 2)))
findingTitle = Trim$(CStr(findingsData(dataRow, 3)))
severity = Trim$(CStr(findingsData(dataRow, 4)))
findingText = Trim$(CStr(findingsData(dataRow, 5)))
photoRelativePath = Trim$(CStr(findingsData(dataRow, 6)))
photoCaption = Trim$(CStr(findingsData(dataRow, 7)))
photoFullPath = ""
If Len(photoRelativePath) > 0 Then
photoFullPath = ResolvePhotoPath(fso, photoRoot, _
photoRelativePath, dataRow + 1)
End If
AppendFindingBlock wdDoc, findingsCursor, sectionName, _
findingTitle, severity, findingText, photoFullPath, _
photoCaption
Next findingIndex
wdDoc.Bookmarks.Add "FindingsStart", _
wdDoc.Range(findingsStartPosition, findingsStartPosition)
If fso.FileExists(docxPath) Then
Err.Raise vbObjectError + 2314, , _
"DOCX output appeared during generation: " & docxPath
End If
If CREATE_PDF And fso.FileExists(pdfPath) Then
Err.Raise vbObjectError + 2315, , _
"PDF output appeared during generation: " & pdfPath
End If
currentStep = "saving the DOCX inspection report"
wdDoc.SaveAs2 docxPath, wdFormatXMLDocument, False, "", False
If CREATE_PDF Then
currentStep = "exporting the PDF inspection report"
wdDoc.ExportAsFixedFormat pdfPath, wdExportFormatPDF, False, _
wdExportOptimizeForPrint
End If
wdDoc.Close wdDoNotSaveChanges
Set wdDoc = Nothing
MsgBox "Inspection " & inspectionId & " generated with " & _
matchingRows.Count & " finding(s)." & vbCrLf & _
"DOCX: " & docxPath & _
IIf(CREATE_PDF, vbCrLf & "PDF: " & pdfPath, ""), _
vbInformation
CleanExit:
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then wdApp.Quit wdDoNotSaveChanges
If screenStateCaptured Then Application.ScreenUpdating = previousScreenUpdating
Set findingsCursor = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
Set matchingRows = Nothing
Set fso = Nothing
On Error GoTo 0
Exit Sub
CleanFail:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then wdApp.Quit wdDoNotSaveChanges
If screenStateCaptured Then Application.ScreenUpdating = previousScreenUpdating
Set findingsCursor = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
Set matchingRows = Nothing
Set fso = Nothing
On Error GoTo 0
MsgBox "Generation stopped while " & currentStep & "." & vbCrLf & _
"Error " & errorNumber & ": " & errorDescription, vbCritical
End Sub
Private Sub RequireHeader(ByVal ws As Worksheet, _
ByVal columnNumber As Long, _
ByVal expectedHeader As String)
Dim headerValue As Variant
headerValue = ws.Cells(1, columnNumber).Value2
If IsError(headerValue) Then
Err.Raise vbObjectError + 2316, , _
"Header cell contains an Excel error on sheet " & _
ws.Name & ", column " & columnNumber & "."
End If
If StrComp(Trim$(CStr(headerValue)), expectedHeader, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 2317, , _
"Expected header '" & expectedHeader & "' on sheet " & _
ws.Name & ", column " & columnNumber & "."
End If
End Sub
Private Function RequiredCellText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
Dim sourceValue As Variant
sourceValue = sourceCell.Value2
If IsError(sourceValue) Then
Err.Raise vbObjectError + 2318, , _
fieldName & " contains an Excel error on row " & excelRow & "."
End If
RequiredCellText = Trim$(CStr(sourceValue))
If Len(RequiredCellText) = 0 Then
Err.Raise vbObjectError + 2319, , _
fieldName & " is required on Excel row " & excelRow & "."
End If
End Function
Private Function RequiredArrayText(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
RequiredArrayText = OptionalArrayText(sourceValue, fieldName, excelRow)
If Len(RequiredArrayText) = 0 Then
Err.Raise vbObjectError + 2320, , _
fieldName & " is required on Findings row " & excelRow & "."
End If
End Function
Private Function OptionalArrayText(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
If IsError(sourceValue) Then
Err.Raise vbObjectError + 2321, , _
fieldName & " contains an Excel error on Findings row " & _
excelRow & "."
End If
OptionalArrayText = Trim$(CStr(sourceValue))
End Function
Private Sub RequireBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2322, , _
"Required Word bookmark is missing: " & 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 originalStyle As Variant
Dim writtenText As String
If Len(Trim$(replacementText)) = 0 Then
Err.Raise vbObjectError + 2323, , _
"Required value is blank for Word bookmark: " & bookmarkName
End If
RequireBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range
originalStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = originalStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2324, , _
"Word bookmark was not restored: " & bookmarkName
End If
writtenText = doc.Bookmarks(bookmarkName).Range.Text
If Len(Trim$(writtenText)) = 0 Then
Err.Raise vbObjectError + 2325, , _
"Required bookmark is empty after replacement: " & bookmarkName
End If
End Sub
Private Sub AppendFindingBlock(ByVal doc As Object, _
ByRef cursor As Object, _
ByVal sectionName As String, _
ByVal findingTitle As String, _
ByVal severity As String, _
ByVal findingText As String, _
ByVal photoPath As String, _
ByVal photoCaption As String)
Dim picture As Object
AppendStyledParagraph doc, cursor, _
sectionName & " — " & findingTitle & " (" & severity & ")", _
wdStyleHeading2
AppendStyledParagraph doc, cursor, findingText, wdStyleNormal
If Len(photoPath) > 0 Then
Set picture = doc.InlineShapes.AddPicture( _
photoPath, False, True, cursor)
picture.LockAspectRatio = msoTrue
If picture.Width > MAX_PHOTO_WIDTH_POINTS Then
picture.Width = MAX_PHOTO_WIDTH_POINTS
End If
If picture.Height > MAX_PHOTO_HEIGHT_POINTS Then
picture.Height = MAX_PHOTO_HEIGHT_POINTS
End If
cursor.SetRange picture.Range.End, picture.Range.End
cursor.InsertAfter vbCr
cursor.Collapse wdCollapseEnd
If Len(photoCaption) > 0 Then
AppendStyledParagraph doc, cursor, photoCaption, wdStyleCaption
End If
Set picture = Nothing
End If
cursor.InsertAfter vbCr
cursor.Collapse wdCollapseEnd
End Sub
Private Sub AppendStyledParagraph(ByVal doc As Object, _
ByRef cursor As Object, _
ByVal paragraphText As String, _
ByVal styleId As Long)
Dim paragraphStart As Long
Dim paragraphRange As Object
paragraphStart = cursor.End
cursor.InsertAfter paragraphText & vbCr
Set paragraphRange = doc.Range(paragraphStart, cursor.End)
paragraphRange.Style = styleId
cursor.Collapse wdCollapseEnd
Set paragraphRange = Nothing
End Sub
Private Function ResolvePhotoPath(ByVal fso As Object, _
ByVal photoRoot As String, _
ByVal relativePath As String, _
ByVal excelRow As Long) As String
Dim normalizedRelative As String
Dim absoluteRoot As String
Dim absoluteCandidate As String
normalizedRelative = Replace$(Trim$(relativePath), "/", "\")
If Len(normalizedRelative) = 0 Then Exit Function
If Left$(normalizedRelative, 1) = "\" Or _
InStr(1, normalizedRelative, ":", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 2326, , _
"PhotoPath must be relative to the Photos folder on Findings row " & _
excelRow & "."
End If
If normalizedRelative = ".." Or _
Left$(normalizedRelative, 3) = "..\" Or _
InStr(1, "\" & normalizedRelative & "\", _
"\..\", vbTextCompare) > 0 Then
Err.Raise vbObjectError + 2327, , _
"PhotoPath cannot leave the Photos folder on Findings row " & _
excelRow & "."
End If
absoluteRoot = fso.GetAbsolutePathName(photoRoot)
absoluteCandidate = fso.GetAbsolutePathName( _
absoluteRoot & "\" & normalizedRelative)
If StrComp(Left$(absoluteCandidate, Len(absoluteRoot) + 1), _
absoluteRoot & "\", vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2328, , _
"Resolved photo is outside the Photos folder on Findings row " & _
excelRow & "."
End If
ResolvePhotoPath = absoluteCandidate
End Function
Private Sub EnsureFolder(ByVal fso As Object, ByVal folderPath As String)
If Not fso.FolderExists(folderPath) Then fso.CreateFolder folderPath
End Sub
Private Function SafeWindowsName(ByVal rawName As String) As String
Dim cleaned As String
Dim invalidCharacters As Variant
Dim invalidCharacter As Variant
Dim characterCode As Long
Dim baseName As String
cleaned = Trim$(rawName)
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", _
"\", "|", "?", "*")
For Each invalidCharacter In invalidCharacters
cleaned = Replace$(cleaned, CStr(invalidCharacter), "_")
Next invalidCharacter
For characterCode = 0 To 31
cleaned = Replace$(cleaned, Chr$(characterCode), "_")
Next characterCode
Do While Len(cleaned) > 0 And _
(Right$(cleaned, 1) = "." Or Right$(cleaned, 1) = " ")
cleaned = Left$(cleaned, Len(cleaned) - 1)
Loop
If Len(cleaned) > 120 Then cleaned = Left$(cleaned, 120)
Do While Len(cleaned) > 0 And _
(Right$(cleaned, 1) = "." Or Right$(cleaned, 1) = " ")
cleaned = Left$(cleaned, Len(cleaned) - 1)
Loop
If Len(cleaned) > 0 Then
baseName = UCase$(Split(cleaned, ".")(0))
Select Case baseName
Case "CON", "PRN", "AUX", "NUL", _
"COM1", "COM2", "COM3", "COM4", "COM5", _
"COM6", "COM7", "COM8", "COM9", _
"LPT1", "LPT2", "LPT3", "LPT4", "LPT5", _
"LPT6", "LPT7", "LPT8", "LPT9"
cleaned = "_" & cleaned
End Select
End If
SafeWindowsName = cleaned
End FunctionThe macro does not decide whether a condition is defective, assign severity, create recommendations, authenticate the inspector, sign the report, or deliver it. It inserts reviewed text and local files only. The relative-path check rejects ordinary drive, UNC, and .. escapes, but it is not a complete defense against filesystem reparse points or other redirection controlled outside the workbook.
There is no automatic rollback. If DOCX save succeeds and PDF export fails, the DOCX remains. Review the output folder and resolve the partial file deliberately before rerunning.
The image insertion uses Word's documented InlineShapes.AddPicture method. The template and output checks use Bookmarks.Exists, SaveAs2, and ExportAsFixedFormat. Review the code statically and test it in Word Desktop with synthetic records and disposable output before production use.
Review the report as inspection evidence
A file can be generated successfully and still contain the wrong property, incomplete findings, a misleading photo, or a layout problem. Review the evidence and the document together.
Case identity
Match InspectionID, client, property address, date, inspector, filename, and intended recipient.
Finding completeness
Confirm that every approved finding for the selected ID appears once and unrelated IDs do not appear.
Photo correspondence
Verify that every image shows the stated condition and that its caption, orientation, and sequence are correct.
Narrative accuracy
Review section, title, severity, description, limitations, and recommendations under the inspection process.
DOCX layout
Inspect styles, page breaks, image size, captions, headers, footers, comments, and tracked changes.
PDF comparison
Compare visible text, image order, page count, fonts, links, and pagination with the reviewed DOCX.
For unavailable network images or shared folders, use the network photo folder troubleshooting guide.
Contractor logic: one inspection case, several documents
Review the property, inspection details, findings, and photo references as one case. Reuse that case data in the different documents required for the same inspection.
Load or enter the property-level values once, then review the fields that should be reused across the document set.
Generate the main inspection report, repair summary, client cover document, or another case-specific output from its own Word template.
The VBA example generates the main detailed report with a variable number of finding-and-photo blocks. Additional case documents require their own templates and explicitly tested mappings. Article №14 will cover Factory batch logic separately.
Use DocxForge Pro Contractor for the case workflow
DocxForge Pro is a local Windows document-production suite. Microsoft Word Desktop for Windows is required. Contractor handles one structured case and the different Word templates selected for that case. Inspection judgments, evidence review, arbitrary repeated custom layouts, signatures, and release approval remain outside the product.
Contractor: review one property case and its documents
Run a six-case pilot
Use synthetic records and image copies. Confirm both successful output and deliberate failure behavior before using inspection evidence.
1. Three valid findings
Generate text-only, landscape-photo, and portrait-photo findings for one synthetic InspectionID.
2. Unrelated finding
Add a row for another ID and confirm it never appears in the selected inspection report.
3. Missing required text
Blank FindingText and confirm preflight stops before Word starts.
4. Missing photo
Reference a nonexistent relative file and confirm no DOCX or PDF is saved.
5. Path escape
Try a drive path, UNC path, and .. segment. Confirm each is rejected.
6. Existing output
Rerun a successful ID and confirm the existing DOCX and PDF remain unchanged.
Frequently asked questions
Short answers about detailed home inspection reports with findings and photos.
Why use separate Inspections and Findings sheets?
One inspection can contain any number of findings. A parent-and-detail structure avoids a growing set of Finding1, Finding2, Photo1, and Photo2 columns and makes every finding easier to validate and relate to its InspectionID.
Can one finding omit a photo?
Yes. PhotoPath and PhotoCaption are optional, but a caption without a photo path is rejected. Required text fields still have to be present for every related finding.
Does the macro resize inspection photos?
It preserves the aspect ratio and shrinks images that exceed the configured maximum width or height. It does not enlarge small images, crop them, rotate them, improve resolution, or verify what they show.
Does ReportStatus = READY prove that the report is approved?
No. READY is only a generation gate. The macro does not authenticate the inspector, prove review authority, capture a signature, or create a tamper-evident release record.
How is this different from the row-based home inspection workflow?
This article builds one detailed report from one property row plus many related finding rows and photos. The row-based workflow is for repeated standardized reports where each Excel row already contains the complete mapped values for one output.
Why does this article use Contractor logic?
One inspection is treated as one reviewed case that may need several different documents from different Word templates. Article №14 uses Factory logic for many complete Excel rows generating standardized reports from one template.