Templates

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.

Relative photo folder
Exact row mapping
Embedded images
DOCX + PDF
The workflow uses one photo filename per row and one standard Word template. Keep the workbook, template, photo folder, and output folder together as one controlled local project.

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.

Portable local project

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.

Project layout

PhotoJobs.xlsm
Templates\PhotoRecordTemplate.docx
Photos\Site-0042-front.jpg
GeneratedPhotoDocuments\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.

Do not accept free-form paths

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.

Required headers in row 1

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.

Fit rather than fill

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.

Excel VBA — local photo-folder batchFactory-style example
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 Sub

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

Same folder rule, different unit of work

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.

DocxForge Pro Contractor Case Data screen for reviewing one case and its photo values
Contractor Case Data: verify the shared values for one reviewed case.
DocxForge Pro Contractor Documents screen for different Word templates in one case pack
Contractor Documents: choose the different templates required for that case.

Factory: Excel row batch

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

DocxForge Pro Factory Excel Data screen for reviewing rows and photo filenames
Factory Excel Data: verify the selected rows and their image values.
DocxForge Pro Factory Documents screen for one photo-enabled Word template
Factory Documents: use one tested Word template for the row-based batch.
Choose the module that matches the document unitTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Contractor case packsFactory row batchesLocal photo filesDOCX + PDF
Start Free 7-Day Trial

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.

Turn a validated photo folder into repeatable Word and PDF outputRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Exact row mappingEmbedded photosFactory batchesControlled output
Start Free 7-Day Trial