Fixes & Troubleshooting

Fix: PDF Output Is Too Large After Adding Photos

Find the size problem before export: measure the prepared photo bytes assigned to each document, reduce approved working copies before insertion, and treat Word's PDF setting as the final control rather than the whole image pipeline.

Measure source bytes
Prepare working copies
Budget per document
Verify DOCX + PDF
Visible image size is not the same as embedded byte size. Keep originals and optimize controlled copies for the document's real output requirements.

Quick answer: fix photo bytes before asking Word to export

Displayed size controls layout

A photo reduced to 6 cm wide may look correct on the page, but Word can still retain much more source image data than that layout needs.

Prepared bytes control the budget

Create approved working copies at a resolution and quality suitable for the final page, then audit the combined bytes assigned to each document.

Use two controls

Prepare images before insertion, then choose and test the Word PDF export setting. Neither step alone proves acceptable quality or file size.

Diagnose where the extra bytes enter the workflow

Oversized camera originals

A multi-megapixel photo is inserted into a small report slot without creating a document-ready copy.

Visual resize only

Width and height change in Word, but the underlying image data or editing history remains much larger.

Lossless PNG for photographs

A photo saved as PNG can be much larger than an appropriate JPEG; logos, diagrams, and transparency have different needs.

Cropped data retained

Word may keep data needed to undo a crop unless the document's image settings deliberately discard it.

Repeated or duplicate assets

Several near-identical photos, accidental duplicates, or the same large image in multiple sections multiply the payload.

Export target mismatch

A print-oriented PDF may be larger than an on-screen copy. Choose based on the delivery requirement, not the smallest possible number.

Measure before changing image quality

Count photos per document

One 2 MB photo and twelve 2 MB photos are different document budgets.

Record each source byte size

Measure the exact working copy used for generation, not only the original or a preview thumbnail.

Record the combined budget

Aggregate the prepared photo bytes by DocumentID before Word starts.

Compare DOCX and PDF

If both are large, the source payload is the likely first target. If only PDF grows, review export and document settings.

Test visual acceptance

Open the output at the intended viewing and print conditions. Byte reduction is not success if evidence becomes unreadable.

Set a documented threshold

Choose limits from business needs and record why. Do not present an arbitrary threshold as universal.

Prepare controlled copies without destroying originals

Preserve the original

Keep the source file according to your evidence, retention, and access policy. Work on a separate copy.

Choose target pixel dimensions

Base the copy on the maximum physical size and required output quality, not the camera's maximum resolution.

Choose the right format

JPEG often suits photographs; PNG suits transparency and crisp flat graphics. Test the actual content.

Use one staging folder

Put reviewed copies in PreparedPhotos beside the audit workbook and reference filenames only.

Record approval state

Use PreparedForUse = YES only after the copy has passed the defined visual and retention checks.

Do not claim authentication

Resizing and byte checks do not prove when, where, or by whom a photo was created.

Use Word compression and PDF export as final controls

Compress Pictures deliberately

In Word Desktop, select a picture, open Picture Format, choose Compress Pictures, decide whether to apply it to all pictures, and choose the required resolution.

Review cropped-area deletion

Deleting cropped data can reduce size but removes the ability to restore that cropped content from the document.

Check document image settings

Make sure “Do not compress images in file” is not enabled when the release policy requires Word compression.

Choose screen or print export

OptimizeForOnScreen may suit electronic delivery; print output may need higher fidelity and a larger PDF.

Do not resize only the shape

Changing displayed width is a layout action. It is not a reliable promise that embedded bytes were removed.

Inspect the released file

Record PDF bytes, page count, photo readability, and the selected export policy after every meaningful template change.

Official Microsoft guidance: reduce picture file size, Document.ExportAsFixedFormat, and InlineShapes.AddPicture.

Free VBA helper: audit a prepared-photo budget

Save a writable workbook as .xlsm. Create PhotoManifest with A — DocumentID; B — PhotoFile; C — PreparedForUse; D — ReportStatus; E — OutputName. Put reviewed working copies in PreparedPhotos. Adjust both example byte limits before treating them as policy.

Exact manifest contract

Checks headers, external spaces, exact YES/NO and READY/HOLD values, text IDs, output names, and repeated document/photo pairs.

Safe local filenames

Rejects paths, wildcards, control characters, terminal dots or spaces, and reserved Windows base names before using Dir$.

Two byte budgets

Checks every prepared photo and aggregates combined photo bytes for each DocumentID.

Honest scope

Does not resize, rewrite, decode, authenticate, insert, or export an image. A clean audit is only a preflight result.

Excel VBA — prepared-photo byte auditPreflight only
Option Explicit

Private Const MANIFEST_SHEET As String = "PhotoManifest"
Private Const AUDIT_PREFIX As String = "PhotoBudgetAudit_"
Private Const PHOTO_FOLDER As String = "\PreparedPhotos"
Private Const FIRST_DATA_ROW As Long = 2

' Example policy limits: adjust these before using the audit as a release gate.
Private Const MAX_PHOTO_BYTES As Double = 2500000#
Private Const MAX_DOCUMENT_PHOTO_BYTES As Double = 12000000#

Private Const COL_DOCUMENT_ID As Long = 1
Private Const COL_PHOTO_FILE As Long = 2
Private Const COL_PREPARED As Long = 3
Private Const COL_STATUS As Long = 4
Private Const COL_OUTPUT_NAME As Long = 5

Private Const VALUE_YES As String = "YES"
Private Const VALUE_NO As String = "NO"
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"

Public Sub AuditPdfPhotoBudget()
    Dim wb As Workbook
    Dim manifestWs As Worksheet
    Dim auditWs As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim auditRow As Long
    Dim documentId As String
    Dim photoFile As String
    Dim preparedValue As String
    Dim reportStatus As String
    Dim outputName As String
    Dim photoPath As String
    Dim photoBytes As Double
    Dim rowErrorStart As Long
    Dim errorCount As Long
    Dim warningCount As Long
    Dim readyPhotoCount As Long
    Dim holdPhotoCount As Long
    Dim auditName As String
    Dim seenPairs As Object
    Dim canonicalIds As Object
    Dim documentBytes As Object
    Dim documentPhotoCounts As Object
    Dim documentOutputs As Object
    Dim documentStatuses As Object
    Dim documentErrorCounts As Object
    Dim outputOwners As Object
    Dim key As Variant
    Dim documentResult As String
    Dim documentDetail As String
    Dim errorMessage As String

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    If Len(wb.Path) = 0 Then
        Err.Raise vbObjectError + 3200, , _
            "Save this workbook as .xlsm before running the audit."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 3201, , _
            "Open a writable copy of the audit workbook."
    End If
    If wb.ProtectStructure Then
        Err.Raise vbObjectError + 3202, , _
            "Unprotect the workbook structure before adding an audit sheet."
    End If

    Set manifestWs = RequireWorksheet(wb, MANIFEST_SHEET)
    ValidateHeaders manifestWs
    Set lastCell = manifestWs.Range("A:E").Find(What:="*", _
        After:=manifestWs.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    If lastCell Is Nothing Then
        Err.Raise vbObjectError + 3203, , _
            "The PhotoManifest sheet is empty."
    End If
    If lastCell.Row < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 3203, , _
            "The PhotoManifest sheet has no photo rows."
    End If
    lastRow = lastCell.Row

    Set seenPairs = CreateObject("Scripting.Dictionary")
    seenPairs.CompareMode = vbTextCompare
    Set canonicalIds = CreateObject("Scripting.Dictionary")
    canonicalIds.CompareMode = vbTextCompare
    Set documentBytes = CreateObject("Scripting.Dictionary")
    documentBytes.CompareMode = vbBinaryCompare
    Set documentPhotoCounts = CreateObject("Scripting.Dictionary")
    documentPhotoCounts.CompareMode = vbBinaryCompare
    Set documentOutputs = CreateObject("Scripting.Dictionary")
    documentOutputs.CompareMode = vbBinaryCompare
    Set documentStatuses = CreateObject("Scripting.Dictionary")
    documentStatuses.CompareMode = vbBinaryCompare
    Set documentErrorCounts = CreateObject("Scripting.Dictionary")
    documentErrorCounts.CompareMode = vbBinaryCompare
    Set outputOwners = CreateObject("Scripting.Dictionary")
    outputOwners.CompareMode = vbTextCompare

    auditName = UniqueAuditSheetName(wb)
    Set auditWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    auditWs.Name = auditName
    WriteAuditHeaders auditWs
    auditRow = 2
    Application.ScreenUpdating = False

    For rowNumber = FIRST_DATA_ROW To lastRow
        rowErrorStart = errorCount
        If Application.CountA(manifestWs.Range( _
            manifestWs.Cells(rowNumber, COL_DOCUMENT_ID), _
            manifestWs.Cells(rowNumber, COL_OUTPUT_NAME))) = 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "ROW", _
                "ERROR", "Blank row inside the manifest range.", _
                errorCount, warningCount
            GoTo NextRow
        End If

        documentId = ReadExactText(manifestWs.Cells(rowNumber, COL_DOCUMENT_ID), _
            "DocumentID", rowNumber, True, auditWs, auditRow, _
            errorCount, warningCount)
        photoFile = ReadExactText(manifestWs.Cells(rowNumber, COL_PHOTO_FILE), _
            "PhotoFile", rowNumber, True, auditWs, auditRow, _
            errorCount, warningCount)
        preparedValue = ReadExactText(manifestWs.Cells(rowNumber, COL_PREPARED), _
            "PreparedForUse", rowNumber, True, auditWs, auditRow, _
            errorCount, warningCount)
        reportStatus = ReadExactText(manifestWs.Cells(rowNumber, COL_STATUS), _
            "ReportStatus", rowNumber, True, auditWs, auditRow, _
            errorCount, warningCount)
        outputName = ReadExactText(manifestWs.Cells(rowNumber, COL_OUTPUT_NAME), _
            "OutputName", rowNumber, True, auditWs, auditRow, _
            errorCount, warningCount)

        If Not IsError(manifestWs.Cells(rowNumber, COL_DOCUMENT_ID).Value2) Then
            If VarType(manifestWs.Cells(rowNumber, COL_DOCUMENT_ID).Value2) <> vbString Then
                WriteAuditRow auditWs, auditRow, rowNumber, "DocumentID", _
                    "ERROR", "Store DocumentID as text.", _
                    errorCount, warningCount
            End If
        End If
        If Len(documentId) > 0 Then
            ValidateIdentifier documentId, rowNumber, auditWs, auditRow, _
                errorCount, warningCount
            If canonicalIds.Exists(documentId) Then
                If StrComp(CStr(canonicalIds(documentId)), documentId, _
                           vbBinaryCompare) <> 0 Then
                    WriteAuditRow auditWs, auditRow, rowNumber, "DocumentID", _
                        "ERROR", "DocumentID differs only by letter case from " & _
                        canonicalIds(documentId) & ".", errorCount, warningCount
                End If
            Else
                canonicalIds.Add documentId, documentId
            End If
        End If

        If preparedValue <> VALUE_YES And preparedValue <> VALUE_NO Then
            WriteAuditRow auditWs, auditRow, rowNumber, "PreparedForUse", _
                "ERROR", "Use YES or NO exactly.", errorCount, warningCount
        End If
        If reportStatus <> STATUS_READY And reportStatus <> STATUS_HOLD Then
            WriteAuditRow auditWs, auditRow, rowNumber, "ReportStatus", _
                "ERROR", "Use READY or HOLD exactly.", errorCount, warningCount
        End If
        If reportStatus = STATUS_READY And preparedValue <> VALUE_YES Then
            WriteAuditRow auditWs, auditRow, rowNumber, "RELEASE", _
                "ERROR", "A READY photo row must have PreparedForUse = YES.", _
                errorCount, warningCount
        End If

        If Len(outputName) > 0 Then
            ValidateFileStem outputName, rowNumber, auditWs, auditRow, _
                errorCount, warningCount
            If Len(documentId) > 0 Then
                If outputOwners.Exists(outputName) Then
                    If StrComp(CStr(outputOwners(outputName)), documentId, _
                               vbBinaryCompare) <> 0 Then
                        WriteAuditRow auditWs, auditRow, rowNumber, _
                            "OutputName", "ERROR", _
                            "OutputName is already owned by DocumentID " & _
                            outputOwners(outputName) & ".", _
                            errorCount, warningCount
                    End If
                Else
                    outputOwners.Add outputName, documentId
                End If
            End If
        End If
        If Len(photoFile) > 0 Then
            photoPath = ResolvePhotoPath(wb.Path, photoFile, rowNumber, _
                auditWs, auditRow, errorCount, warningCount)
            If Len(photoPath) > 0 Then
                photoBytes = InspectPhotoFile(photoPath, rowNumber, _
                    auditWs, auditRow, errorCount, warningCount)
            End If
        End If

        If Len(documentId) > 0 And Len(photoFile) > 0 Then
            key = documentId & "||" & photoFile
            If seenPairs.Exists(key) Then
                WriteAuditRow auditWs, auditRow, rowNumber, "MANIFEST", _
                    "ERROR", "Duplicate document/photo pair; first used in row " & _
                    seenPairs(key) & ".", errorCount, warningCount
            Else
                seenPairs.Add key, rowNumber
            End If
        End If

        If Len(documentId) > 0 Then
            AddDocumentValue documentBytes, documentId, photoBytes
            AddDocumentValue documentPhotoCounts, documentId, 1
            CheckDocumentContract documentOutputs, documentId, outputName, _
                "OutputName", rowNumber, auditWs, auditRow, errorCount, warningCount
            CheckDocumentContract documentStatuses, documentId, reportStatus, _
                "ReportStatus", rowNumber, auditWs, auditRow, errorCount, warningCount
        End If

        If reportStatus = STATUS_HOLD Then
            holdPhotoCount = holdPhotoCount + 1
            WriteAuditRow auditWs, auditRow, rowNumber, "ROW PREFLIGHT", _
                "WARNING", "HOLD photo rows must not enter production generation.", _
                errorCount, warningCount
        ElseIf reportStatus = STATUS_READY Then
            readyPhotoCount = readyPhotoCount + 1
            If errorCount = rowErrorStart Then
                WriteAuditRow auditWs, auditRow, rowNumber, "ROW PREFLIGHT", _
                    "PASS", "READY row passed its recorded preflight checks.", _
                    errorCount, warningCount
            Else
                WriteAuditRow auditWs, auditRow, rowNumber, "ROW PREFLIGHT", _
                    "ERROR", "READY row has preflight errors.", _
                    errorCount, warningCount
            End If
        End If
        If Len(documentId) > 0 Then
            AddDocumentValue documentErrorCounts, documentId, _
                CDbl(errorCount - rowErrorStart)
        End If
NextRow:
        photoPath = vbNullString
        photoBytes = 0
    Next rowNumber

    For Each key In documentBytes.Keys
        If CDbl(documentErrorCounts(key)) > 0 Then
            documentResult = "ERROR"
            documentDetail = _
                "Document is not released because one or more photo rows have errors."
        ElseIf CStr(documentStatuses(key)) <> STATUS_READY Then
            documentResult = "WARNING"
            documentDetail = _
                "Document is not released because its manifest status is HOLD."
        ElseIf CDbl(documentBytes(key)) > MAX_DOCUMENT_PHOTO_BYTES Then
            documentResult = "ERROR"
            documentDetail = _
                "Document is not released because combined prepared-photo bytes exceed the example budget."
        Else
            documentResult = "PASS"
            documentDetail = _
                "READY document passed row preflight and the combined photo-byte budget."
        End If
        WriteDocumentSummary auditWs, auditRow, CStr(key), _
            CLng(documentPhotoCounts(key)), CDbl(documentBytes(key)), _
            documentResult, documentDetail, errorCount, warningCount
    Next key

    WriteSummary auditWs, readyPhotoCount, holdPhotoCount, _
        errorCount, warningCount
    auditWs.Columns("A:G").AutoFit
    auditWs.Rows(1).Font.Bold = True
    wb.Save
    Application.ScreenUpdating = True

    MsgBox "Photo budget audit created: " & auditName & vbCrLf & _
           "Errors: " & errorCount & vbCrLf & _
           "Warnings: " & warningCount, _
           IIf(errorCount = 0, vbInformation, vbExclamation), _
           "PDF photo budget audit"
    Exit Sub

CleanFail:
    errorMessage = Err.Description
    Application.ScreenUpdating = True
    MsgBox "Audit stopped." & vbCrLf & errorMessage, _
        vbCritical, "PDF photo budget audit"
    Err.Clear
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 + 3210, , _
            "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", "PreparedForUse", _
        "ReportStatus", "OutputName")
    For columnNumber = 1 To 5
        rawHeader = ws.Cells(1, columnNumber).Value2
        If IsError(rawHeader) Then
            Err.Raise vbObjectError + 3211, , _
                "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 + 3211, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " must match exactly: " & headers(columnNumber - 1) & "."
        End If
    Next columnNumber
End Sub

Private Function ReadExactText(ByVal cell As Range, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long, _
                               ByVal required As Boolean, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount As Long) As String
    Dim rawValue As Variant
    rawValue = cell.Value2
    If IsError(rawValue) Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Cell contains an Excel error.", errorCount, warningCount
        Exit Function
    End If
    ReadExactText = CStr(rawValue)
    If required And Len(ReadExactText) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Required value is blank.", errorCount, warningCount
        Exit Function
    End If
    If StrComp(ReadExactText, Trim$(ReadExactText), vbBinaryCompare) <> 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Leading or trailing spaces are not allowed.", _
            errorCount, warningCount
    End If
    If InStr(ReadExactText, vbCr) > 0 Or InStr(ReadExactText, vbLf) > 0 Or _
       InStr(ReadExactText, vbTab) > 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Tabs and line breaks are not allowed.", errorCount, warningCount
    End If
End Function

Private Sub ValidateIdentifier(ByVal valueText As String, _
                               ByVal rowNumber As Long, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount 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
            WriteAuditRow auditWs, auditRow, rowNumber, "DocumentID", _
                "ERROR", "Use only letters, digits, underscore, and hyphen.", _
                errorCount, warningCount
            Exit Sub
        End If
    Next position
End Sub

Private Sub ValidateFileStem(ByVal fileStem As String, _
                             ByVal rowNumber As Long, _
                             ByVal auditWs As Worksheet, _
                             ByRef auditRow As Long, _
                             ByRef errorCount As Long, _
                             ByRef warningCount As Long)
    Dim forbidden As Variant
    Dim item As Variant
    Dim baseName As String
    Dim position As Long
    Dim characterCode As Long
    forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    If Len(fileStem) > 120 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "OutputName", "ERROR", _
            "Use 120 characters or fewer.", errorCount, warningCount
    End If
    If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
        WriteAuditRow auditWs, auditRow, rowNumber, "OutputName", "ERROR", _
            "Filename cannot end with a dot or space.", errorCount, warningCount
    End If
    If LCase$(Right$(fileStem, 5)) = ".docx" Or _
       LCase$(Right$(fileStem, 4)) = ".pdf" Then
        WriteAuditRow auditWs, auditRow, rowNumber, "OutputName", "ERROR", _
            "Store the output stem without .docx or .pdf.", _
            errorCount, warningCount
    End If
    For Each item In forbidden
        If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "OutputName", "ERROR", _
                "Filename contains a forbidden character: " & item, _
                errorCount, warningCount
        End If
    Next item
    For position = 1 To Len(fileStem)
        characterCode = AscW(Mid$(fileStem, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "OutputName", "ERROR", _
                "Filename contains a Windows control character.", _
                errorCount, warningCount
            Exit For
        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
        WriteAuditRow auditWs, auditRow, rowNumber, "OutputName", "ERROR", _
            "Filename starts with a reserved Windows device name.", _
            errorCount, warningCount
    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 ValidatePhotoFileName(ByVal fileName As String, _
                                  ByVal rowNumber As Long, _
                                  ByVal auditWs As Worksheet, _
                                  ByRef auditRow As Long, _
                                  ByRef errorCount As Long, _
                                  ByRef warningCount 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 Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Photo filename cannot end with a dot or space.", _
            errorCount, warningCount
    End If
    For Each item In forbidden
        If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
                "Photo filename contains a forbidden character: " & item, _
                errorCount, warningCount
        End If
    Next item
    For position = 1 To Len(fileName)
        characterCode = AscW(Mid$(fileName, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
                "Photo filename contains a Windows control character.", _
                errorCount, warningCount
            Exit For
        End If
    Next position
    baseName = fileName
    If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
        baseName = Left$(baseName, InStr(1, baseName, ".", vbBinaryCompare) - 1)
    End If
    If Len(baseName) = 0 Or IsReservedWindowsName(UCase$(baseName)) Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Photo filename has an empty or reserved Windows base name.", _
            errorCount, warningCount
    End If
End Sub

Private Function ResolvePhotoPath(ByVal workbookPath As String, _
                                  ByVal photoFile As String, _
                                  ByVal rowNumber As Long, _
                                  ByVal auditWs As Worksheet, _
                                  ByRef auditRow As Long, _
                                  ByRef errorCount As Long, _
                                  ByRef warningCount As Long) As String
    Dim errorsBefore As Long
    errorsBefore = errorCount
    ValidatePhotoFileName photoFile, rowNumber, auditWs, auditRow, _
        errorCount, warningCount
    If errorCount > errorsBefore Then Exit Function
    If InStr(photoFile, "..") > 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Parent-path syntax is not allowed.", errorCount, warningCount
        Exit Function
    End If
    ResolvePhotoPath = workbookPath & PHOTO_FOLDER & "\" & photoFile
End Function

Private Function InspectPhotoFile(ByVal filePath As String, _
                                  ByVal rowNumber As Long, _
                                  ByVal auditWs As Worksheet, _
                                  ByRef auditRow As Long, _
                                  ByRef errorCount As Long, _
                                  ByRef warningCount As Long) As Double
    Dim extension As String
    Dim dotPosition As Long
    dotPosition = InStrRev(filePath, ".")
    If dotPosition = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Photo filename has no extension.", errorCount, warningCount
        Exit Function
    End If
    extension = LCase$(Mid$(filePath, dotPosition + 1))
    If extension <> "jpg" And extension <> "jpeg" And extension <> "png" Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Use JPG, JPEG, or PNG only.", errorCount, warningCount
        Exit Function
    End If
    If Len(Dir$(filePath, vbNormal Or vbReadOnly Or vbHidden Or vbSystem)) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Prepared photo is missing: " & filePath, errorCount, warningCount
        Exit Function
    End If
    InspectPhotoFile = CDbl(FileLen(filePath))
    If InspectPhotoFile <= 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PhotoFile", "ERROR", _
            "Prepared photo exists but is empty.", errorCount, warningCount
    ElseIf InspectPhotoFile > MAX_PHOTO_BYTES Then
        WriteAuditRow auditWs, auditRow, rowNumber, "PHOTO BYTES", "ERROR", _
            "Photo exceeds the example per-photo budget: " & _
            FormatBytes(InspectPhotoFile) & ".", errorCount, warningCount
    Else
        WriteAuditRow auditWs, auditRow, rowNumber, "PHOTO BYTES", "PASS", _
            "Prepared photo size: " & FormatBytes(InspectPhotoFile) & ".", _
            errorCount, warningCount
    End If
End Function

Private Sub AddDocumentValue(ByVal dictionary As Object, _
                             ByVal documentId As String, _
                             ByVal valueToAdd As Double)
    If dictionary.Exists(documentId) Then
        dictionary(documentId) = CDbl(dictionary(documentId)) + valueToAdd
    Else
        dictionary.Add documentId, valueToAdd
    End If
End Sub

Private Sub CheckDocumentContract(ByVal dictionary As Object, _
                                  ByVal documentId As String, _
                                  ByVal valueText As String, _
                                  ByVal fieldName As String, _
                                  ByVal rowNumber As Long, _
                                  ByVal auditWs As Worksheet, _
                                  ByRef auditRow As Long, _
                                  ByRef errorCount As Long, _
                                  ByRef warningCount As Long)
    If dictionary.Exists(documentId) Then
        If StrComp(CStr(dictionary(documentId)), valueText, _
                   vbBinaryCompare) <> 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
                "All photo rows for one DocumentID must use the same value.", _
                errorCount, warningCount
        End If
    Else
        dictionary.Add documentId, valueText
    End If
End Sub

Private Function FormatBytes(ByVal byteCount As Double) As String
    If byteCount >= 1048576# Then
        FormatBytes = Format$(byteCount / 1048576#, "0.00") & " MB"
    Else
        FormatBytes = Format$(byteCount / 1024#, "0.0") & " KB"
    End If
End Function

Private Function UniqueAuditSheetName(ByVal wb As Workbook) As String
    Dim baseName As String
    Dim candidate As String
    Dim counter As Long
    baseName = AUDIT_PREFIX & Format$(Now, "yyyymmdd_hhnnss")
    candidate = Left$(baseName, 31)
    counter = 1
    Do While WorksheetExists(wb, candidate)
        counter = counter + 1
        candidate = Left$(baseName, 27) & "_" & counter
    Loop
    UniqueAuditSheetName = candidate
End Function

Private Function WorksheetExists(ByVal wb As Workbook, _
                                 ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    WorksheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

Private Sub WriteAuditHeaders(ByVal ws As Worksheet)
    ws.Cells(1, 1).Value = "SourceRow"
    ws.Cells(1, 2).Value = "DocumentID"
    ws.Cells(1, 3).Value = "Check"
    ws.Cells(1, 4).Value = "Result"
    ws.Cells(1, 5).Value = "Details"
    ws.Cells(1, 6).Value = "PhotoBytes"
    ws.Cells(1, 7).Value = "CheckedAt"
End Sub

Private Sub WriteAuditRow(ByVal auditWs As Worksheet, _
                          ByRef auditRow As Long, _
                          ByVal sourceRow As Long, _
                          ByVal checkName As String, _
                          ByVal resultText As String, _
                          ByVal details As String, _
                          ByRef errorCount As Long, _
                          ByRef warningCount As Long)
    auditWs.Cells(auditRow, 1).Value = sourceRow
    If sourceRow >= FIRST_DATA_ROW Then
        auditWs.Cells(auditRow, 2).Value = _
            ThisWorkbook.Worksheets(MANIFEST_SHEET).Cells( _
                sourceRow, COL_DOCUMENT_ID).Value2
    End If
    auditWs.Cells(auditRow, 3).Value = checkName
    auditWs.Cells(auditRow, 4).Value = resultText
    auditWs.Cells(auditRow, 5).Value = details
    auditWs.Cells(auditRow, 7).Value = Now
    If resultText = "ERROR" Then errorCount = errorCount + 1
    If resultText = "WARNING" Then warningCount = warningCount + 1
    auditRow = auditRow + 1
End Sub

Private Sub WriteDocumentSummary(ByVal ws As Worksheet, _
                                 ByRef auditRow As Long, _
                                 ByVal documentId As String, _
                                 ByVal photoCount As Long, _
                                 ByVal totalBytes As Double, _
                                 ByVal resultText As String, _
                                 ByVal details As String, _
                                 ByRef errorCount As Long, _
                                 ByRef warningCount As Long)
    ws.Cells(auditRow, 1).Value = "DOCUMENT"
    ws.Cells(auditRow, 2).Value = documentId
    ws.Cells(auditRow, 3).Value = "DOCUMENT RELEASE"
    ws.Cells(auditRow, 4).Value = resultText
    ws.Cells(auditRow, 5).Value = details & " Photos: " & photoCount & "."
    ws.Cells(auditRow, 6).Value = totalBytes
    ws.Cells(auditRow, 7).Value = Now
    If resultText = "ERROR" Then errorCount = errorCount + 1
    If resultText = "WARNING" Then warningCount = warningCount + 1
    auditRow = auditRow + 1
End Sub

Private Sub WriteSummary(ByVal ws As Worksheet, _
                         ByVal readyPhotoCount As Long, _
                         ByVal holdPhotoCount As Long, _
                         ByVal errorCount As Long, _
                         ByVal warningCount As Long)
    ws.Range("I1").Value = "SUMMARY"
    ws.Range("I2").Value = "READY photo rows"
    ws.Range("J2").Value = readyPhotoCount
    ws.Range("I3").Value = "HOLD photo rows"
    ws.Range("J3").Value = holdPhotoCount
    ws.Range("I4").Value = "Errors"
    ws.Range("J4").Value = errorCount
    ws.Range("I5").Value = "Warnings"
    ws.Range("J5").Value = warningCount
    ws.Range("I7").Value = "Per-photo example budget"
    ws.Range("J7").Value = MAX_PHOTO_BYTES
    ws.Range("I8").Value = "Per-document example budget"
    ws.Range("J8").Value = MAX_DOCUMENT_PHOTO_BYTES
    ws.Range("I10").Value = "Scope"
    ws.Range("J10").Value = _
        "Checks bytes and contract only; does not inspect pixels or image content."
    ws.Columns("I:J").AutoFit
End Sub

Interpret the audit without confusing size with quality

Photo over budget

Return to the approved working copy and review pixels, format, quality, and intended display size.

Document over budget

Review the total number of photos and combined bytes. Do not reduce every image blindly.

PreparedForUse = NO

Keep the row on HOLD. A smaller file is not automatically an approved asset.

Duplicate pair

Confirm whether repetition is intentional. Remove accidental duplicates before document generation.

PASS

The declared byte contract passed. Pixel dimensions, content, metadata, fidelity, and authenticity were not inspected.

Clean pilot

Generate representative DOCX and PDF files, compare their sizes, and inspect photos visually at the delivery target.

Run a six-output size and quality pilot

1. One typical photo

Establish the baseline DOCX/PDF size and visual result.

2. Maximum photo count

Test the largest permitted document payload.

3. Portrait and landscape

Verify image fit, aspect ratio, captions, and page breaks.

4. JPEG and PNG

Use representative content rather than assuming one format always wins.

5. Screen export

Check electronic delivery quality and the resulting bytes.

6. Print export

Compare detail, print behavior, and file size with the screen target.

Record the decision

Keep the approved staging rules, byte limits, Word image settings, PDF target, and sample outputs together. A future template change should rerun the pilot.

Use prepared photos in the correct DocxForge Pro workflow

DocxForge Pro uses the document workflow you configure; prepare and approve photo copies before they enter either module.

Contractor

One case creates several different documents from different Word templates. Use the same approved case assets only where each template requires them.

Factory

Many independent Excel rows use one standard Word template and create one separate output per row.

DocxForge Pro Contractor case data using approved prepared photos
Contractor: review one case and its approved assets.
DocxForge Pro Contractor document set created from different Word templates
Contractor: confirm the different documents for the case.
DocxForge Pro Factory Excel rows using prepared photo filenames
Factory: review independent rows and photo references.
DocxForge Pro Factory document settings with one standard Word template
Factory: confirm one standard template and output settings.
Generate Word and PDF files from controlled data and prepared photo copiesTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Contractor + FactoryPrepared photosLocal processingDOCX + PDF
Start Free 7-Day Trial

Frequently asked questions

Why can a small-looking photo still create a large PDF?

Changing a picture's displayed width in Word does not necessarily remove the original image data. A high-resolution source can remain embedded behind a small layout box and increase both DOCX and PDF size.

Does OptimizeForOnScreen compress every embedded photo?

No. It asks Word to optimize PDF export for on-screen viewing, but it is not a guaranteed replacement for preparing source images or using Word's picture-compression controls.

Should I overwrite the original evidence photos?

No. Preserve originals according to your retention policy and create approved working copies for document generation. Resizing, cropping, or stripping metadata can be irreversible.

What photo-size budget should I use?

There is no universal number. Choose limits from your required print quality, page dimensions, photo count, delivery channel, and retention rules. The VBA values are examples that must be adjusted.

Can the VBA helper prove that a JPG or PNG is valid?

No. It checks the filename, extension, existence, and byte size. It does not decode pixels, confirm the real file signature, assess visual quality, or authenticate the image.

Which DocxForge Pro module fits this workflow?

Factory fits many rows through one standard template. Contractor fits one case that produces several different documents from different templates. Both should receive prepared photo copies rather than unrestricted originals.

Keep photo-heavy document generation repeatable after the assets are preparedRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Controlled inputTwo workflow modulesLocal generationReviewable output
Start Free 7-Day Trial