How to Create Photo-Based Property Condition Reports
Turn one reviewed property record and a sequenced photo log into a structured evidence report with embedded images, stable identifiers, and separate DOCX and PDF outputs.
Quick answer: model the property once and the photographs as a child log
Keep stable property facts on one row, then store every observation photograph on a separate PhotoLog row with its own ID, area, caption, sequence, and release status. Validate the full case before Word opens, embed the checked files in sequence, and approve the resulting DOCX and PDF together.
Selected Properties row → matching INCLUDE rows in PhotoLog → verified files in Photos → one controlled Word template → staged DOCX/PDF → human review and release.
Define the report’s purpose before collecting photographs
Move-in or move-out record
Document visible condition at a named handover point, using stable room names and captions rather than an unstructured camera roll.
Maintenance evidence
Record a reported issue, the affected area, and the visible state before and after authorized work.
Damage documentation
Keep identifiers, dates, captions, and original source files under a controlled evidence process.
Due-diligence package
Prepare a factual photo appendix for reviewers while keeping conclusions and approvals outside the automation.
Not a professional inspection
The document can present facts and observations, but the macro does not assess structure, safety, code compliance, or causation.
Not a mass row report
This article builds one property case with many photo rows. A standardized one-row-per-report batch is a different workflow.
Create the parent property record
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). On a sheet named Properties, use these exact headers in row 1:
A–D: identity and recipient
A — PropertyID; B — PropertyAddress; C — ReportDateDisplay; D — PreparedFor.
E–H: ownership and release
E — PreparedBy; F — Purpose; G — OutputName; H — ReportStatus.
Store document-ready text
Store identifiers, dates, names, and the output stem as text. Enter 2026-11-19, 19 November 2026, or your approved wording explicitly rather than relying on Excel display formatting.
Select one READY property
The example runs from the active Properties row and requires ReportStatus to equal READY exactly. It does not silently trim or change case.
Use one auditable row per photograph
On a sheet named PhotoLog, use exact headers and keep the relationship to the selected property explicit.
A–D: ownership and order
A — PropertyID; B — PhotoID; C — Sequence; D — Area. Sequence must be a unique positive whole number within the selected property.
E–H: evidence and inclusion
E — Caption; F — PhotoFile; G — CapturedAtDisplay; H — IncludeStatus. Use exactly INCLUDE or EXCLUDE.
Use filename-only references
Put PNG, JPG, or JPEG files in a Photos folder beside the workbook. PhotoFile must be a filename only, not an absolute path or wildcard.
Keep evidence identifiers unique
Each PhotoID and Sequence must be unique for the selected property. Included rows also require unique PhotoFile values so accidental duplication is visible before generation.
Build a Word template with a controlled insertion point
Save PropertyConditionReport.docx under Templates beside the workbook. Use Word bookmarks for the parent data and one bookmark for the repeated evidence section.
Required text bookmarks
PropertyID, PropertyAddress, ReportDateDisplay, PreparedFor, PreparedBy, and Purpose.
Required evidence bookmark
Place PhotoEvidenceStart at the location where ordered photo blocks should begin. Do not overlap it with a field, content control, or existing image.
Design for variable length
Keep the evidence section in the main story, allow headings and inline images to flow, and test page breaks with long captions and mixed image proportions.
Keep the template clean
The macro creates a new document from the template. It does not alter the source DOCX and refuses existing final filenames during the ordinary single-writer run.
Generate the report through six controlled stages
1. Select the property
Activate one READY row on Properties. The macro reads the exact text contract and validates the output filename.
2. Preflight the photo log
Every matching row is checked for unique IDs, unique sequence, exact status, and required evidence values.
3. Verify every included file
The code checks filename safety, allowed extension, existence, and nonzero size before Word starts.
4. Sort by sequence
Included rows are ordered numerically so the report follows the reviewed evidence plan rather than worksheet order.
5. Embed the evidence
Word receives each file as an inline embedded image, fitted within maximum dimensions without enlarging small images.
6. Stage and commit
DOCX and PDF are created in a run-specific staging folder, checked, then moved to GeneratedPropertyReports.
Free Excel VBA helper: build one sequenced photo report
Add a standard module to the saved .xlsm workbook and run GeneratePropertyConditionReport while the intended Properties row is active. The example does not delete final or staged evidence automatically after a failure.
Exact data contract
Headers, statuses, IDs, filenames, and display text are checked without silent trimming or case normalization.
Two-pass lifecycle
All matching evidence is validated first. The checked rows are read again during generation so a changed row stops the run.
Safe bookmark replacement
Text bookmarks reject nested fields, content controls, and inline images before replacement, then restore and verify the bookmark.
Embedded images
Each picture is inserted with linking disabled and saving enabled, then given alternative text based on PhotoID and Area.
No ordinary overwrite
Targets are checked before Word starts and immediately before the staged files are committed.
Failure evidence retained
If generation fails, existing final files are not deleted and any run-specific staging folder remains available for inspection.
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 = 432#
Private Const MAX_PHOTO_HEIGHT_POINTS As Double = 288#
Public Sub GeneratePropertyConditionReport()
Const PROPERTIES_SHEET As String = "Properties"
Const PHOTO_LOG_SHEET As String = "PhotoLog"
Const TEMPLATE_FOLDER_NAME As String = "Templates"
Const TEMPLATE_FILE_NAME As String = "PropertyConditionReport.docx"
Const PHOTO_FOLDER_NAME As String = "Photos"
Const OUTPUT_FOLDER_NAME As String = "GeneratedPropertyReports"
Const READY_STATUS As String = "READY"
Const INCLUDE_STATUS As String = "INCLUDE"
Const EXCLUDE_STATUS As String = "EXCLUDE"
Dim propertiesWs As Worksheet
Dim photoLogWs As Worksheet
Dim selectedRow As Long
Dim propertyId As String
Dim currentPropertyId As String
Dim propertyAddress As String
Dim reportDateText As String
Dim preparedFor As String
Dim preparedBy As String
Dim purposeText As String
Dim outputName As String
Dim reportStatus As String
Dim lastCell As Range
Dim lastPhotoRow As Long
Dim photoData As Variant
Dim photoRows() As Long
Dim photoSequences() As Long
Dim includedCount As Long
Dim dataRow As Long
Dim excelRow As Long
Dim photoId As String
Dim areaName As String
Dim captionText As String
Dim photoFile As String
Dim capturedAtText As String
Dim includeStatus As String
Dim photoSequence As Long
Dim photoPath As String
Dim templatePath As String
Dim photoFolder As String
Dim outputFolder As String
Dim runFolder As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim photoIds As Object
Dim sequenceOwners As Object
Dim photoFiles As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim evidenceCursor As Object
Dim evidenceStart As Long
Dim previousScreenUpdating As Boolean
Dim screenStateCaptured As Boolean
Dim currentStep As String
Dim errorNumber As Long
Dim errorDescription As String
On Error GoTo CleanFail
previousScreenUpdating = Application.ScreenUpdating
screenStateCaptured = True
Application.ScreenUpdating = False
currentStep = "checking the saved workbook"
If Len(ThisWorkbook.Path) = 0 Then
Err.Raise vbObjectError + 4400, , _
"Save the Excel workbook as an .xlsm file before running the macro."
End If
Set propertiesWs = ThisWorkbook.Worksheets(PROPERTIES_SHEET)
Set photoLogWs = ThisWorkbook.Worksheets(PHOTO_LOG_SHEET)
If Not (ActiveWorkbook Is ThisWorkbook) Then
Err.Raise vbObjectError + 4401, , _
"Activate this workbook before selecting a property row."
End If
If Not (ActiveSheet Is propertiesWs) Then
Err.Raise vbObjectError + 4402, , _
"Select one property row on the Properties sheet."
End If
selectedRow = ActiveCell.Row
If selectedRow < 2 Then
Err.Raise vbObjectError + 4403, , _
"Select a property row below the header row."
End If
RequireHeader propertiesWs, 1, "PropertyID"
RequireHeader propertiesWs, 2, "PropertyAddress"
RequireHeader propertiesWs, 3, "ReportDateDisplay"
RequireHeader propertiesWs, 4, "PreparedFor"
RequireHeader propertiesWs, 5, "PreparedBy"
RequireHeader propertiesWs, 6, "Purpose"
RequireHeader propertiesWs, 7, "OutputName"
RequireHeader propertiesWs, 8, "ReportStatus"
RequireHeader photoLogWs, 1, "PropertyID"
RequireHeader photoLogWs, 2, "PhotoID"
RequireHeader photoLogWs, 3, "Sequence"
RequireHeader photoLogWs, 4, "Area"
RequireHeader photoLogWs, 5, "Caption"
RequireHeader photoLogWs, 6, "PhotoFile"
RequireHeader photoLogWs, 7, "CapturedAtDisplay"
RequireHeader photoLogWs, 8, "IncludeStatus"
currentStep = "validating Properties row " & selectedRow
propertyId = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 1), _
"PropertyID", selectedRow)
propertyAddress = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 2), _
"PropertyAddress", selectedRow)
reportDateText = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 3), _
"ReportDateDisplay", selectedRow)
preparedFor = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 4), _
"PreparedFor", selectedRow)
preparedBy = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 5), _
"PreparedBy", selectedRow)
purposeText = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 6), _
"Purpose", selectedRow)
outputName = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 7), _
"OutputName", selectedRow)
reportStatus = ReadRequiredCellText(propertiesWs.Cells(selectedRow, 8), _
"ReportStatus", selectedRow)
ValidateIdentifier propertyId, "PropertyID", selectedRow
ValidateFileStem outputName, "OutputName", selectedRow
If StrComp(reportStatus, READY_STATUS, vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4404, , _
"ReportStatus must be exactly READY on Properties row " & _
selectedRow & "."
End If
RequireUniquePropertyId propertiesWs, propertyId, selectedRow
templatePath = JoinPath(JoinPath(ThisWorkbook.Path, _
TEMPLATE_FOLDER_NAME), TEMPLATE_FILE_NAME)
photoFolder = JoinPath(ThisWorkbook.Path, PHOTO_FOLDER_NAME)
outputFolder = JoinPath(ThisWorkbook.Path, OUTPUT_FOLDER_NAME)
finalDocxPath = JoinPath(outputFolder, outputName & ".docx")
finalPdfPath = JoinPath(outputFolder, outputName & ".pdf")
RequireExistingFile templatePath, "Word template"
RequireExistingFolder photoFolder, "Photos folder"
Set lastCell = photoLogWs.Range("A:H").Find( _
What:="*", _
After:=photoLogWs.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 4405, , _
"No rows were found on the PhotoLog sheet."
End If
If lastCell.Row < 2 Then
Err.Raise vbObjectError + 4405, , _
"No rows were found on the PhotoLog sheet."
End If
lastPhotoRow = lastCell.Row
photoData = photoLogWs.Range("A2:H" & lastPhotoRow).Value2
Set photoIds = CreateObject("Scripting.Dictionary")
photoIds.CompareMode = vbTextCompare
Set sequenceOwners = CreateObject("Scripting.Dictionary")
sequenceOwners.CompareMode = vbTextCompare
Set photoFiles = CreateObject("Scripting.Dictionary")
photoFiles.CompareMode = vbTextCompare
'Pass 1 validates every row belonging to the selected property.
For dataRow = 1 To UBound(photoData, 1)
excelRow = dataRow + 1
If ArrayTextMatches(photoData(dataRow, 1), propertyId, _
"PropertyID", excelRow) Then
currentStep = "validating PhotoLog row " & excelRow
photoId = ReadRequiredArrayText(photoData(dataRow, 2), _
"PhotoID", excelRow)
photoSequence = ReadPositiveWholeNumber(photoData(dataRow, 3), _
"Sequence", excelRow)
includeStatus = ReadRequiredArrayText(photoData(dataRow, 8), _
"IncludeStatus", excelRow)
ValidateIdentifier photoId, "PhotoID", excelRow
If photoIds.Exists(photoId) Then
Err.Raise vbObjectError + 4406, , _
"Duplicate PhotoID on PhotoLog rows " & _
photoIds(photoId) & " and " & excelRow & ": " & photoId
End If
photoIds.Add photoId, excelRow
If sequenceOwners.Exists(CStr(photoSequence)) Then
Err.Raise vbObjectError + 4407, , _
"Duplicate Sequence on PhotoLog rows " & _
sequenceOwners(CStr(photoSequence)) & " and " & _
excelRow & ": " & photoSequence
End If
sequenceOwners.Add CStr(photoSequence), excelRow
If StrComp(includeStatus, INCLUDE_STATUS, vbBinaryCompare) <> 0 And _
StrComp(includeStatus, EXCLUDE_STATUS, vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4408, , _
"IncludeStatus must be exactly INCLUDE or EXCLUDE on " & _
"PhotoLog row " & excelRow & "."
End If
If StrComp(includeStatus, INCLUDE_STATUS, vbBinaryCompare) = 0 Then
areaName = ReadRequiredArrayText(photoData(dataRow, 4), _
"Area", excelRow)
captionText = ReadRequiredArrayText(photoData(dataRow, 5), _
"Caption", excelRow)
photoFile = ReadRequiredArrayText(photoData(dataRow, 6), _
"PhotoFile", excelRow)
capturedAtText = ReadOptionalArrayText(photoData(dataRow, 7), _
"CapturedAtDisplay", excelRow)
ValidatePhotoFileName photoFile, excelRow
If photoFiles.Exists(photoFile) Then
Err.Raise vbObjectError + 4409, , _
"Duplicate included PhotoFile on PhotoLog rows " & _
photoFiles(photoFile) & " and " & excelRow & ": " & _
photoFile
End If
photoFiles.Add photoFile, excelRow
photoPath = JoinPath(photoFolder, photoFile)
RequireNonemptyPhoto photoPath, excelRow
includedCount = includedCount + 1
ReDim Preserve photoRows(1 To includedCount)
ReDim Preserve photoSequences(1 To includedCount)
photoRows(includedCount) = dataRow
photoSequences(includedCount) = photoSequence
End If
End If
Next dataRow
If includedCount = 0 Then
Err.Raise vbObjectError + 4410, , _
"No INCLUDE photo rows match PropertyID " & propertyId & "."
End If
SortPhotoRows photoRows, photoSequences, includedCount
currentStep = "checking output targets"
EnsureFolder outputFolder
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
runFolder = CreateRunFolder(outputFolder)
tempDocxPath = JoinPath(runFolder, outputName & ".docx")
tempPdfPath = JoinPath(runFolder, outputName & ".pdf")
currentStep = "starting Microsoft Word"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone
wdApp.ScreenUpdating = False
currentStep = "creating a report from the Word template"
Set wdDoc = wdApp.Documents.Add(templatePath, False, 0, False)
RequireBookmark wdDoc, "PropertyID"
RequireBookmark wdDoc, "PropertyAddress"
RequireBookmark wdDoc, "ReportDateDisplay"
RequireBookmark wdDoc, "PreparedFor"
RequireBookmark wdDoc, "PreparedBy"
RequireBookmark wdDoc, "Purpose"
RequireBookmark wdDoc, "PhotoEvidenceStart"
WriteRequiredBookmark wdDoc, "PropertyID", propertyId
WriteRequiredBookmark wdDoc, "PropertyAddress", propertyAddress
WriteRequiredBookmark wdDoc, "ReportDateDisplay", reportDateText
WriteRequiredBookmark wdDoc, "PreparedFor", preparedFor
WriteRequiredBookmark wdDoc, "PreparedBy", preparedBy
WriteRequiredBookmark wdDoc, "Purpose", purposeText
Set evidenceCursor = wdDoc.Bookmarks("PhotoEvidenceStart").Range
If evidenceCursor.Fields.Count > 0 Or _
evidenceCursor.ContentControls.Count > 0 Or _
evidenceCursor.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 4411, , _
"PhotoEvidenceStart overlaps a field, content control, or image."
End If
evidenceStart = evidenceCursor.Start
evidenceCursor.Text = ""
evidenceCursor.SetRange evidenceStart, evidenceStart
'Pass 2 writes only the checked INCLUDE rows in Sequence order.
For dataRow = 1 To includedCount
excelRow = photoRows(dataRow) + 1
currentStep = "adding photo " & dataRow & " of " & includedCount & _
" from PhotoLog row " & excelRow
RequirePhotoLogRowUnchanged photoLogWs, excelRow, photoData, _
photoRows(dataRow)
currentPropertyId = ReadRequiredCellText( _
photoLogWs.Cells(excelRow, 1), "PropertyID", excelRow)
If StrComp(currentPropertyId, propertyId, vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4412, , _
"PropertyID changed on PhotoLog row " & excelRow & "."
End If
photoId = ReadRequiredCellText( _
photoLogWs.Cells(excelRow, 2), "PhotoID", excelRow)
photoSequence = ReadPositiveWholeNumber( _
photoLogWs.Cells(excelRow, 3).Value2, "Sequence", excelRow)
areaName = ReadRequiredCellText( _
photoLogWs.Cells(excelRow, 4), "Area", excelRow)
captionText = ReadRequiredCellText( _
photoLogWs.Cells(excelRow, 5), "Caption", excelRow)
photoFile = ReadRequiredCellText( _
photoLogWs.Cells(excelRow, 6), "PhotoFile", excelRow)
capturedAtText = ReadOptionalCellText( _
photoLogWs.Cells(excelRow, 7), "CapturedAtDisplay", excelRow)
includeStatus = ReadRequiredCellText( _
photoLogWs.Cells(excelRow, 8), "IncludeStatus", excelRow)
If photoSequence <> photoSequences(dataRow) Or _
StrComp(includeStatus, INCLUDE_STATUS, vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4412, , _
"PhotoLog row " & excelRow & _
" changed after preflight. Run the macro again."
End If
photoPath = JoinPath(photoFolder, photoFile)
RequireNonemptyPhoto photoPath, excelRow
AppendPhotoEvidenceBlock wdDoc, evidenceCursor, photoSequence, _
photoId, areaName, captionText, capturedAtText, photoPath
Next dataRow
wdDoc.Bookmarks.Add "PhotoEvidenceStart", _
wdDoc.Range(evidenceStart, evidenceStart)
currentStep = "saving staged DOCX and PDF files"
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
wdDoc.SaveAs2 tempDocxPath, wdFormatXMLDocument, False, "", False
wdDoc.ExportAsFixedFormat tempPdfPath, wdExportFormatPDF, False, _
wdExportOptimizeForPrint
wdDoc.Close wdDoNotSaveChanges
Set wdDoc = Nothing
ValidateCreatedFile tempDocxPath, "staged DOCX"
ValidateCreatedFile tempPdfPath, "staged PDF"
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
currentStep = "committing the final DOCX"
Name tempDocxPath As finalDocxPath
currentStep = "committing the final PDF"
Name tempPdfPath As finalPdfPath
RemoveEmptyRunFolder runFolder
If Not wdApp Is Nothing Then wdApp.NormalTemplate.Saved = True
wdApp.Quit wdDoNotSaveChanges
Set wdApp = Nothing
MsgBox "Property condition report created with " & _
includedCount & " photo(s)." & vbCrLf & _
"DOCX: " & finalDocxPath & vbCrLf & _
"PDF: " & finalPdfPath, vbInformation
CleanExit:
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit wdDoNotSaveChanges
End If
If screenStateCaptured Then Application.ScreenUpdating = previousScreenUpdating
Set evidenceCursor = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
Set photoIds = Nothing
Set sequenceOwners = Nothing
Set photoFiles = 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.NormalTemplate.Saved = True
wdApp.Quit wdDoNotSaveChanges
End If
If screenStateCaptured Then Application.ScreenUpdating = previousScreenUpdating
Set evidenceCursor = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
Set photoIds = Nothing
Set sequenceOwners = Nothing
Set photoFiles = Nothing
On Error GoTo 0
MsgBox "Generation stopped while " & currentStep & "." & vbCrLf & _
"Error " & errorNumber & ": " & errorDescription & vbCrLf & vbCrLf & _
"Existing final files were not deleted. Inspect any run-specific " & _
"staging folder before retrying.", vbCritical
End Sub
Private Sub RequireUniquePropertyId(ByVal propertiesWs As Worksheet, _
ByVal selectedPropertyId As String, _
ByVal selectedRow As Long)
Dim lastCell As Range
Dim rowNumber As Long
Dim matchCount As Long
Dim firstMatchRow As Long
Dim candidateId As String
Dim sourceValue As Variant
Set lastCell = propertiesWs.Columns(1).Find( _
What:="*", _
After:=propertiesWs.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 4448, , _
"No PropertyID values were found on the Properties sheet."
End If
For rowNumber = 2 To lastCell.Row
sourceValue = propertiesWs.Cells(rowNumber, 1).Value2
If IsError(sourceValue) Then
Err.Raise vbObjectError + 4449, , _
"PropertyID contains an Excel error on Properties row " & _
rowNumber & "."
End If
If Not IsEmpty(sourceValue) Then
If Len(CStr(sourceValue)) > 0 Then
candidateId = ReadExactTextValue(sourceValue, _
"PropertyID", rowNumber, True)
If StrComp(candidateId, selectedPropertyId, _
vbTextCompare) = 0 Then
matchCount = matchCount + 1
If firstMatchRow = 0 Then firstMatchRow = rowNumber
If matchCount > 1 Then
Err.Raise vbObjectError + 4450, , _
"PropertyID '" & selectedPropertyId & _
"' is duplicated on Properties rows " & _
firstMatchRow & " and " & rowNumber & _
". Use a unique case identifier for each report."
End If
End If
End If
End If
Next rowNumber
If matchCount <> 1 Or firstMatchRow <> selectedRow Then
Err.Raise vbObjectError + 4451, , _
"The selected PropertyID must occur exactly once on Properties."
End If
End Sub
Private Sub RequirePhotoLogRowUnchanged(ByVal photoLogWs As Worksheet, _
ByVal excelRow As Long, _
ByRef originalData As Variant, _
ByVal originalDataRow As Long)
Dim columnNumber As Long
Dim originalValue As Variant
Dim currentValue As Variant
For columnNumber = 1 To 8
originalValue = originalData(originalDataRow, columnNumber)
currentValue = photoLogWs.Cells(excelRow, columnNumber).Value2
If Not VariantValuesExactlyEqual(originalValue, currentValue) Then
Err.Raise vbObjectError + 4452, , _
"PhotoLog row " & excelRow & ", column " & _
columnNumber & " changed after preflight. " & _
"Run the macro again."
End If
Next columnNumber
End Sub
Private Function VariantValuesExactlyEqual(ByVal originalValue As Variant, _
ByVal currentValue As Variant) As Boolean
If VarType(originalValue) <> VarType(currentValue) Then Exit Function
If IsError(originalValue) Or IsError(currentValue) Then Exit Function
Select Case VarType(originalValue)
Case vbEmpty, vbNull
VariantValuesExactlyEqual = True
Case vbString
VariantValuesExactlyEqual = _
(StrComp(CStr(originalValue), CStr(currentValue), _
vbBinaryCompare) = 0)
Case Else
VariantValuesExactlyEqual = (originalValue = currentValue)
End Select
End Function
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 + 4413, , _
"Header contains an Excel error on sheet " & ws.Name & _
", column " & columnNumber & "."
End If
If VarType(headerValue) <> vbString Or _
StrComp(CStr(headerValue), expectedHeader, vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4414, , _
"Expected exact header '" & expectedHeader & "' on sheet " & _
ws.Name & ", column " & columnNumber & "."
End If
End Sub
Private Function ReadRequiredCellText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadRequiredCellText = ReadExactTextValue(sourceCell.Value2, _
fieldName, excelRow, True)
End Function
Private Function ReadOptionalCellText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadOptionalCellText = ReadExactTextValue(sourceCell.Value2, _
fieldName, excelRow, False)
End Function
Private Function ReadRequiredArrayText(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadRequiredArrayText = ReadExactTextValue(sourceValue, fieldName, _
excelRow, True)
End Function
Private Function ReadOptionalArrayText(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadOptionalArrayText = ReadExactTextValue(sourceValue, fieldName, _
excelRow, False)
End Function
Private Function ReadExactTextValue(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long, _
ByVal required As Boolean) As String
Dim valueText As String
If IsError(sourceValue) Then
Err.Raise vbObjectError + 4415, , _
fieldName & " contains an Excel error on row " & excelRow & "."
End If
If IsEmpty(sourceValue) Then
If required Then
Err.Raise vbObjectError + 4416, , _
fieldName & " is required on row " & excelRow & "."
End If
Exit Function
End If
If Len(CStr(sourceValue)) = 0 Then
If required Then
Err.Raise vbObjectError + 4416, , _
fieldName & " is required on row " & excelRow & "."
End If
Exit Function
End If
If VarType(sourceValue) <> vbString Then
Err.Raise vbObjectError + 4417, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
valueText = CStr(sourceValue)
If valueText <> Trim$(valueText) Then
Err.Raise vbObjectError + 4418, , _
fieldName & " has leading or trailing whitespace on row " & _
excelRow & "."
End If
If ContainsControlCharacter(valueText) Then
Err.Raise vbObjectError + 4419, , _
fieldName & " contains a control character on row " & _
excelRow & "."
End If
ReadExactTextValue = valueText
End Function
Private Function ArrayTextMatches(ByVal sourceValue As Variant, _
ByVal expectedValue As String, _
ByVal fieldName As String, _
ByVal excelRow As Long) As Boolean
If IsError(sourceValue) Then
Err.Raise vbObjectError + 4420, , _
fieldName & " contains an Excel error on row " & excelRow & "."
End If
If IsEmpty(sourceValue) Then Exit Function
If Len(CStr(sourceValue)) = 0 Then Exit Function
If VarType(sourceValue) <> vbString Then
Err.Raise vbObjectError + 4421, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
If CStr(sourceValue) <> Trim$(CStr(sourceValue)) Then
Err.Raise vbObjectError + 4422, , _
fieldName & " has leading or trailing whitespace on row " & _
excelRow & "."
End If
ArrayTextMatches = _
(StrComp(CStr(sourceValue), expectedValue, vbBinaryCompare) = 0)
End Function
Private Function ReadPositiveWholeNumber(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As Long
Dim numericValue As Double
If IsError(sourceValue) Then
Err.Raise vbObjectError + 4423, , _
fieldName & " must be a positive whole number on row " & _
excelRow & "."
End If
If IsEmpty(sourceValue) Then
Err.Raise vbObjectError + 4423, , _
fieldName & " must be a positive whole number on row " & _
excelRow & "."
End If
If VarType(sourceValue) = vbString Or _
VarType(sourceValue) = vbBoolean Then
Err.Raise vbObjectError + 4423, , _
fieldName & " must be stored as a number on row " & _
excelRow & "."
End If
If Not IsNumeric(sourceValue) Then
Err.Raise vbObjectError + 4423, , _
fieldName & " must be a positive whole number on row " & _
excelRow & "."
End If
numericValue = CDbl(sourceValue)
If numericValue < 1 Or numericValue > 2147483647# Or _
numericValue <> Fix(numericValue) Then
Err.Raise vbObjectError + 4424, , _
fieldName & " must be a positive whole number on row " & _
excelRow & "."
End If
ReadPositiveWholeNumber = CLng(numericValue)
End Function
Private Sub ValidateIdentifier(ByVal identifierValue As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
If InStr(1, identifierValue, "<", vbBinaryCompare) > 0 Or _
InStr(1, identifierValue, ">", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4425, , _
fieldName & " contains an unsupported bracket on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateFileStem(ByVal fileStem As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
Dim invalidCharacters As Variant
Dim invalidCharacter As Variant
Dim baseName As String
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", _
"\", "|", "?", "*")
For Each invalidCharacter In invalidCharacters
If InStr(1, fileStem, CStr(invalidCharacter), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4426, , _
fieldName & " contains a forbidden Windows character on row " & _
excelRow & "."
End If
Next invalidCharacter
If ContainsControlCharacter(fileStem) Then
Err.Raise vbObjectError + 4427, , _
fieldName & " contains a control character on row " & _
excelRow & "."
End If
If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
Err.Raise vbObjectError + 4428, , _
fieldName & " cannot end with a period or space on row " & _
excelRow & "."
End If
If Len(fileStem) > 120 Then
Err.Raise vbObjectError + 4429, , _
fieldName & " exceeds 120 characters 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 + 4430, , _
fieldName & " uses a reserved Windows name on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidatePhotoFileName(ByVal photoFile As String, _
ByVal excelRow As Long)
Dim extensionPosition As Long
Dim extensionText As String
ValidateFileStem photoFile, "PhotoFile", excelRow
If InStr(1, photoFile, "\", vbBinaryCompare) > 0 Or _
InStr(1, photoFile, "/", vbBinaryCompare) > 0 Or _
InStr(1, photoFile, ":", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4431, , _
"PhotoFile must be a filename only on PhotoLog row " & _
excelRow & "."
End If
extensionPosition = InStrRev(photoFile, ".")
If extensionPosition <= 1 Then
Err.Raise vbObjectError + 4432, , _
"PhotoFile needs a PNG, JPG, or JPEG extension on row " & _
excelRow & "."
End If
extensionText = UCase$(Mid$(photoFile, extensionPosition + 1))
If extensionText <> "PNG" And extensionText <> "JPG" And _
extensionText <> "JPEG" Then
Err.Raise vbObjectError + 4433, , _
"PhotoFile must use PNG, JPG, or JPEG on row " & _
excelRow & "."
End If
End Sub
Private Sub RequireNonemptyPhoto(ByVal photoPath As String, _
ByVal excelRow As Long)
RequireExistingFile photoPath, "PhotoLog row " & excelRow & " photo"
If FileLen(photoPath) <= 0 Then
Err.Raise vbObjectError + 4434, , _
"Photo is empty on PhotoLog row " & excelRow & ": " & photoPath
End If
End Sub
Private Sub SortPhotoRows(ByRef photoRows() As Long, _
ByRef sequences() As Long, _
ByVal itemCount As Long)
Dim outerIndex As Long
Dim innerIndex As Long
Dim rowValue As Long
Dim sequenceValue As Long
For outerIndex = 2 To itemCount
rowValue = photoRows(outerIndex)
sequenceValue = sequences(outerIndex)
innerIndex = outerIndex - 1
Do While innerIndex >= 1
If sequences(innerIndex) <= sequenceValue Then Exit Do
photoRows(innerIndex + 1) = photoRows(innerIndex)
sequences(innerIndex + 1) = sequences(innerIndex)
innerIndex = innerIndex - 1
Loop
photoRows(innerIndex + 1) = rowValue
sequences(innerIndex + 1) = sequenceValue
Next outerIndex
End Sub
Private Sub RequireBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 4435, , _
"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
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 + 4436, , _
"Bookmark overlaps a field, content control, or image: " & _
bookmarkName
End If
originalStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = originalStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 4437, , _
"Word bookmark was not restored: " & bookmarkName
End If
If StrComp(doc.Bookmarks(bookmarkName).Range.Text, replacementText, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4438, , _
"Bookmark value differs after replacement: " & bookmarkName
End If
End Sub
Private Sub AppendPhotoEvidenceBlock(ByVal doc As Object, _
ByRef cursor As Object, _
ByVal sequenceNumber As Long, _
ByVal photoId As String, _
ByVal areaName As String, _
ByVal captionText As String, _
ByVal capturedAtText As String, _
ByVal photoPath As String)
Dim picture As Object
Dim metadataText As String
AppendStyledParagraph doc, cursor, _
CStr(sequenceNumber) & ". " & areaName & " — " & photoId, _
wdStyleHeading2
AppendStyledParagraph doc, cursor, captionText, wdStyleNormal
If Len(capturedAtText) > 0 Then
metadataText = "Captured: " & capturedAtText
AppendStyledParagraph doc, cursor, metadataText, wdStyleCaption
End If
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
picture.AlternativeText = photoId & " — " & areaName
cursor.SetRange picture.Range.End, picture.Range.End
cursor.InsertAfter vbCr & vbCr
cursor.Collapse wdCollapseEnd
Set picture = Nothing
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 Sub ValidateCreatedFile(ByVal filePath As String, _
ByVal fileLabel As String)
If Not FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 4439, , _
fileLabel & " was not created: " & filePath
End If
If FileLen(filePath) <= 0 Then
Err.Raise vbObjectError + 4440, , _
fileLabel & " is empty: " & filePath
End If
End Sub
Private Sub RequireTargetAbsent(ByVal filePath As String)
If FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 4441, , _
"Output already exists and will not be overwritten: " & filePath
End If
End Sub
Private Sub RequireExistingFile(ByVal filePath As String, _
ByVal fileLabel As String)
If Not FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 4442, , _
fileLabel & " was not found: " & filePath
End If
End Sub
Private Function FileExistsStrict(ByVal filePath As String) As Boolean
On Error GoTo CheckFailed
FileExistsStrict = (Len(Dir$(filePath, vbNormal Or vbHidden Or _
vbSystem Or vbReadOnly Or vbArchive)) > 0)
Exit Function
CheckFailed:
Err.Raise vbObjectError + 4443, , _
"Could not check file path: " & filePath & ". " & Err.Description
End Function
Private Sub RequireExistingFolder(ByVal folderPath As String, _
ByVal folderLabel As String)
If Not FolderExistsStrict(folderPath) Then
Err.Raise vbObjectError + 4444, , _
folderLabel & " was not found: " & folderPath
End If
End Sub
Private Sub EnsureFolder(ByVal folderPath As String)
If FolderExistsStrict(folderPath) Then Exit Sub
On Error GoTo CreateFailed
MkDir folderPath
If Not FolderExistsStrict(folderPath) Then GoTo CreateFailed
Exit Sub
CreateFailed:
Err.Raise vbObjectError + 4445, , _
"Could not create folder: " & folderPath & ". " & Err.Description
End Sub
Private Function CreateRunFolder(ByVal outputFolder As String) As String
Dim attempt As Long
Dim candidate As String
Dim runId As String
runId = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 100), "00000000")
For attempt = 0 To 99
candidate = JoinPath(outputFolder, "_staging_" & runId & _
IIf(attempt = 0, "", "_" & Format$(attempt, "00")))
If Not FolderExistsStrict(candidate) Then
MkDir candidate
CreateRunFolder = candidate
Exit Function
End If
Next attempt
Err.Raise vbObjectError + 4446, , _
"Could not create a unique staging folder in " & outputFolder & "."
End Function
Private Function FolderExistsStrict(ByVal folderPath As String) As Boolean
Dim attributes As Long
On Error GoTo NotFound
attributes = GetAttr(folderPath)
FolderExistsStrict = ((attributes And vbDirectory) = vbDirectory)
Exit Function
NotFound:
If Err.Number = 53 Or Err.Number = 76 Then
Err.Clear
FolderExistsStrict = False
Else
Err.Raise vbObjectError + 4447, , _
"Could not check folder path: " & folderPath & ". " & _
Err.Description
End If
End Function
Private Sub RemoveEmptyRunFolder(ByVal runFolder As String)
If Len(runFolder) = 0 Then Exit Sub
If Not FolderExistsStrict(runFolder) Then Exit Sub
If Len(Dir$(JoinPath(runFolder, "*"), vbNormal Or vbHidden Or _
vbSystem Or vbReadOnly Or vbArchive Or vbDirectory)) > 0 Then
Exit Sub
End If
RmDir runFolder
End Sub
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 prefix As String
Dim finalCharacter As String
upperName = UCase$(baseName)
Select Case upperName
Case "CON", "PRN", "AUX", "NUL", _
"COM1", "COM2", "COM3", "COM4", "COM5", _
"COM6", "COM7", "COM8", "COM9", _
"LPT1", "LPT2", "LPT3", "LPT4", "LPT5", _
"LPT6", "LPT7", "LPT8", "LPT9"
IsReservedWindowsName = True
Exit Function
End Select
If Len(upperName) = 4 Then
prefix = Left$(upperName, 3)
finalCharacter = Right$(upperName, 1)
If (prefix = "COM" Or prefix = "LPT") And _
(finalCharacter = ChrW$(&HB9) Or _
finalCharacter = ChrW$(&HB2) Or _
finalCharacter = ChrW$(&HB3)) Then
IsReservedWindowsName = True
End If
End If
End FunctionMicrosoft documents that InlineShapes.AddPicture can insert an independent picture at a supplied range, Bookmarks.Add can restore a bookmark on a range, and ExportAsFixedFormat creates PDF output.
Keep the automation inside its evidence limits
No authenticity proof
Extension, existence, and size checks do not prove that the file is a valid image, unedited, correctly dated, or connected to the stated property.
No professional conclusion
The macro formats facts supplied by the operator. It does not diagnose a defect, determine causation, estimate cost, or certify compliance.
No metadata preservation guarantee
Word and PDF processing may not preserve every source metadata field. Retain controlled originals separately when provenance matters.
No atomic output pair
DOCX and PDF are moved separately. A failure between moves can leave one final file and one staged file requiring review.
No concurrent-writer protection
Use one writer per output folder or add external coordination. Existence checks alone cannot eliminate a race with another process.
No release without review
Inspect identity, captions, image order, orientation, page breaks, signatures, privacy, and both output formats before distribution.
Run one evidence pilot before operational use
Use obvious test images
Label six disposable images with their PhotoID so identity and order are visible in the final pages.
Mix orientations
Include portrait, landscape, and square images to test fitting and white-space behavior.
Stress the captions
Use long area names and captions to expose wrapping and page-break problems before real evidence is used.
Test excluded evidence
Confirm that an EXCLUDE row remains absent while its PhotoID and Sequence still participate in the audit contract.
Test a blocked output
Create a disposable existing target and confirm the macro refuses to overwrite it.
Review away from Photos
Copy the finished DOCX and PDF elsewhere and confirm the embedded images remain visible without the source folder.
Approve only when every PhotoID, caption, area, image, and sequence matches the reviewed log; both files open independently; the layout survives mixed photo sizes; and the exception tests stop safely.
Use Contractor for a reviewed property document pack
DocxForge Pro Contractor starts with one reviewed case and can create several different documents for that case from different Word templates. For a property workflow, that could mean a detailed condition report, a handover summary, and an evidence cover sheet that reuse approved case data and assets.
Where it fits
Use it when one property case needs a coordinated set of different document types and each template has been reviewed for its own purpose.
Where it does not fit
It does not authenticate photographs, make inspection judgments, approve release, or turn an unreviewed evidence folder into a reliable report automatically.
Contractor: one reviewed property case
Review the shared case values, then choose the different Word documents required for that property.


Frequently asked questions
Is a property condition report the same as a professional home inspection?
No. This workflow organizes observations and photographs into a traceable evidence document. It does not supply professional judgment, certify safety, establish legal liability, or replace a qualified inspection where one is required.
Can one property report contain many photographs?
Yes. The VBA example reads one selected property record and all matching PhotoLog rows marked INCLUDE. It sorts them by Sequence, embeds each checked image, and creates one DOCX/PDF report for that property.
Why are display dates stored as text?
ReportDateDisplay and CapturedAtDisplay are document-ready strings. This prevents Excel serial numbers or locale-dependent formatting from appearing in Word. Store identifiers as text too when leading zeros matter.
Does the macro overwrite an existing report?
No in the ordinary single-writer workflow. It checks final targets before Word starts and again before committing staged files. DOCX and PDF are moved separately, so concurrent writers still require coordination and a rare partial commit needs manual review.
Does the macro prove that a photo is authentic?
No. It validates the filename, allowed extension, existence, nonzero size, unique PhotoID, and sequence. It does not decode the file, verify capture metadata, identify the photographed area, or prove authenticity.
Where does DocxForge Pro fit this workflow?
For one reviewed property case, Contractor can reuse shared case data and assets to create several different documents from different Word templates, such as a full condition report, a handover summary, and an evidence cover sheet. Test the mapping and outputs with your own templates before release.