Detailed Multi-Photo Inspection Report

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.

One Contractor case
Multiple findings
Separate Word templates
DOCX + optional PDF
This article follows Contractor logic: one inspection case can produce several different documents. Article №14 follows Factory logic: many Excel rows generate from one standard template.

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.

Keep article №13 separate from article №14

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.

Required workbook setup

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.

Required folders

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.

Expected output

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 Function
Limits of the example

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

One reviewed case record

Load or enter the property-level values once, then review the fields that should be reused across the document set.

Different templates for different outputs

Generate the main inspection report, repair summary, client cover document, or another case-specific output from its own Word template.

Scope of the VBA fallback

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

DocxForge Pro Contractor case data screen for reviewing one property inspection case
Review structured data for one property case.
DocxForge Pro Contractor document selection for different inspection outputs
Choose the different documents required for that case.
Test with synthetic property data and disposable photos 7 days free on the 3-month plan, then $38 every 3 months • 14-day refund after purchase
Contractor case workflow Local processing Different Word templates DOCX + optional PDF
Start Free 7-Day Trial

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.

Prove the detailed report with synthetic findings first DocxForge Pro 7 days free on the 3-month plan, then $38 every 3 months • 14-day refund after purchase
One reviewed case Different document types Separate Word templates DOCX + optional PDF
Start Free 7-Day Trial