High-Volume Excel-to-Word Batch

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.

Full-source preflight
250-document batches
25-document checkpoints
Resume with DONE status
“1000+ documents” describes the total workload. It does not require one fragile, uninterrupted Word session.

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.

Recommended progression

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.

Required row-1 headers

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.

Excel VBA — controlled Factory-shaped batchWord Desktop on Windows required
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 Sub

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

Then run 50

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.

DocxForge Pro Factory Excel data screen for a large batch
Review the Excel rows that will feed the standard template.
DocxForge Pro Factory documents screen for batch generation
Confirm the template and document settings before production.
One approved template. Hundreds or thousands of separate row-based documents.Try DocxForge Pro Factory for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Factory batch workflowExcel → WordLocal processingOptional PDF
Start Free 7-Day Trial

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.

Move from a proven pilot to controlled Factory productionGenerate separate row-based documents locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
One Word templateMany Excel rowsControlled filenamesDOCX + optional PDF
Start Free 7-Day Trial