How to Generate 1000+ Word Documents from One Excel File
Generate more than 1000 documents by treating the work as a controlled series of resumable batches. Preflight every READY row, prevent output collisions, use one fresh template instance per record, checkpoint progress, and review both DOCX and PDF.
Quick answer: scale the process, not the risk
Use one Excel table where every READY row is an independent record, one approved Word template, and one deterministic output name per row. Validate the complete source first, then generate bounded batches that record progress and can be resumed safely.
What scales
The same field contract, template, naming rule, image rule, and validation sequence can repeat across thousands of rows.
What should stay bounded
Each desktop run should have a clear limit, checkpoints, a stop-on-error rule, and an operator review before the next group begins.
6-record pilot → 50-record verification run → controlled batches of 100–250 → reconciliation of row statuses and output counts.
Plan the 1000-document workload before opening Word
Large batches fail expensively when validation, naming, and recovery are deferred until generation.
Count eligible rows
Define eligibility explicitly: Status = READY and GenerationStatus ≠ DONE. Do not infer approval from a nonblank row.
Estimate output volume
DOCX plus PDF doubles the file count. One thousand eligible rows can produce two thousand final files plus temporary files during processing.
Reserve storage
Measure representative DOCX, PDF, and image sizes. Confirm free local disk space and avoid generating directly into a synchronized or shared folder.
Use one writer
Run one workbook and one Word automation process against the output folder. Parallel writers introduce naming races and ambiguous ownership.
Choose checkpoint frequency
Saving too rarely risks lost status updates; saving every row adds overhead. The example checkpoints every 25 completed documents.
Define stop and resume
Stop on the first invalid row or Word error. Review the current record and folder, then resume only after reconciling outputs with Excel status.
Use a source table designed for batch recovery
The example locates headers by name. Column order may vary, but the meanings must remain stable.
Unique identity field
DocumentID is the stable record key and maps to the matching Word bookmark. Store it as text before import when leading zeros must be preserved; duplicate READY identifiers stop preflight.
Document values
ClientName, DocumentDateDisplay, and AmountDisplay feed the Word text bookmarks.
Display fields are text
The two Display columns must contain final document-ready strings, not numeric cells whose Excel formatting merely looks correct.
Optional image
ImageFile contains only a file name from Assets\. Blank removes the image placeholder for that row.
Explicit output name
OutputName contains the desired base name without .docx or .pdf. The macro cleans and validates it before use.
Release and progress fields
Status controls approval. GenerationStatus and GeneratedAt record completed work for resumption and reconciliation.
DocumentID | ClientName | DocumentDateDisplay | AmountDisplay | ImageFile | OutputName | Status | GenerationStatus | GeneratedAt
Keep one standard Word template deliberately narrow
For this Factory-shaped workload, every row uses Templates\StandardBatchTemplate.docx.
Four uniform text bookmarks
Create DocumentID, ClientName, DocumentDateDisplay, and AmountDisplay. Keep each bookmark inside one styled text range without paragraph or cell marks.
One inline image bookmark
Create RecordImage around one inline placeholder image or token. Floating shapes require a different insertion routine.
No nested dynamic structures
The example rejects mapped bookmarks containing Word fields or content controls. Split or redesign those destinations before the batch.
Test maximum content
Approve the longest name, largest formatted amount, date format, blank image, wide image, tall image, tables, and final PDF pagination.
Microsoft references: Bookmarks.Exists and Bookmarks.Add.
Preflight every eligible row before generating the first file
The macro scans the full eligible workload, confirms that Excel can record progress, and caches the validated values for the first 250 rows before Word opens.
Required replacement values
Every eligible row needs a unique ID, client name, two display strings, and an output base name. Tabs, line breaks, and page breaks are rejected before Word opens.
Exact display types
Numeric dates and amounts are rejected in the display columns so Word does not receive serials or unformatted values.
Image availability
Every nonblank image file must exist, be nonempty, and remain inside the controlled local Assets folder.
Cleaned-name uniqueness
Duplicate detection happens after forbidden characters and reserved Windows names are handled.
Existing outputs
Any existing final DOCX or PDF blocks the run. The example never treats overwrite as a normal batch operation.
Writable progress log
A read-only workbook or protected Data sheet stops the run. The workbook is saved once before Word opens to confirm that progress can be recorded.
Free VBA example: controlled, resumable document batches
Save a writable copy as an Excel Macro-Enabled Workbook (.xlsm) and leave the Data sheet unprotected. Create the nine headers, then place Templates\StandardBatchTemplate.docx, Assets\, and an empty GeneratedDocuments\ folder beside the workbook. Add this code to a standard Excel VBA module.
Full preflight, bounded output
All eligible rows are validated before Word opens. The checked values for the first 250 are cached and used for the current run instead of being read again without validation.
Fresh template per row
Word remains open for the run, while each record starts from a new read-only instance of the same approved template.
Temporary-file promotion
DOCX and PDF are created under unique temporary names, verified, and moved only after a second final-name collision check.
Checkpoint and resume
Completed rows receive DONE and a timestamp. The workbook saves every 25 documents and once more at normal completion or error cleanup.
Option Explicit
Private Const DATA_SHEET As String = "Data"
Private Const TEMPLATE_RELATIVE_PATH As String = "\Templates\StandardBatchTemplate.docx"
Private Const OUTPUT_RELATIVE_FOLDER As String = "\GeneratedDocuments"
Private Const ASSET_RELATIVE_FOLDER As String = "\Assets"
Private Const FIRST_DATA_ROW As Long = 2
Private Const BATCH_LIMIT As Long = 250
Private Const CHECKPOINT_EVERY As Long = 25
Private Const EXPORT_PDF As Boolean = True
Private Const HEADER_DOCUMENT_ID As String = "DocumentID"
Private Const HEADER_CLIENT_NAME As String = "ClientName"
Private Const HEADER_DATE_DISPLAY As String = "DocumentDateDisplay"
Private Const HEADER_AMOUNT_DISPLAY As String = "AmountDisplay"
Private Const HEADER_IMAGE_FILE As String = "ImageFile"
Private Const HEADER_OUTPUT_NAME As String = "OutputName"
Private Const HEADER_STATUS As String = "Status"
Private Const HEADER_GENERATION_STATUS As String = "GenerationStatus"
Private Const HEADER_GENERATED_AT As String = "GeneratedAt"
Private Const READY_STATUS As String = "READY"
Private Const DONE_STATUS As String = "DONE"
Private Const BOOKMARK_DOCUMENT_ID As String = "DocumentID"
Private Const BOOKMARK_CLIENT_NAME As String = "ClientName"
Private Const BOOKMARK_DATE_DISPLAY As String = "DocumentDateDisplay"
Private Const BOOKMARK_AMOUNT_DISPLAY As String = "AmountDisplay"
Private Const BOOKMARK_IMAGE As String = "RecordImage"
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_COLLAPSE_START As Long = 1
Private Const WD_UNDEFINED As Long = 9999999
Private Const MSO_TRUE As Long = -1
Private Const WORD_ALERTS_NONE As Long = 0
Public Sub GenerateControlledDocumentBatch()
Dim wb As Workbook
Dim ws As Worksheet
Dim documentIdColumn As Long
Dim clientNameColumn As Long
Dim dateDisplayColumn As Long
Dim amountDisplayColumn As Long
Dim imageFileColumn As Long
Dim outputNameColumn As Long
Dim statusColumn As Long
Dim generationStatusColumn As Long
Dim generatedAtColumn As Long
Dim lastRow As Long
Dim rowNumber As Long
Dim sourceRow As Long
Dim batchRows(1 To BATCH_LIMIT) As Long
Dim batchDocumentIds(1 To BATCH_LIMIT) As String
Dim batchClientNames(1 To BATCH_LIMIT) As String
Dim batchDateDisplays(1 To BATCH_LIMIT) As String
Dim batchAmountDisplays(1 To BATCH_LIMIT) As String
Dim batchImagePaths(1 To BATCH_LIMIT) As String
Dim batchSafeOutputNames(1 To BATCH_LIMIT) As String
Dim batchCount As Long
Dim eligibleCount As Long
Dim completedCount As Long
Dim documentId As String
Dim clientName As String
Dim dateDisplay As String
Dim amountDisplay As String
Dim imageFile As String
Dim imagePath As String
Dim outputName As String
Dim safeOutputName As String
Dim statusText As String
Dim generationStatusText As String
Dim templatePath As String
Dim outputFolder As String
Dim assetFolder As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim runToken As String
Dim seenDocumentIds As Object
Dim seenOutputNames As Object
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
Dim errorNumber As Long
Dim errorMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 2500, , _
"Save the workbook before running the batch."
End If
If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 2501, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 2502, , _
"The workbook is read-only. Open a writable copy before generating files."
End If
Set ws = RequireWorksheet(wb, DATA_SHEET)
If ws.ProtectContents Then
Err.Raise vbObjectError + 2503, , _
"The Data sheet is protected. Unprotect it before generating files."
End If
documentIdColumn = RequireHeaderColumn(ws, HEADER_DOCUMENT_ID)
clientNameColumn = RequireHeaderColumn(ws, HEADER_CLIENT_NAME)
dateDisplayColumn = RequireHeaderColumn(ws, HEADER_DATE_DISPLAY)
amountDisplayColumn = RequireHeaderColumn(ws, HEADER_AMOUNT_DISPLAY)
imageFileColumn = RequireHeaderColumn(ws, HEADER_IMAGE_FILE)
outputNameColumn = RequireHeaderColumn(ws, HEADER_OUTPUT_NAME)
statusColumn = RequireHeaderColumn(ws, HEADER_STATUS)
generationStatusColumn = RequireHeaderColumn(ws, HEADER_GENERATION_STATUS)
generatedAtColumn = RequireHeaderColumn(ws, HEADER_GENERATED_AT)
templatePath = wb.Path & TEMPLATE_RELATIVE_PATH
outputFolder = wb.Path & OUTPUT_RELATIVE_FOLDER
assetFolder = wb.Path & ASSET_RELATIVE_FOLDER
RequireFile templatePath, "Word template"
RequireFolder outputFolder, "output folder"
RequireFolder assetFolder, "asset folder"
lastRow = LastUsedRowInColumns(ws, Array( _
documentIdColumn, clientNameColumn, dateDisplayColumn, _
amountDisplayColumn, imageFileColumn, outputNameColumn, _
statusColumn, generationStatusColumn, generatedAtColumn))
If lastRow < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 2504, , _
"No data rows were found on the " & DATA_SHEET & " sheet."
End If
Set seenDocumentIds = CreateObject("Scripting.Dictionary")
seenDocumentIds.CompareMode = vbTextCompare
Set seenOutputNames = CreateObject("Scripting.Dictionary")
seenOutputNames.CompareMode = vbTextCompare
For rowNumber = FIRST_DATA_ROW To lastRow
statusText = UCase$(Trim$(CStr(ws.Cells(rowNumber, statusColumn).Value2)))
generationStatusText = UCase$(Trim$(CStr(ws.Cells(rowNumber, generationStatusColumn).Value2)))
If statusText = READY_STATUS And generationStatusText <> DONE_STATUS Then
documentId = RequireCellText(ws, rowNumber, documentIdColumn, HEADER_DOCUMENT_ID)
clientName = RequireCellText(ws, rowNumber, clientNameColumn, HEADER_CLIENT_NAME)
dateDisplay = RequireDisplayText(ws, rowNumber, dateDisplayColumn, HEADER_DATE_DISPLAY)
amountDisplay = RequireDisplayText(ws, rowNumber, amountDisplayColumn, HEADER_AMOUNT_DISPLAY)
imageFile = ReadCellText(ws, rowNumber, imageFileColumn, HEADER_IMAGE_FILE)
outputName = RequireCellText(ws, rowNumber, outputNameColumn, HEADER_OUTPUT_NAME)
ValidateReplacementText documentId, BOOKMARK_DOCUMENT_ID
ValidateReplacementText clientName, BOOKMARK_CLIENT_NAME
ValidateReplacementText dateDisplay, BOOKMARK_DATE_DISPLAY
ValidateReplacementText amountDisplay, BOOKMARK_AMOUNT_DISPLAY
If seenDocumentIds.Exists(documentId) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The identifier duplicates READY row " & _
seenDocumentIds(documentId) & "."
End If
seenDocumentIds.Add documentId, rowNumber
RejectOutputExtension outputName, rowNumber
safeOutputName = CleanFileName(outputName)
If Len(safeOutputName) = 0 Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The value does not contain a usable Windows file name."
End If
If seenOutputNames.Exists(safeOutputName) Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The cleaned name duplicates READY row " & _
seenOutputNames(safeOutputName) & "."
End If
seenOutputNames.Add safeOutputName, rowNumber
finalDocxPath = outputFolder & "\" & safeOutputName & ".docx"
finalPdfPath = outputFolder & "\" & safeOutputName & ".pdf"
If Len(finalDocxPath) > 218 Or Len(finalPdfPath) > 218 Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The output path is too long for this example workflow."
End If
If FileExists(finalDocxPath) Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The DOCX output already exists: " & finalDocxPath
End If
If EXPORT_PDF And FileExists(finalPdfPath) Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"The PDF output already exists: " & finalPdfPath
End If
If Len(imageFile) > 0 Then
imagePath = ResolveAssetFile(assetFolder, imageFile, rowNumber)
RequireFile imagePath, "image for row " & rowNumber
Else
imagePath = vbNullString
End If
eligibleCount = eligibleCount + 1
If batchCount < BATCH_LIMIT Then
batchCount = batchCount + 1
batchRows(batchCount) = rowNumber
batchDocumentIds(batchCount) = documentId
batchClientNames(batchCount) = clientName
batchDateDisplays(batchCount) = dateDisplay
batchAmountDisplays(batchCount) = amountDisplay
batchImagePaths(batchCount) = imagePath
batchSafeOutputNames(batchCount) = safeOutputName
End If
End If
Next rowNumber
If eligibleCount = 0 Then
MsgBox "No rows are eligible. Use Status = " & READY_STATUS & _
" and leave GenerationStatus different from " & DONE_STATUS & ".", _
vbInformation, "Nothing to generate"
Exit Sub
End If
wb.Save
Set fso = CreateObject("Scripting.FileSystemObject")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = WORD_ALERTS_NONE
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
ValidateTemplate doc
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 1000), "00000000")
For rowNumber = 1 To batchCount
sourceRow = batchRows(rowNumber)
documentId = batchDocumentIds(rowNumber)
clientName = batchClientNames(rowNumber)
dateDisplay = batchDateDisplays(rowNumber)
amountDisplay = batchAmountDisplays(rowNumber)
imagePath = batchImagePaths(rowNumber)
safeOutputName = batchSafeOutputNames(rowNumber)
finalDocxPath = outputFolder & "\" & safeOutputName & ".docx"
finalPdfPath = outputFolder & "\" & safeOutputName & ".pdf"
tempDocxPath = outputFolder & "\.__batch_" & runToken & _
"_" & Format$(sourceRow, "000000") & ".docx"
tempPdfPath = outputFolder & "\.__batch_" & runToken & _
"_" & Format$(sourceRow, "000000") & ".pdf"
If FileExists(tempDocxPath) Or FileExists(tempPdfPath) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"A temporary file collision was detected. Run the batch again."
End If
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
WriteTextBookmark doc, BOOKMARK_DOCUMENT_ID, documentId
WriteTextBookmark doc, BOOKMARK_CLIENT_NAME, clientName
WriteTextBookmark doc, BOOKMARK_DATE_DISPLAY, dateDisplay
WriteTextBookmark doc, BOOKMARK_AMOUNT_DISPLAY, amountDisplay
WritePictureBookmark doc, BOOKMARK_IMAGE, imagePath, True
doc.SaveAs2 tempDocxPath, WD_FORMAT_XML_DOCUMENT, False, vbNullString, False
If Not FileExists(tempDocxPath) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"Word did not create the temporary DOCX file."
End If
If EXPORT_PDF Then
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_FORMAT_PDF
If Not FileExists(tempPdfPath) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"Word did not create the temporary PDF file."
End If
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If FileExists(finalDocxPath) Or _
(EXPORT_PDF And FileExists(finalPdfPath)) Then
RaiseRowError sourceRow, HEADER_OUTPUT_NAME, _
"An output appeared after preflight. No existing file was replaced."
End If
fso.MoveFile tempDocxPath, finalDocxPath
tempDocxPath = vbNullString
If EXPORT_PDF Then
fso.MoveFile tempPdfPath, finalPdfPath
tempPdfPath = vbNullString
End If
ws.Cells(sourceRow, generationStatusColumn).Value2 = DONE_STATUS
ws.Cells(sourceRow, generatedAtColumn).Value = Now
completedCount = completedCount + 1
If completedCount Mod CHECKPOINT_EVERY = 0 Then wb.Save
Next rowNumber
wb.Save
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
MsgBox completedCount & " documents completed in this run." & vbCrLf & _
CStr(eligibleCount - completedCount) & _
" eligible rows remain for the next controlled batch.", _
vbInformation, "Batch complete"
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.Quit WD_DO_NOT_SAVE_CHANGES
SafeDeleteTempFile tempDocxPath
SafeDeleteTempFile tempPdfPath
If Not wb Is Nothing Then wb.Save
On Error GoTo 0
MsgBox completedCount & " documents were completed before the stop." & vbCrLf & _
"Review the current row and output folder before resuming." & vbCrLf & _
"Error " & errorNumber & ": " & errorMessage, _
vbCritical, "Batch stopped"
End Sub
Private Sub ValidateTemplate(ByVal doc As Object)
ValidateTextBookmark doc, BOOKMARK_DOCUMENT_ID
ValidateTextBookmark doc, BOOKMARK_CLIENT_NAME
ValidateTextBookmark doc, BOOKMARK_DATE_DISPLAY
ValidateTextBookmark doc, BOOKMARK_AMOUNT_DISPLAY
ValidatePictureBookmark doc, BOOKMARK_IMAGE
End Sub
Private Sub ValidateTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2510, , _
"Required Word bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.Start = targetRange.End Then
Err.Raise vbObjectError + 2511, , _
"Text bookmark must contain a styled placeholder: " & bookmarkName
End If
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2512, , _
"Text bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2513, , _
"Text bookmark contains a Word field or content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 2519, , _
"Text bookmark contains an inline shape: " & bookmarkName
End If
If HasMixedCharacterFormatting(targetRange) Then
Err.Raise vbObjectError + 2514, , _
"Text bookmark uses mixed character formatting: " & bookmarkName
End If
End Sub
Private Sub ValidatePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2515, , _
"Required picture bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2516, , _
"Picture bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2517, , _
"Picture bookmark contains a Word field or content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 1 Then
Err.Raise vbObjectError + 2518, , _
"Picture bookmark may contain one inline placeholder image at most: " & bookmarkName
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim savedFont As Object
Dim savedStyle As Variant
ValidateReplacementText replacementText, bookmarkName
ValidateTextBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
Set savedFont = targetRange.Font.Duplicate
savedStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = savedStyle
targetRange.Font = savedFont
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2520, , _
"Bookmark was not restored: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.Text <> replacementText Then
Err.Raise vbObjectError + 2521, , _
"Bookmark text did not verify: " & bookmarkName
End If
End Sub
Private Sub ValidateReplacementText(ByVal replacementText As String, _
ByVal bookmarkName As String)
If InStr(1, replacementText, vbTab, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbLf, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(11), vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(12), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 2522, , _
"Single-line replacement contains a tab, line break, or page break: " & _
bookmarkName
End If
End Sub
Private Sub WritePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal picturePath As String, _
ByVal allowBlank As Boolean)
Dim targetRange As Object
Dim picture As Object
Dim startPosition As Long
Dim maxWidth As Single
Dim maxHeight As Single
If Len(picturePath) = 0 And Not allowBlank Then
Err.Raise vbObjectError + 2530, , _
"A required picture is blank: " & bookmarkName
End If
ValidatePictureBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.InlineShapes.Count = 1 Then
maxWidth = targetRange.InlineShapes(1).Width
maxHeight = targetRange.InlineShapes(1).Height
Else
maxWidth = 216!
maxHeight = 144!
End If
startPosition = targetRange.Start
targetRange.Delete
targetRange.SetRange startPosition, startPosition
targetRange.Collapse WD_COLLAPSE_START
If Len(picturePath) = 0 Then
doc.Bookmarks.Add bookmarkName, targetRange
Exit Sub
End If
RequireFile picturePath, "picture for bookmark " & bookmarkName
Set picture = doc.InlineShapes.AddPicture( _
picturePath, False, True, targetRange)
picture.LockAspectRatio = MSO_TRUE
If picture.Width > maxWidth Then picture.Width = maxWidth
If picture.Height > maxHeight Then picture.Height = maxHeight
doc.Bookmarks.Add bookmarkName, picture.Range
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2531, , _
"Picture bookmark was not restored: " & bookmarkName
End If
End Sub
Private Function HasMixedCharacterFormatting(ByVal targetRange As Object) As Boolean
With targetRange.Font
HasMixedCharacterFormatting = _
(.Bold = WD_UNDEFINED) Or _
(.Italic = WD_UNDEFINED) Or _
(.Underline = WD_UNDEFINED) Or _
(.Size = WD_UNDEFINED) Or _
(.Color = WD_UNDEFINED)
End With
End Function
Private Function RangeContainsParagraphOrCellMark(ByVal targetRange As Object) As Boolean
Dim rangeText As String
rangeText = targetRange.Text
RangeContainsParagraphOrCellMark = _
(InStr(1, rangeText, vbCr, vbBinaryCompare) > 0) Or _
(InStr(1, rangeText, Chr$(7), vbBinaryCompare) > 0)
End Function
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal worksheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(worksheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 2540, , _
"Required worksheet is missing: " & worksheetName
End If
End Function
Private Function RequireHeaderColumn(ByVal ws As Worksheet, _
ByVal headerText As String) As Long
Dim lastHeaderColumn As Long
Dim columnNumber As Long
Dim matchCount As Long
lastHeaderColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
For columnNumber = 1 To lastHeaderColumn
If StrComp(Trim$(CStr(ws.Cells(1, columnNumber).Value2)), _
headerText, vbTextCompare) = 0 Then
RequireHeaderColumn = columnNumber
matchCount = matchCount + 1
End If
Next columnNumber
If matchCount = 0 Then
Err.Raise vbObjectError + 2541, , _
"Required header missing from row 1: " & headerText
ElseIf matchCount > 1 Then
Err.Raise vbObjectError + 2542, , _
"Duplicate header in row 1: " & headerText
End If
End Function
Private Function LastUsedRowInColumns(ByVal ws As Worksheet, _
ByVal columnsToCheck As Variant) As Long
Dim item As Variant
Dim candidateRow As Long
For Each item In columnsToCheck
candidateRow = ws.Cells(ws.Rows.Count, CLng(item)).End(xlUp).Row
If candidateRow > LastUsedRowInColumns Then
LastUsedRowInColumns = candidateRow
End If
Next item
End Function
Private Function ReadCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
ReadCellText = Trim$(CStr(cellValue))
End Function
Private Function RequireCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
RequireCellText = ReadCellText(ws, rowNumber, columnNumber, headerText)
If Len(RequireCellText) = 0 Then
RaiseRowError rowNumber, headerText, "A value is required for READY rows."
End If
End Function
Private Function RequireDisplayText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
If VarType(cellValue) <> vbString Then
RaiseRowError rowNumber, headerText, _
"Store the final document-ready value as text, not a formatted number or date."
End If
RequireDisplayText = Trim$(CStr(cellValue))
If Len(RequireDisplayText) = 0 Then
RaiseRowError rowNumber, headerText, "A display value is required."
End If
End Function
Private Function ResolveAssetFile(ByVal assetFolder As String, _
ByVal fileNameOnly As String, _
ByVal rowNumber As Long) As String
Dim cleanedName As String
cleanedName = Trim$(fileNameOnly)
If InStr(cleanedName, "\") > 0 Or InStr(cleanedName, "/") > 0 Or _
InStr(cleanedName, ":") > 0 Or InStr(cleanedName, "*") > 0 Or _
InStr(cleanedName, "?") > 0 Or InStr(cleanedName, "<") > 0 Or _
InStr(cleanedName, ">") > 0 Or InStr(cleanedName, "|") > 0 Or _
InStr(cleanedName, Chr$(34)) > 0 Or cleanedName = "." Or cleanedName = ".." Then
RaiseRowError rowNumber, HEADER_IMAGE_FILE, _
"Use a file name only. Store the image inside the Assets folder."
End If
ResolveAssetFile = assetFolder & "\" & cleanedName
End Function
Private Sub RejectOutputExtension(ByVal outputName As String, _
ByVal rowNumber As Long)
Dim lowerName As String
lowerName = LCase$(Trim$(outputName))
If Right$(lowerName, 5) = ".docx" Or Right$(lowerName, 4) = ".pdf" Then
RaiseRowError rowNumber, HEADER_OUTPUT_NAME, _
"Enter the output name without a .docx or .pdf extension."
End If
End Sub
Private Function CleanFileName(ByVal rawName As String) As String
Dim result As String
Dim invalidCharacters As Variant
Dim item As Variant
Dim characterCode As Long
Dim position As Long
result = Trim$(rawName)
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", "\", "|", "?", "*")
For Each item In invalidCharacters
result = Replace(result, CStr(item), "_")
Next item
For position = 1 To Len(result)
characterCode = AscW(Mid$(result, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Mid$(result, position, 1) = "_"
End If
Next position
result = Left$(result, 120)
Do While Len(result) > 0 And _
(Right$(result, 1) = "." Or Right$(result, 1) = " ")
result = Left$(result, Len(result) - 1)
Loop
If IsReservedWindowsName(result) Then result = "_" & result
CleanFileName = result
End Function
Private Function IsReservedWindowsName(ByVal fileName As String) As Boolean
Dim baseName As String
Dim dotPosition As Long
Dim numberValue As Long
baseName = UCase$(fileName)
dotPosition = InStr(1, baseName, ".", vbBinaryCompare)
If dotPosition > 0 Then baseName = Left$(baseName, dotPosition - 1)
Select Case baseName
Case "CON", "PRN", "AUX", "NUL"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(baseName, 3) = "COM" Or Left$(baseName, 3) = "LPT" Then
If Len(baseName) = 4 And IsNumeric(Right$(baseName, 1)) Then
numberValue = CLng(Right$(baseName, 1))
IsReservedWindowsName = (numberValue >= 1 And numberValue <= 9)
End If
End If
End Function
Private Sub RequireFile(ByVal filePath As String, ByVal labelText As String)
If Not FileExists(filePath) Then
Err.Raise vbObjectError + 2550, , _
"Missing " & labelText & ": " & filePath
End If
If FileLen(filePath) = 0 Then
Err.Raise vbObjectError + 2551, , _
"The " & labelText & " is empty: " & filePath
End If
End Sub
Private Sub RequireFolder(ByVal folderPath As String, ByVal labelText As String)
If Not FolderExists(folderPath) Then
Err.Raise vbObjectError + 2552, , _
"Missing " & labelText & ": " & folderPath
End If
End Sub
Private Function FileExists(ByVal filePath As String) As Boolean
On Error Resume Next
FileExists = (Len(Dir$(filePath, vbNormal Or vbReadOnly Or vbHidden Or vbSystem)) > 0)
On Error GoTo 0
End Function
Private Function FolderExists(ByVal folderPath As String) As Boolean
Dim attributes As Long
On Error Resume Next
attributes = GetAttr(folderPath)
FolderExists = (Err.Number = 0) And ((attributes And vbDirectory) = vbDirectory)
Err.Clear
On Error GoTo 0
End Function
Private Sub SafeDeleteTempFile(ByVal filePath As String)
If Len(filePath) = 0 Then Exit Sub
If InStr(1, filePath, "\.__batch_", vbTextCompare) = 0 Then Exit Sub
If FileExists(filePath) Then Kill filePath
End Sub
Private Sub RaiseRowError(ByVal rowNumber As Long, _
ByVal fieldName As String, _
ByVal messageText As String)
Err.Raise vbObjectError + 2560, , _
"Row " & rowNumber & ", " & fieldName & ": " & messageText
End SubMicrosoft references: InlineShapes.AddPicture, SaveAs2, ExportAsFixedFormat, and Application.DisplayAlerts.
Resume from evidence, not from assumptions
A status column helps, but Excel and the file system cannot be updated as one atomic transaction.
DONE means both files moved
The example sets DONE only after the final DOCX and optional PDF have been promoted from their temporary names.
A crash can leave a mismatch
If the process stops after a file move but before the next workbook save, the file may exist while Excel still shows the row as incomplete.
Reconcile the last checkpoint
Compare recent output names with GenerationStatus and GeneratedAt. Do not delete or regenerate files until ownership is clear.
Resume with one writer
After correction, restart the same workbook against the same controlled folder. Existing outputs remain collision blockers.
Use a six-record pilot before the first large batch
1. Normal complete row
Confirm every bookmark, filename, DOCX, PDF, status update, timestamp, and checkpoint behavior.
2. Longest realistic text
Test wrapping, table growth, page breaks, headers, footers, and text near the bottom of a page.
3. Leading-zero identifier
Verify that Excel, Word, PDF, and the output name all preserve the required identifier exactly.
4. Blank optional image
Confirm that the placeholder clears without leaving a token or changing the surrounding layout.
5. Wide and tall images
Verify embedded files, preserved aspect ratio, bounding box behavior, and PDF output.
6. Existing-file collision
Place a file with the intended name in the output folder and confirm preflight stops before Word generation.
The 50-record run tests throughput, checkpoint saving, output counts, and recovery procedures before you allow 250-record production chunks.
Use Factory for the row-based production workload
DocxForge Pro Factory matches this relationship: many independent Excel rows, one standard Word template, and one separate DOCX or PDF per row.
Correct module
Factory is designed for repeated row-based output from one standard template.
Input discipline
Review the Excel rows, template tags, file names, and image references before generation.
Output discipline
Use deterministic names, a controlled local folder, and a reconciliation step after each production chunk.
Where it does not fit
Contractor is for one case that creates several different documents from different Word templates, not 1000 independent rows through one template.


Frequently asked questions
Can one Excel file generate more than 1000 Word documents?
Yes, when each row is an independent record and every row uses one approved Word template. For reliability, preflight the full source but process the outputs in controlled batches rather than treating one uninterrupted 1000-file run as the only success condition.
Why does the VBA example process only 250 documents per run?
A bounded run makes failures easier to isolate, reduces the amount of unsaved progress, and gives the operator a review point. Rows marked DONE are skipped when the next batch starts.
How do I prevent duplicate files from overwriting each other?
Provide an explicit OutputName, clean it before use, check duplicates after cleaning, reject existing DOCX/PDF files, write to a unique temporary name, and move the completed file only after a second collision check.
Can every row include a different image?
Yes. The example reads an optional ImageFile from a controlled local Assets folder, embeds it at the RecordImage bookmark, preserves its aspect ratio, and fits it inside the template’s tested image box.
Can I resume after Word or the macro stops?
The workbook records DONE and GeneratedAt after each completed row and saves a checkpoint every 25 documents. Review the last row and output folder before resuming, especially if a file was moved immediately before an unexpected stop.
Should I use Factory or Contractor for 1000 independent rows?
Use Factory: many independent Excel rows, one standard Word template, and one separate output per row. Contractor is for one reviewed case that creates several different documents from different Word templates.