How to Use a Photo Folder with Excel-to-Word Templates
Keep photographs in one controlled local folder, store only exact filenames in Excel, and let one reusable Word template embed the correct image for each row.
Quick answer: store filenames in Excel and keep paths out of the rows
Create a Photos folder beside the saved Excel workbook. Put one exact filename such as Site-0042-front.jpg in each row, keep a fixed image bookmark named PhotoInsert in the Word template, and resolve the full path only when the document is generated.
Use Photos\Site-0042-front.jpg as the project relationship, but store only Site-0042-front.jpg in the cell. Avoid user-specific paths such as C:\Users\Name\Desktop\....
Define one literal photo-folder contract
A folder-based workflow is reliable only when the spreadsheet, filesystem, and template agree on the same record.
PhotoJobs.xlsmTemplates\PhotoRecordTemplate.docxPhotos\Site-0042-front.jpgGeneratedPhotoDocuments\Record_Site-0042.docx
Excel owns the mapping
Each row connects a stable DocumentID, one exact PhotoFile, the display text, and one output name.
The folder owns the binaries
Photographs stay outside Excel as ordinary PNG or JPEG files that can be reviewed, replaced, and backed up independently.
The VBA example rejects slashes, drive letters, wildcards, and parent-folder navigation in PhotoFile. Every row must resolve inside the one approved Photos folder.
Build the Excel contract in exact column order
Create a sheet named PhotoJobs. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) before running the macro.
A — DocumentID; B — PhotoFile; C — ClientName; D — PhotoCaption; E — OutputName; F — OutputFormat; G — RecordStatus.
DocumentID
Store it as text and keep it unique. The example accepts letters, digits, underscore, and hyphen.
PhotoFile
Use one filename with .png, .jpg, or .jpeg. Reusing the same controlled photo across rows is allowed.
OutputName
Store the filename stem without .docx or .pdf. Names must be unique without regard to letter case.
OutputFormat and status
Use exactly DOCX, PDF, or BOTH and exactly READY, HOLD, or DONE. The macro does not silently normalize case.
Prepare one Word template with four required bookmarks
Keep the page design in Word. The automation should replace defined bookmark ranges, not rebuild the layout.
DocumentID
A text bookmark for the stable business identifier used to trace the output back to Excel.
ClientName
A text bookmark for the row-specific display name.
PhotoCaption
A required bookmark that may receive blank text when the caption is optional.
PhotoInsert
A required image bookmark. Put it in an ordinary paragraph or tested table cell, not inside a field, content control, or existing image.
The example preserves the photograph's aspect ratio, reduces oversized images to a 432 × 288 point box, and does not enlarge smaller files. It does not crop images to fill a frame.
Run a six-record pilot before the full folder
Choose records that expose mapping, sizing, naming, and output problems before a large batch starts.
1. Landscape photograph
Confirm it fits the box without horizontal distortion.
2. Portrait photograph
Confirm height is limited without cropping or stretching.
3. Small photograph
Confirm the workflow does not upscale it and amplify artifacts.
4. Reused photograph
Reference one approved image from two rows and verify both mappings intentionally match.
5. Missing-file copy
Temporarily rename one pilot file. Preflight should stop before Word creates output.
6. Existing output
Create a conflicting filename and confirm the run refuses to overwrite it.
Free VBA helper: validate, embed, and export the photo batch
The macro validates all rows and files before Word starts, opens a fresh read-only copy of the template for every READY row, embeds the photograph at PhotoInsert, writes the text bookmarks, stages the result, and commits only non-conflicting output.
Complete preflight
Checks exact headers, string storage, unique IDs and output names, image files, output formats, statuses, and existing targets.
Fresh template instance
Every output starts from PhotoRecordTemplate.docx instead of reusing a previous generated document.
Embedded inline image
The source file is copied into the DOCX rather than left as a path-dependent link.
Owned staging cleanup
On failure the handler removes only files inside the unique staging folder for the current run.
Option Explicit
Private Const DATA_SHEET As String = "PhotoJobs"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "PhotoRecordTemplate.docx"
Private Const PHOTO_FOLDER As String = "\Photos"
Private Const OUTPUT_FOLDER As String = "\GeneratedPhotoDocuments"
Private Const FIRST_DATA_ROW As Long = 2
Private Const COL_DOCUMENT_ID As Long = 1
Private Const COL_PHOTO_FILE As Long = 2
Private Const COL_CLIENT_NAME As Long = 3
Private Const COL_PHOTO_CAPTION As Long = 4
Private Const COL_OUTPUT_NAME As Long = 5
Private Const COL_OUTPUT_FORMAT As Long = 6
Private Const COL_RECORD_STATUS As Long = 7
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const FORMAT_DOCX As String = "DOCX"
Private Const FORMAT_PDF As String = "PDF"
Private Const FORMAT_BOTH As String = "BOTH"
Private Const WD_FORMAT_DOCUMENT_DEFAULT As Long = 16
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_EXPORT_OPTIMIZE_FOR_PRINT As Long = 0
Private Const WD_EXPORT_ALL_DOCUMENT As Long = 0
Private Const WD_EXPORT_DOCUMENT_CONTENT As Long = 0
Private Const WD_EXPORT_CREATE_HEADING_BOOKMARKS As Long = 1
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_COLLAPSE_START As Long = 1
Private Const MSO_TRUE As Long = -1
Private Const FILE_ATTRIBUTES As Long = 39
Private Const MAX_PHOTO_WIDTH_POINTS As Double = 432
Private Const MAX_PHOTO_HEIGHT_POINTS As Double = 288
Public Sub GenerateDocumentsFromPhotoFolder()
Dim wb As Workbook
Dim ws As Worksheet
Dim dataValues As Variant
Dim lastCell As Range
Dim lastRow As Long
Dim dataRow As Long
Dim rowNumber As Long
Dim readyCount As Long
Dim generatedCount As Long
Dim templatePath As String
Dim photoFolder As String
Dim outputFolder As String
Dim runFolder As String
Dim documentId As String
Dim photoFile As String
Dim clientName As String
Dim photoCaption As String
Dim outputName As String
Dim outputFormat As String
Dim recordStatus As String
Dim photoPath As String
Dim stagedDocxPath As String
Dim stagedPdfPath As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim idRows As Object
Dim outputRows As Object
Dim wdApp As Object
Dim doc As Object
Dim errorNumber As Long
Dim errorMessage As String
Dim currentDocumentId As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 3900, , _
"Save this workbook as .xlsm before generating documents."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 3901, , _
"Open a writable workbook before generation."
End If
If wb.ProtectStructure Then
Err.Raise vbObjectError + 3902, , _
"Unprotect the workbook structure before generation."
End If
Set ws = RequireWorksheet(wb, DATA_SHEET)
If ws.ProtectContents Then
Err.Raise vbObjectError + 3903, , _
"Unprotect PhotoJobs so DONE can be recorded."
End If
ValidateHeaders ws
Set lastCell = ws.Range("A:G").Find(What:="*", _
After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 3904, , "PhotoJobs is empty."
End If
If lastCell.Row < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 3904, , _
"PhotoJobs has no data rows."
End If
lastRow = lastCell.Row
Application.CalculateFull
dataValues = ws.Range(ws.Cells(FIRST_DATA_ROW, 1), _
ws.Cells(lastRow, COL_RECORD_STATUS)).Value2
templatePath = wb.Path & TEMPLATE_FOLDER & "\" & TEMPLATE_FILE
photoFolder = wb.Path & PHOTO_FOLDER
outputFolder = wb.Path & OUTPUT_FOLDER
RequireNonEmptyFile templatePath, "Word template"
RequireExistingFolder photoFolder, "Photo folder"
EnsureFolder outputFolder
Set idRows = CreateObject("Scripting.Dictionary")
idRows.CompareMode = vbTextCompare
Set outputRows = CreateObject("Scripting.Dictionary")
outputRows.CompareMode = vbTextCompare
' Pass 1: validate the complete batch before Word starts.
For dataRow = 1 To UBound(dataValues, 1)
rowNumber = dataRow + FIRST_DATA_ROW - 1
If ArrayRowIsBlank(dataValues, dataRow) Then
Err.Raise vbObjectError + 3910, , _
"Blank row inside the data range: row " & rowNumber
End If
documentId = ReadRequiredText( _
dataValues(dataRow, COL_DOCUMENT_ID), "DocumentID", rowNumber)
photoFile = ReadRequiredText( _
dataValues(dataRow, COL_PHOTO_FILE), "PhotoFile", rowNumber)
clientName = ReadRequiredText( _
dataValues(dataRow, COL_CLIENT_NAME), "ClientName", rowNumber)
photoCaption = ReadOptionalText( _
dataValues(dataRow, COL_PHOTO_CAPTION), "PhotoCaption", rowNumber)
outputName = ReadRequiredText( _
dataValues(dataRow, COL_OUTPUT_NAME), "OutputName", rowNumber)
outputFormat = ReadRequiredText( _
dataValues(dataRow, COL_OUTPUT_FORMAT), "OutputFormat", rowNumber)
recordStatus = ReadRequiredText( _
dataValues(dataRow, COL_RECORD_STATUS), "RecordStatus", rowNumber)
RequireStringValue dataValues(dataRow, COL_DOCUMENT_ID), _
"DocumentID", rowNumber, False
RequireStringValue dataValues(dataRow, COL_PHOTO_FILE), _
"PhotoFile", rowNumber, False
RequireStringValue dataValues(dataRow, COL_CLIENT_NAME), _
"ClientName", rowNumber, False
RequireStringValue dataValues(dataRow, COL_PHOTO_CAPTION), _
"PhotoCaption", rowNumber, True
RequireStringValue dataValues(dataRow, COL_OUTPUT_NAME), _
"OutputName", rowNumber, False
RequireStringValue dataValues(dataRow, COL_OUTPUT_FORMAT), _
"OutputFormat", rowNumber, False
RequireStringValue dataValues(dataRow, COL_RECORD_STATUS), _
"RecordStatus", rowNumber, False
ValidateIdentifier documentId, "DocumentID", rowNumber
ValidatePhotoFileName photoFile, rowNumber
ValidateFileStem outputName, rowNumber
ValidateOutputFormat outputFormat, rowNumber
ValidateRecordStatus recordStatus, rowNumber
If idRows.Exists(documentId) Then
Err.Raise vbObjectError + 3911, , _
"Duplicate DocumentID in rows " & idRows(documentId) & _
" and " & rowNumber & ": " & documentId
End If
idRows.Add documentId, rowNumber
If outputRows.Exists(outputName) Then
Err.Raise vbObjectError + 3912, , _
"Duplicate OutputName in rows " & outputRows(outputName) & _
" and " & rowNumber & ": " & outputName
End If
outputRows.Add outputName, rowNumber
If recordStatus = STATUS_READY Then
readyCount = readyCount + 1
photoPath = photoFolder & "\" & photoFile
RequireNonEmptyFile photoPath, _
"PhotoFile in READY row " & rowNumber
finalDocxPath = outputFolder & "\" & outputName & ".docx"
finalPdfPath = outputFolder & "\" & outputName & ".pdf"
If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable finalDocxPath, rowNumber
End If
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable finalPdfPath, rowNumber
End If
End If
Next dataRow
If readyCount = 0 Then
Err.Raise vbObjectError + 3913, , _
"No READY rows were found on PhotoJobs."
End If
' Confirm that Excel can save before any output is created.
wb.Save
runFolder = CreateRunFolder(outputFolder)
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = WD_ALERTS_NONE
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
ValidateTemplateBookmarks doc
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
' Pass 2: generate from the cached values validated above.
For dataRow = 1 To UBound(dataValues, 1)
rowNumber = dataRow + FIRST_DATA_ROW - 1
recordStatus = CStr(dataValues(dataRow, COL_RECORD_STATUS))
If recordStatus = STATUS_READY Then
documentId = CStr(dataValues(dataRow, COL_DOCUMENT_ID))
currentDocumentId = documentId
photoFile = CStr(dataValues(dataRow, COL_PHOTO_FILE))
clientName = CStr(dataValues(dataRow, COL_CLIENT_NAME))
photoCaption = CStr(dataValues(dataRow, COL_PHOTO_CAPTION))
outputName = CStr(dataValues(dataRow, COL_OUTPUT_NAME))
outputFormat = CStr(dataValues(dataRow, COL_OUTPUT_FORMAT))
photoPath = photoFolder & "\" & photoFile
stagedDocxPath = runFolder & "\" & outputName & ".docx"
stagedPdfPath = runFolder & "\" & outputName & ".pdf"
finalDocxPath = outputFolder & "\" & outputName & ".docx"
finalPdfPath = outputFolder & "\" & outputName & ".pdf"
RequireTargetAvailable stagedDocxPath, rowNumber
RequireTargetAvailable stagedPdfPath, rowNumber
If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable finalDocxPath, rowNumber
End If
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable finalPdfPath, rowNumber
End If
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
WriteRequiredBookmark doc, "DocumentID", documentId
WriteRequiredBookmark doc, "ClientName", clientName
WriteRequiredBookmark doc, "PhotoCaption", photoCaption
InsertPhotoBookmark doc, "PhotoInsert", photoPath, _
MAX_PHOTO_WIDTH_POINTS, MAX_PHOTO_HEIGHT_POINTS
doc.SaveAs2 stagedDocxPath, WD_FORMAT_DOCUMENT_DEFAULT
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
doc.ExportAsFixedFormat stagedPdfPath, _
WD_EXPORT_FORMAT_PDF, False, _
WD_EXPORT_OPTIMIZE_FOR_PRINT, _
WD_EXPORT_ALL_DOCUMENT, 1, 1, _
WD_EXPORT_DOCUMENT_CONTENT, True, True, _
WD_EXPORT_CREATE_HEADING_BOOKMARKS, True, True, False
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
CommitStagedFile stagedDocxPath, finalDocxPath, rowNumber
Else
DeleteStagedFile stagedDocxPath
End If
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
CommitStagedFile stagedPdfPath, finalPdfPath, rowNumber
End If
ws.Cells(rowNumber, COL_RECORD_STATUS).Value = STATUS_DONE
wb.Save
generatedCount = generatedCount + 1
currentDocumentId = vbNullString
End If
Next dataRow
wdApp.ScreenUpdating = True
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
RemoveRunFolder runFolder
MsgBox generatedCount & " photo document(s) generated.", _
vbInformation, "Photo-folder generation"
Exit Sub
CleanFail:
errorNumber = Err.Number
errorMessage = Err.Description
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.ScreenUpdating = True
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
RemoveRunFolder runFolder
On Error GoTo 0
MsgBox "Generation stopped." & vbCrLf & _
IIf(Len(currentDocumentId) > 0, _
"Current DocumentID: " & currentDocumentId & vbCrLf, "") & _
"Error " & errorNumber & ": " & errorMessage & vbCrLf & _
"Review GeneratedPhotoDocuments before retrying. " & _
"Committed files are not deleted automatically.", _
vbCritical, "Photo-folder generation"
End Sub
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(sheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 3920, , _
"Required sheet is missing: " & sheetName
End If
End Function
Private Sub ValidateHeaders(ByVal ws As Worksheet)
Dim headers As Variant
Dim columnNumber As Long
Dim rawHeader As Variant
headers = Array("DocumentID", "PhotoFile", "ClientName", _
"PhotoCaption", "OutputName", "OutputFormat", "RecordStatus")
For columnNumber = 1 To 7
rawHeader = ws.Cells(1, columnNumber).Value2
If IsError(rawHeader) Then
Err.Raise vbObjectError + 3921, , _
"Header " & ws.Cells(1, columnNumber).Address(False, False) & _
" contains an Excel error."
End If
If StrComp(CStr(rawHeader), CStr(headers(columnNumber - 1)), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3921, , _
"Header " & ws.Cells(1, columnNumber).Address(False, False) & _
" must match exactly: " & headers(columnNumber - 1) & "."
End If
Next columnNumber
End Sub
Private Function ArrayRowIsBlank(ByVal values As Variant, _
ByVal dataRow As Long) As Boolean
Dim columnNumber As Long
For columnNumber = 1 To COL_RECORD_STATUS
If IsError(values(dataRow, columnNumber)) Then Exit Function
If Len(CStr(values(dataRow, columnNumber))) > 0 Then Exit Function
Next columnNumber
ArrayRowIsBlank = True
End Function
Private Function ReadRequiredText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
If IsError(rawValue) Then
Err.Raise vbObjectError + 3922, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
ReadRequiredText = CStr(rawValue)
If Len(ReadRequiredText) = 0 Then
Err.Raise vbObjectError + 3923, , _
fieldName & " is required in row " & rowNumber & "."
End If
ValidateOuterSpaces ReadRequiredText, fieldName, rowNumber
ValidateSingleLine ReadRequiredText, fieldName, rowNumber
ValidateControlCharacters ReadRequiredText, fieldName, rowNumber
End Function
Private Function ReadOptionalText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
If IsError(rawValue) Then
Err.Raise vbObjectError + 3922, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
ReadOptionalText = CStr(rawValue)
If Len(ReadOptionalText) > 0 Then
ValidateOuterSpaces ReadOptionalText, fieldName, rowNumber
ValidateSingleLine ReadOptionalText, fieldName, rowNumber
ValidateControlCharacters ReadOptionalText, fieldName, rowNumber
End If
End Function
Private Sub RequireStringValue(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal allowBlank As Boolean)
If IsError(rawValue) Then
Err.Raise vbObjectError + 3922, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
If allowBlank And Len(CStr(rawValue)) = 0 Then Exit Sub
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 3924, , _
fieldName & " must be stored as text in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateOuterSpaces(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
If StrComp(valueText, Trim$(valueText), vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3925, , _
fieldName & " has leading or trailing spaces in row " & _
rowNumber & "."
End If
End Sub
Private Sub ValidateSingleLine(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
If InStr(valueText, vbCr) > 0 Or InStr(valueText, vbLf) > 0 Or _
InStr(valueText, vbTab) > 0 Then
Err.Raise vbObjectError + 3926, , _
fieldName & " must be one line in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateControlCharacters(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
Dim position As Long
Dim characterCode As Long
For position = 1 To Len(valueText)
characterCode = AscW(Mid$(valueText, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Err.Raise vbObjectError + 3927, , _
fieldName & " contains a Windows control character in row " & _
rowNumber & "."
End If
Next position
End Sub
Private Sub ValidateIdentifier(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
Dim position As Long
Dim character As String
For position = 1 To Len(valueText)
character = Mid$(valueText, position, 1)
If Not (character Like "[A-Za-z0-9_-]") Then
Err.Raise vbObjectError + 3928, , _
fieldName & " may use only letters, digits, underscore, and hyphen " & _
"in row " & rowNumber & "."
End If
Next position
End Sub
Private Sub ValidatePhotoFileName(ByVal fileName As String, _
ByVal rowNumber As Long)
Dim lowerName As String
Dim baseName As String
If Len(fileName) > 180 Then
Err.Raise vbObjectError + 3929, , _
"PhotoFile exceeds 180 characters in row " & rowNumber & "."
End If
If InStr(1, fileName, "\", vbBinaryCompare) > 0 Or _
InStr(1, fileName, "/", vbBinaryCompare) > 0 Or _
InStr(1, fileName, ":", vbBinaryCompare) > 0 Or _
InStr(1, fileName, "*", vbBinaryCompare) > 0 Or _
InStr(1, fileName, "?", vbBinaryCompare) > 0 Or _
InStr(1, fileName, """", vbBinaryCompare) > 0 Or _
InStr(1, fileName, "<", vbBinaryCompare) > 0 Or _
InStr(1, fileName, ">", vbBinaryCompare) > 0 Or _
InStr(1, fileName, "|", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 3929, , _
"PhotoFile must be one filename, not a path, in row " & rowNumber & "."
End If
If Left$(fileName, 1) = "." Or Right$(fileName, 1) = "." Or _
Right$(fileName, 1) = " " Then
Err.Raise vbObjectError + 3929, , _
"PhotoFile has an unsafe leading or trailing character in row " & _
rowNumber & "."
End If
lowerName = LCase$(fileName)
If Not (Right$(lowerName, 4) = ".png" Or _
Right$(lowerName, 4) = ".jpg" Or _
Right$(lowerName, 5) = ".jpeg") Then
Err.Raise vbObjectError + 3929, , _
"PhotoFile must end in .png, .jpg, or .jpeg in row " & _
rowNumber & "."
End If
baseName = fileName
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(UCase$(baseName)) Then
Err.Raise vbObjectError + 3929, , _
"PhotoFile starts with a reserved Windows name in row " & _
rowNumber & "."
End If
End Sub
Private Sub ValidateOutputFormat(ByVal valueText As String, _
ByVal rowNumber As Long)
If valueText <> FORMAT_DOCX And valueText <> FORMAT_PDF And _
valueText <> FORMAT_BOTH Then
Err.Raise vbObjectError + 3930, , _
"OutputFormat must be DOCX, PDF, or BOTH in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateRecordStatus(ByVal valueText As String, _
ByVal rowNumber As Long)
If valueText <> STATUS_READY And valueText <> STATUS_HOLD And _
valueText <> STATUS_DONE Then
Err.Raise vbObjectError + 3931, , _
"RecordStatus must be READY, HOLD, or DONE in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateFileStem(ByVal fileStem As String, _
ByVal rowNumber As Long)
Dim forbidden As Variant
Dim item As Variant
Dim position As Long
Dim characterCode As Long
Dim baseName As String
forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
If Len(fileStem) > 120 Then
Err.Raise vbObjectError + 3932, , _
"OutputName exceeds 120 characters in row " & rowNumber & "."
End If
If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
Err.Raise vbObjectError + 3932, , _
"OutputName ends with a dot or space in row " & rowNumber & "."
End If
If LCase$(Right$(fileStem, 5)) = ".docx" Or _
LCase$(Right$(fileStem, 4)) = ".pdf" Then
Err.Raise vbObjectError + 3932, , _
"Store OutputName without .docx or .pdf in row " & rowNumber & "."
End If
For Each item In forbidden
If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 3932, , _
"OutputName contains a forbidden character in row " & _
rowNumber & "."
End If
Next item
For position = 1 To Len(fileStem)
characterCode = AscW(Mid$(fileStem, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Err.Raise vbObjectError + 3932, , _
"OutputName contains a control character in row " & _
rowNumber & "."
End If
Next position
baseName = fileStem
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(UCase$(baseName)) Then
Err.Raise vbObjectError + 3932, , _
"OutputName starts with a reserved Windows name in row " & _
rowNumber & "."
End If
End Sub
Private Function IsReservedWindowsName(ByVal upperBase As String) As Boolean
Dim finalCharacter As String
Select Case upperBase
Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(upperBase, 3) = "COM" Or Left$(upperBase, 3) = "LPT" Then
If Len(upperBase) = 4 And Mid$(upperBase, 4, 1) Like "[1-9]" Then
IsReservedWindowsName = True
Exit Function
End If
If Len(upperBase) = 4 Then
finalCharacter = Mid$(upperBase, 4, 1)
If finalCharacter = ChrW$(&HB9) Or _
finalCharacter = ChrW$(&HB2) Or _
finalCharacter = ChrW$(&HB3) Then
IsReservedWindowsName = True
End If
End If
End If
End Function
Private Sub ValidateTemplateBookmarks(ByVal doc As Object)
Dim names As Variant
Dim item As Variant
names = Array("DocumentID", "ClientName", "PhotoCaption", "PhotoInsert")
For Each item In names
If Not doc.Bookmarks.Exists(CStr(item)) Then
Err.Raise vbObjectError + 3933, , _
"Required Word bookmark is missing: " & item
End If
ValidateBookmarkContainer doc, CStr(item)
Next item
End Sub
Private Sub ValidateBookmarkContainer(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
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 + 3935, , _
"Bookmark contains a field, content control, or image: " & _
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 targetStyle As Variant
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3934, , _
"Required Word bookmark is missing: " & bookmarkName
End If
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 + 3935, , _
"Text bookmark contains another Word object: " & bookmarkName
End If
targetStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = targetStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3936, , _
"Bookmark was not restored: " & bookmarkName
End If
End Sub
Private Sub InsertPhotoBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal photoPath As String, _
ByVal maxWidth As Double, _
ByVal maxHeight As Double)
Dim targetRange As Object
Dim picture As Object
Dim scaleFactor As Double
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3937, , _
"Required photo bookmark is missing: " & bookmarkName
End If
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 + 3938, , _
"Photo bookmark contains another Word object: " & bookmarkName
End If
targetRange.Text = vbNullString
targetRange.Collapse WD_COLLAPSE_START
Set picture = targetRange.InlineShapes.AddPicture( _
photoPath, False, True, targetRange)
picture.LockAspectRatio = MSO_TRUE
scaleFactor = 1#
If picture.Width > maxWidth Then
scaleFactor = maxWidth / picture.Width
End If
If picture.Height * scaleFactor > maxHeight Then
scaleFactor = maxHeight / picture.Height
End If
If scaleFactor < 1# Then picture.Width = picture.Width * scaleFactor
doc.Bookmarks.Add bookmarkName, picture.Range
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3939, , _
"Photo bookmark was not restored: " & bookmarkName
End If
End Sub
Private Sub RequireExistingFolder(ByVal folderPath As String, _
ByVal labelText As String)
Dim attributes As Long
On Error GoTo CheckFailed
If Len(Dir$(folderPath, vbDirectory)) = 0 Then
Err.Raise vbObjectError + 3940, , _
labelText & " is missing: " & folderPath
End If
attributes = GetAttr(folderPath)
If (attributes And vbDirectory) <> vbDirectory Then
Err.Raise vbObjectError + 3940, , _
labelText & " is not a folder: " & folderPath
End If
Exit Sub
CheckFailed:
If Err.Number >= vbObjectError + 3900 And _
Err.Number <= vbObjectError + 3999 Then
Err.Raise Err.Number, , Err.Description
End If
Err.Raise vbObjectError + 3941, , _
"Could not inspect " & labelText & ": " & folderPath
End Sub
Private Sub RequireNonEmptyFile(ByVal filePath As String, _
ByVal labelText As String)
Dim fileSize As Long
If Not FileExists(filePath) Then
Err.Raise vbObjectError + 3942, , _
labelText & " is missing: " & filePath
End If
On Error GoTo SizeFailed
fileSize = FileLen(filePath)
If fileSize <= 0 Then
Err.Raise vbObjectError + 3942, , _
labelText & " is empty: " & filePath
End If
Exit Sub
SizeFailed:
If Err.Number >= vbObjectError + 3900 And _
Err.Number <= vbObjectError + 3999 Then
Err.Raise Err.Number, , Err.Description
End If
Err.Raise vbObjectError + 3943, , _
"Could not read " & labelText & ": " & filePath
End Sub
Private Sub RequireTargetAvailable(ByVal filePath As String, _
ByVal rowNumber As Long)
If FileExists(filePath) Then
Err.Raise vbObjectError + 3944, , _
"Output already exists for row " & rowNumber & ": " & filePath
End If
End Sub
Private Function FileExists(ByVal filePath As String) As Boolean
Dim foundName As String
Dim checkErrorNumber As Long
Dim checkErrorDescription As String
On Error GoTo CheckFailed
foundName = Dir$(filePath, FILE_ATTRIBUTES)
FileExists = (Len(foundName) > 0)
Exit Function
CheckFailed:
checkErrorNumber = Err.Number
checkErrorDescription = Err.Description
Err.Clear
Err.Raise vbObjectError + 3945, "FileExists", _
"Could not check path: " & filePath & vbCrLf & _
"File-system error " & checkErrorNumber & ": " & _
checkErrorDescription
End Function
Private Sub EnsureFolder(ByVal folderPath As String)
Dim attributes As Long
If Len(Dir$(folderPath, vbDirectory)) = 0 Then
MkDir folderPath
Else
attributes = GetAttr(folderPath)
If (attributes And vbDirectory) <> vbDirectory Then
Err.Raise vbObjectError + 3946, , _
"Expected a folder but found a file: " & folderPath
End If
End If
End Sub
Private Function CreateRunFolder(ByVal outputFolder As String) As String
Dim attempt As Long
Dim candidate As String
For attempt = 0 To 99
candidate = outputFolder & "\_run_" & _
Format$(Now, "yyyymmdd_hhnnss") & "_" & Format$(attempt, "00")
If Len(Dir$(candidate, vbDirectory)) = 0 Then
MkDir candidate
CreateRunFolder = candidate
Exit Function
End If
Next attempt
Err.Raise vbObjectError + 3947, , _
"Could not create a unique staging folder."
End Function
Private Sub CommitStagedFile(ByVal stagedPath As String, _
ByVal finalPath As String, _
ByVal rowNumber As Long)
Dim fso As Object
RequireNonEmptyFile stagedPath, "Staged output"
RequireTargetAvailable finalPath, rowNumber
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile stagedPath, finalPath
If Not FileExists(finalPath) Then
Err.Raise vbObjectError + 3948, , _
"Committed output could not be verified: " & finalPath
End If
End Sub
Private Sub DeleteStagedFile(ByVal stagedPath As String)
If FileExists(stagedPath) Then Kill stagedPath
End Sub
Private Sub RemoveRunFolder(ByVal runFolder As String)
Dim itemName As String
If Len(runFolder) = 0 Then Exit Sub
If Len(Dir$(runFolder, vbDirectory)) = 0 Then Exit Sub
itemName = Dir$(runFolder & "\*", FILE_ATTRIBUTES)
Do While Len(itemName) > 0
If itemName <> "." And itemName <> ".." Then
If (GetAttr(runFolder & "\" & itemName) And vbDirectory) = 0 Then
Kill runFolder & "\" & itemName
End If
End If
itemName = Dir$()
Loop
RmDir runFolder
End SubThe Word object model documents the relevant methods: InlineShapes.AddPicture inserts an image at a range and can create an independent copy; Bookmarks.Add restores a bookmark around the new range; and Document.ExportAsFixedFormat creates the PDF result.
Understand what the folder contract does not guarantee
File extension is not authentication
A .jpg or .png name and non-zero size do not prove valid content, ownership, or permission to publish the photograph.
DOCX and PDF are not one atomic pair
A filesystem cannot commit two separate files as one indivisible operation. If a later move or workbook save fails, review the output folder before retrying.
One writer should own the output folder
The example rechecks destinations before moving staged files, but a shared folder still needs process-level coordination to avoid concurrent naming races.
Large source photos remain large
Changing visible dimensions in Word does not necessarily reduce embedded bytes. Prepare separate output copies when PDF size matters.
Use the photo-folder contract in the correct DocxForge Pro module
DocxForge Pro includes Contractor and Factory. Both run locally and can use controlled photo values, but they organize the work around different output units.
Contractor: one case, several different documents
Load and review one case dataset, including its approved photo values, then generate several different documents for that case from different Word templates. Use it for a case pack in which the same reviewed data must flow into a report, letter, checklist, or other distinct document.
Factory: many rows, one standard template
Select many independent Excel rows, map each row's photo filename, and run them through one standard Word template. Each selected row creates its own document, which is the direct product equivalent of the row-by-row VBA workflow in this article.
In Contractor, verify the photo value once in the reviewed case and verify every different template that uses it. In Factory, verify the photo value on each selected row and the one standard template used for the batch.
Contractor: reviewed case workflow
Review one case and its photo values, then generate the case document set from different Word templates.


Factory: Excel row batch
Review many independent rows and their photo values, then generate each row through one standard Word template.


Frequently asked questions
Can the photo folder stay beside the Excel workbook?
Yes. The example uses a Photos folder beside the saved .xlsm workbook. Excel stores only a filename such as Site-0042-front.jpg, so moving the complete project folder together does not require rewriting absolute paths.
Can several rows reuse the same photo?
Yes. PhotoFile does not need to be unique. Several records may reference the same controlled image, while DocumentID and OutputName remain unique so the generated files are distinguishable.
What happens when a photograph is missing or empty?
The preflight stops before Word starts. It checks every PNG or JPEG referenced by a READY row for existence and non-zero size, reports the affected row, and creates no documents from a partially validated READY batch.
Are the inserted photographs linked or embedded?
They are embedded. The macro calls InlineShapes.AddPicture with LinkToFile set to False and SaveWithDocument set to True, so the finished DOCX does not depend on the source photo remaining at the same path.
Why does this workflow use one Word template?
It follows the Factory pattern: many independent Excel rows feed one standard template and produce separate outputs. If one reviewed case must create several different documents from different templates, use the Contractor workflow instead.
Does the macro prove that a renamed file is a valid image?
No. It checks the filename extension, file presence, and non-zero size. Word insertion is the practical format test, but the macro does not authenticate the file content, ownership, or permission to use the photograph.