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




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.