Fixes & Troubleshooting

Fix: Batch Generation Slows Down After Hundreds of Documents

Measure where time grows, limit each desktop run, restart the Word instance at controlled boundaries, and resume from saved evidence instead of pushing one process indefinitely.

Stage timing
40-document sessions
200-document runs
Safe resume
A restart can reduce accumulated Word-session cost, but it is not proof that Word caused the slowdown. Log opening, filling, saving, PDF export, and Excel checkpoints separately.

Quick answer: measure the slope before changing the batch size

Compare per-document time near the start and end of the same run. If only Word startup is expensive, fewer restarts may help. If template opening, DOCX saving, PDF export, or Excel checkpoints become progressively slower, fix that stage instead of guessing.

Use bounded runs

Process a controlled number of READY rows, record DONE only after output succeeds, save checkpoints, close every document, and restart the hidden Word instance between measured sessions.

Match the slowdown signature to the likely stage

Opening the template grows slower

Investigate the template, attached resources, add-ins, fonts, linked content, network access, and the lifetime of the Word session.

Bookmark filling grows slower

Check whether the code repeatedly scans the full document, updates unnecessary fields, or leaves objects referenced after each row.

DOCX saving grows slower

Check output storage, sync software, antivirus scanning, filename collisions, document size, and whether the folder contains an extreme number of files.

PDF export grows slower

Inspect images, fonts, pagination, fields, document complexity, and the fixed-format export itself. Compare DOCX-only and PDF runs.

Only checkpoint rows are slow

The Excel workbook save may dominate the interval. Measure it separately before changing the Word restart frequency.

Every stage is slow from document one

Look for machine load, storage latency, add-ins, security scans, or a heavy template rather than a gradual session buildup.

Time the stages that can change independently

One total duration cannot tell you whether Word, PDF export, Excel, or storage is responsible.

Word session startup

Log startup only on the first document of each session. It is expected to be non-zero at documents 1, 41, 81, 121, and 161 in the example.

Open + fill

Measure opening the read-only template separately from writing the two required bookmarks.

DOCX + PDF

Measure SaveAs2 separately from ExportAsFixedFormat so PDF-specific growth is visible.

Excel checkpoint

Measure workbook saving every tenth completed document rather than hiding it inside total time.

Total elapsed time

Keep the overall time for comparison, but interpret it through the component columns.

Use a queue that can stop and resume cleanly

Save the workbook as .xlsm. Create PerformanceQueue and use the exact A–E order below.

Required headers in row 1

A — DocumentID; B — ClientName; C — OutputName; D — OutputFormat; E — RecordStatus.

DocumentID

Use a unique text identifier with letters, digits, underscore, or hyphen.

OutputName

Use a unique filename stem without .docx or .pdf. Existing targets block the run.

OutputFormat

Use exactly DOCX, PDF, or BOTH. A PDF-only row uses a temporary DOCX during export.

RecordStatus

Use exactly READY, HOLD, or DONE. The next run processes the next READY rows in sheet order.

Keep four limits explicit

200 documents per run

The process stops after a bounded unit of work. Additional READY rows remain for the next run.

40 documents per Word session

The owned hidden instance is closed and recreated only at a controlled boundary.

10-document Excel checkpoints

Status changes are saved periodically, and the checkpoint duration appears in its own log column.

One output writer

Do not run concurrent writers against the same output folder. Rechecking a filename does not make a DOCX/PDF pair atomic.

Template contract

Place Templates\PerformanceTemplate.docx beside the workbook and create two ordinary Word bookmarks: DocumentID and ClientName.

Free VBA helper: generate and log a measured batch

The macro preflights the queue, validates the template in a separate Word session, creates or checks BatchPerformanceLog, times every major stage, restarts its own Word instance every 40 documents, and caps one run at 200 outputs.

Exact preflight

Checks headers, text storage, duplicate IDs, duplicate names, output formats, statuses, the template, bookmarks, and existing targets.

Per-document timing

Logs startup, open, fill, DOCX, PDF, Excel checkpoint, and total seconds with the session number.

Controlled lifecycle

Closes each document and restarts only the hidden Word instance created by the macro.

Evidence-preserving failure

Writes an ERROR log when possible and never deletes already committed output automatically.

Excel VBA — measured Word batchStatic example
Option Explicit

Private Const DATA_SHEET As String = "PerformanceQueue"
Private Const LOG_SHEET As String = "BatchPerformanceLog"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "PerformanceTemplate.docx"
Private Const OUTPUT_FOLDER As String = "\GeneratedPerformanceBatch"
Private Const FIRST_DATA_ROW As Long = 2

Private Const COL_DOCUMENT_ID As Long = 1
Private Const COL_CLIENT_NAME As Long = 2
Private Const COL_OUTPUT_NAME As Long = 3
Private Const COL_OUTPUT_FORMAT As Long = 4
Private Const COL_RECORD_STATUS As Long = 5

Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const FORMAT_DOCX As String = "DOCX"
Private Const FORMAT_PDF As String = "PDF"
Private Const FORMAT_BOTH As String = "BOTH"

Private Const MAX_DOCUMENTS_PER_RUN As Long = 200
Private Const DOCUMENTS_PER_WORD_SESSION As Long = 40
Private Const CHECKPOINT_INTERVAL As Long = 10

Private Const WD_FORMAT_DOCUMENT_DEFAULT As Long = 16
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_EXPORT_OPTIMIZE_FOR_PRINT As Long = 0
Private Const WD_EXPORT_ALL_DOCUMENT As Long = 0
Private Const WD_EXPORT_DOCUMENT_CONTENT As Long = 0
Private Const WD_EXPORT_CREATE_HEADING_BOOKMARKS As Long = 1
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_ALERTS_NONE As Long = 0
Private Const FILE_ATTRIBUTES As Long = 39

Public Sub GenerateMeasuredPerformanceBatch()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim logWs As Worksheet
    Dim dataValues As Variant
    Dim lastCell As Range
    Dim lastRow As Long
    Dim dataRow As Long
    Dim rowNumber As Long
    Dim readyTotal As Long
    Dim plannedCount As Long
    Dim processedCount As Long
    Dim sessionDocumentCount As Long
    Dim sessionNumber As Long
    Dim templatePath As String
    Dim outputFolder As String
    Dim documentId As String
    Dim clientName As String
    Dim outputName As String
    Dim outputFormat As String
    Dim recordStatus As String
    Dim docxPath As String
    Dim pdfPath As String
    Dim workingDocxPath As String
    Dim runId As String
    Dim idRows As Object
    Dim outputRows As Object
    Dim wdApp As Object
    Dim doc As Object
    Dim totalStart As Double
    Dim stageStart As Double
    Dim wordStartSeconds As Double
    Dim openSeconds As Double
    Dim fillSeconds As Double
    Dim saveDocxSeconds As Double
    Dim exportPdfSeconds As Double
    Dim excelCheckpointSeconds As Double
    Dim totalSeconds As Double
    Dim failureTotalSeconds As Double
    Dim errorNumber As Long
    Dim errorMessage As String
    Dim currentDocumentId As String
    Dim cleanupSaveMessage As String
    Dim workingDocxIsTemporary As Boolean
    Dim performanceLogRow As Long

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    If Len(wb.Path) = 0 Then
        Err.Raise vbObjectError + 4000, , _
            "Save this workbook as .xlsm before running the batch."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 4001, , _
            "Open a writable workbook before running the batch."
    End If
    If wb.ProtectStructure Then
        Err.Raise vbObjectError + 4002, , _
            "Unprotect the workbook structure before creating or using the log sheet."
    End If

    Set ws = RequireWorksheet(wb, DATA_SHEET)
    If ws.ProtectContents Then
        Err.Raise vbObjectError + 4003, , _
            "Unprotect PerformanceQueue so DONE can be recorded."
    End If
    ValidateQueueHeaders ws
    Set logWs = EnsureLogSheet(wb)
    If logWs.ProtectContents Then
        Err.Raise vbObjectError + 4004, , _
            "Unprotect BatchPerformanceLog before running the batch."
    End If
    ValidateLogHeaders logWs

    Set lastCell = ws.Range("A:E").Find(What:="*", _
        After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    If lastCell Is Nothing Then
        Err.Raise vbObjectError + 4005, , "PerformanceQueue is empty."
    End If
    If lastCell.Row < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 4005, , _
            "PerformanceQueue has no data rows."
    End If
    lastRow = lastCell.Row

    Application.CalculateFull
    dataValues = ws.Range(ws.Cells(FIRST_DATA_ROW, 1), _
        ws.Cells(lastRow, COL_RECORD_STATUS)).Value2

    templatePath = wb.Path & TEMPLATE_FOLDER & "\" & TEMPLATE_FILE
    outputFolder = wb.Path & OUTPUT_FOLDER
    RequireNonEmptyFile templatePath, "Word template"
    EnsureFolder outputFolder

    Set idRows = CreateObject("Scripting.Dictionary")
    idRows.CompareMode = vbTextCompare
    Set outputRows = CreateObject("Scripting.Dictionary")
    outputRows.CompareMode = vbTextCompare

    ' Pass 1: validate the queue before Word starts.
    For dataRow = 1 To UBound(dataValues, 1)
        rowNumber = dataRow + FIRST_DATA_ROW - 1
        If ArrayRowIsBlank(dataValues, dataRow) Then
            Err.Raise vbObjectError + 4010, , _
                "Blank row inside the data range: row " & rowNumber
        End If

        documentId = ReadRequiredText( _
            dataValues(dataRow, COL_DOCUMENT_ID), "DocumentID", rowNumber)
        clientName = ReadRequiredText( _
            dataValues(dataRow, COL_CLIENT_NAME), "ClientName", rowNumber)
        outputName = ReadRequiredText( _
            dataValues(dataRow, COL_OUTPUT_NAME), "OutputName", rowNumber)
        outputFormat = ReadRequiredText( _
            dataValues(dataRow, COL_OUTPUT_FORMAT), "OutputFormat", rowNumber)
        recordStatus = ReadRequiredText( _
            dataValues(dataRow, COL_RECORD_STATUS), "RecordStatus", rowNumber)

        RequireStringValue dataValues(dataRow, COL_DOCUMENT_ID), _
            "DocumentID", rowNumber
        RequireStringValue dataValues(dataRow, COL_CLIENT_NAME), _
            "ClientName", rowNumber
        RequireStringValue dataValues(dataRow, COL_OUTPUT_NAME), _
            "OutputName", rowNumber
        RequireStringValue dataValues(dataRow, COL_OUTPUT_FORMAT), _
            "OutputFormat", rowNumber
        RequireStringValue dataValues(dataRow, COL_RECORD_STATUS), _
            "RecordStatus", rowNumber

        ValidateIdentifier documentId, "DocumentID", rowNumber
        ValidateFileStem outputName, rowNumber
        ValidateOutputFormat outputFormat, rowNumber
        ValidateRecordStatus recordStatus, rowNumber

        If idRows.Exists(documentId) Then
            Err.Raise vbObjectError + 4011, , _
                "Duplicate DocumentID in rows " & idRows(documentId) & _
                " and " & rowNumber & ": " & documentId
        End If
        idRows.Add documentId, rowNumber

        If outputRows.Exists(outputName) Then
            Err.Raise vbObjectError + 4012, , _
                "Duplicate OutputName in rows " & outputRows(outputName) & _
                " and " & rowNumber & ": " & outputName
        End If
        outputRows.Add outputName, rowNumber

        If recordStatus = STATUS_READY Then
            readyTotal = readyTotal + 1
            If readyTotal <= MAX_DOCUMENTS_PER_RUN Then
                docxPath = outputFolder & "\" & outputName & ".docx"
                pdfPath = outputFolder & "\" & outputName & ".pdf"
                If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
                    RequireTargetAvailable docxPath, rowNumber
                End If
                If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
                    RequireTargetAvailable pdfPath, rowNumber
                End If
            End If
        End If
    Next dataRow

    If readyTotal = 0 Then
        Err.Raise vbObjectError + 4013, , _
            "No READY rows were found on PerformanceQueue."
    End If
    plannedCount = readyTotal
    If plannedCount > MAX_DOCUMENTS_PER_RUN Then
        plannedCount = MAX_DOCUMENTS_PER_RUN
    End If

    wb.Save
    runId = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
        Format$(CLng(Timer * 100), "00000000")
    rowNumber = 0

    ' Validate the template in a separate Word session before timed output.
    Set wdApp = StartWordApplication()
    Set doc = wdApp.Documents.Open(templatePath, False, True, False)
    ValidateTemplateBookmarks doc
    doc.Close WD_DO_NOT_SAVE_CHANGES
    Set doc = Nothing
    StopWordApplication wdApp
    Set wdApp = Nothing

    ' Pass 2: process at most MAX_DOCUMENTS_PER_RUN cached READY rows.
    For dataRow = 1 To UBound(dataValues, 1)
        If processedCount >= plannedCount Then Exit For
        rowNumber = dataRow + FIRST_DATA_ROW - 1
        recordStatus = CStr(dataValues(dataRow, COL_RECORD_STATUS))
        If recordStatus = STATUS_READY Then
            totalStart = Timer
            wordStartSeconds = 0
            openSeconds = 0
            fillSeconds = 0
            saveDocxSeconds = 0
            exportPdfSeconds = 0
            excelCheckpointSeconds = 0
            performanceLogRow = 0

            If sessionDocumentCount = 0 Then
                stageStart = Timer
                Set wdApp = StartWordApplication()
                wordStartSeconds = SecondsElapsed(stageStart, Timer)
                sessionNumber = sessionNumber + 1
            End If

            documentId = CStr(dataValues(dataRow, COL_DOCUMENT_ID))
            currentDocumentId = documentId
            clientName = CStr(dataValues(dataRow, COL_CLIENT_NAME))
            outputName = CStr(dataValues(dataRow, COL_OUTPUT_NAME))
            outputFormat = CStr(dataValues(dataRow, COL_OUTPUT_FORMAT))
            docxPath = outputFolder & "\" & outputName & ".docx"
            pdfPath = outputFolder & "\" & outputName & ".pdf"
            workingDocxPath = docxPath
            workingDocxIsTemporary = False
            If outputFormat = FORMAT_PDF Then
                workingDocxPath = outputFolder & "\.__pdf_stage_" & _
                    outputName & "_" & runId & ".docx"
                workingDocxIsTemporary = True
            End If

            RequireTargetAvailable workingDocxPath, rowNumber
            If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
                RequireTargetAvailable pdfPath, rowNumber
            End If

            stageStart = Timer
            Set doc = wdApp.Documents.Open(templatePath, False, True, False)
            openSeconds = SecondsElapsed(stageStart, Timer)

            stageStart = Timer
            WriteRequiredBookmark doc, "DocumentID", documentId
            WriteRequiredBookmark doc, "ClientName", clientName
            fillSeconds = SecondsElapsed(stageStart, Timer)

            stageStart = Timer
            doc.SaveAs2 workingDocxPath, WD_FORMAT_DOCUMENT_DEFAULT
            saveDocxSeconds = SecondsElapsed(stageStart, Timer)

            If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
                stageStart = Timer
                doc.ExportAsFixedFormat pdfPath, WD_EXPORT_FORMAT_PDF, _
                    False, WD_EXPORT_OPTIMIZE_FOR_PRINT, _
                    WD_EXPORT_ALL_DOCUMENT, 1, 1, _
                    WD_EXPORT_DOCUMENT_CONTENT, True, True, _
                    WD_EXPORT_CREATE_HEADING_BOOKMARKS, True, True, False
                exportPdfSeconds = SecondsElapsed(stageStart, Timer)
            End If

            doc.Close WD_DO_NOT_SAVE_CHANGES
            Set doc = Nothing
            If outputFormat = FORMAT_PDF Then
                If FileExists(workingDocxPath) Then Kill workingDocxPath
            End If
            workingDocxPath = vbNullString
            workingDocxIsTemporary = False

            processedCount = processedCount + 1
            sessionDocumentCount = sessionDocumentCount + 1
            ws.Cells(rowNumber, COL_RECORD_STATUS).Value = STATUS_DONE

            If processedCount Mod CHECKPOINT_INTERVAL = 0 Then
                totalSeconds = SecondsElapsed(totalStart, Timer)
                performanceLogRow = AppendPerformanceLog( _
                    logWs, runId, processedCount, rowNumber, documentId, _
                    sessionNumber, wordStartSeconds, openSeconds, _
                    fillSeconds, saveDocxSeconds, exportPdfSeconds, 0, _
                    totalSeconds, "OK_PENDING_CHECKPOINT", _
                    "Status and preliminary log are awaiting one checkpoint save.")

                ' This save commits DONE and the preliminary log row together.
                stageStart = Timer
                wb.Save
                excelCheckpointSeconds = SecondsElapsed(stageStart, Timer)
                totalSeconds = SecondsElapsed(totalStart, Timer)
                FinalizePerformanceLog logWs, performanceLogRow, _
                    excelCheckpointSeconds, totalSeconds, "OK", _
                    "DONE and the log row were committed together. " & _
                    "A second save persisted these timing values."

                ' The second save persists the measured first save. Its own
                ' duration is intentionally excluded to avoid recursive timing.
                wb.Save
            Else
                totalSeconds = SecondsElapsed(totalStart, Timer)
                performanceLogRow = AppendPerformanceLog( _
                    logWs, runId, processedCount, rowNumber, documentId, _
                    sessionNumber, wordStartSeconds, openSeconds, _
                    fillSeconds, saveDocxSeconds, exportPdfSeconds, 0, _
                    totalSeconds, "OK", vbNullString)
            End If

            currentDocumentId = vbNullString

            If sessionDocumentCount >= DOCUMENTS_PER_WORD_SESSION And _
               processedCount < plannedCount Then
                StopWordApplication wdApp
                Set wdApp = Nothing
                sessionDocumentCount = 0
            End If
        End If
    Next dataRow

    StopWordApplication wdApp
    Set wdApp = Nothing
    wb.Save

    MsgBox processedCount & " document(s) generated and logged." & vbCrLf & _
        IIf(readyTotal > plannedCount, _
            CStr(readyTotal - plannedCount) & _
            " READY row(s) remain for the next run.", _
            "No READY rows remain from this preflight."), _
        vbInformation, "Measured performance batch"
    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
    StopWordApplication wdApp
    If workingDocxIsTemporary And Len(workingDocxPath) > 0 Then
        If FileExists(workingDocxPath) Then Kill workingDocxPath
    End If
    Err.Clear
    If totalStart > 0 Then
        failureTotalSeconds = SecondsElapsed(totalStart, Timer)
    Else
        failureTotalSeconds = 0
    End If
    If Not logWs Is Nothing And Len(runId) > 0 Then
        performanceLogRow = AppendPerformanceLog( _
            logWs, runId, processedCount + 1, rowNumber, _
            currentDocumentId, sessionNumber, wordStartSeconds, _
            openSeconds, fillSeconds, saveDocxSeconds, _
            exportPdfSeconds, excelCheckpointSeconds, _
            failureTotalSeconds, "ERROR", _
            "Error " & errorNumber & ": " & errorMessage)
    End If
    If Err.Number <> 0 Then
        cleanupSaveMessage = vbCrLf & _
            "The ERROR row could not be added to BatchPerformanceLog: " & _
            Err.Description
    End If
    Err.Clear
    If Not wb Is Nothing Then wb.Save
    If Err.Number <> 0 Then
        cleanupSaveMessage = cleanupSaveMessage & vbCrLf & _
            "The error log or latest statuses could not be saved: " & _
            Err.Description
    End If
    On Error GoTo 0
    MsgBox "Generation stopped." & vbCrLf & _
        IIf(Len(currentDocumentId) > 0, _
            "Current DocumentID: " & currentDocumentId & vbCrLf, "") & _
        "Error " & errorNumber & ": " & errorMessage & _
        cleanupSaveMessage & vbCrLf & _
        "Review GeneratedPerformanceBatch and BatchPerformanceLog before retrying. " & _
        "Existing output is not deleted automatically.", _
        vbCritical, "Measured performance batch"
End Sub

Private Function StartWordApplication() As Object
    Dim app As Object
    Set app = CreateObject("Word.Application")
    app.Visible = False
    app.ScreenUpdating = False
    app.DisplayAlerts = WD_ALERTS_NONE
    Set StartWordApplication = app
End Function

Private Sub StopWordApplication(ByVal wdApp As Object)
    If wdApp Is Nothing Then Exit Sub
    wdApp.ScreenUpdating = True
    wdApp.NormalTemplate.Saved = True
    wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End Sub

Private Function SecondsElapsed(ByVal startValue As Double, _
                                ByVal endValue As Double) As Double
    If endValue >= startValue Then
        SecondsElapsed = endValue - startValue
    Else
        SecondsElapsed = (86400# - startValue) + endValue
    End If
End Function

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 + 4020, , _
            "Required sheet is missing: " & sheetName
    End If
End Function

Private Function EnsureLogSheet(ByVal wb As Workbook) As Worksheet
    On Error Resume Next
    Set EnsureLogSheet = wb.Worksheets(LOG_SHEET)
    On Error GoTo 0
    If EnsureLogSheet Is Nothing Then
        Set EnsureLogSheet = wb.Worksheets.Add( _
            After:=wb.Worksheets(wb.Worksheets.Count))
        EnsureLogSheet.Name = LOG_SHEET
        WriteLogHeaders EnsureLogSheet
    End If
End Function

Private Sub WriteLogHeaders(ByVal ws As Worksheet)
    Dim headers As Variant
    Dim columnNumber As Long
    headers = Array("RunID", "Sequence", "ExcelRow", _
        "DocumentID", "WordSession", "WordStartSeconds", _
        "OpenSeconds", "FillSeconds", "SaveDocxSeconds", _
        "ExportPdfSeconds", "ExcelCheckpointSeconds", "TotalSeconds", _
        "Result", "Message", "LoggedAt")
    For columnNumber = 1 To 15
        ws.Cells(1, columnNumber).Value = headers(columnNumber - 1)
    Next columnNumber
End Sub

Private Sub ValidateQueueHeaders(ByVal ws As Worksheet)
    Dim headers As Variant
    Dim columnNumber As Long
    Dim rawHeader As Variant
    headers = Array("DocumentID", "ClientName", "OutputName", _
        "OutputFormat", "RecordStatus")
    For columnNumber = 1 To 5
        rawHeader = ws.Cells(1, columnNumber).Value2
        If IsError(rawHeader) Then
            Err.Raise vbObjectError + 4021, , _
                "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 + 4021, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " must match exactly: " & headers(columnNumber - 1) & "."
        End If
    Next columnNumber
End Sub

Private Sub ValidateLogHeaders(ByVal ws As Worksheet)
    Dim headers As Variant
    Dim columnNumber As Long
    Dim rawHeader As Variant
    headers = Array("RunID", "Sequence", "ExcelRow", "DocumentID", _
        "WordSession", "WordStartSeconds", "OpenSeconds", "FillSeconds", _
        "SaveDocxSeconds", "ExportPdfSeconds", "ExcelCheckpointSeconds", _
        "TotalSeconds", "Result", "Message", "LoggedAt")
    For columnNumber = 1 To 15
        rawHeader = ws.Cells(1, columnNumber).Value2
        If IsError(rawHeader) Or _
           StrComp(CStr(rawHeader), CStr(headers(columnNumber - 1)), _
                   vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 4022, , _
                "BatchPerformanceLog header " & _
                ws.Cells(1, columnNumber).Address(False, False) & _
                " must match exactly: " & headers(columnNumber - 1) & "."
        End If
    Next columnNumber
End Sub

Private Function AppendPerformanceLog(ByVal ws As Worksheet, _
                                      ByVal runId As String, _
                                      ByVal sequenceNumber As Long, _
                                      ByVal excelRow As Long, _
                                      ByVal documentId As String, _
                                      ByVal wordSession As Long, _
                                      ByVal wordStartSeconds As Double, _
                                      ByVal openSeconds As Double, _
                                      ByVal fillSeconds As Double, _
                                      ByVal saveDocxSeconds As Double, _
                                      ByVal exportPdfSeconds As Double, _
                                      ByVal checkpointSeconds As Double, _
                                      ByVal totalSeconds As Double, _
                                      ByVal resultText As String, _
                                      ByVal messageText As String) As Long
    Dim nextRow As Long
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(nextRow, 1).Value = runId
    ws.Cells(nextRow, 2).Value = sequenceNumber
    ws.Cells(nextRow, 3).Value = excelRow
    ws.Cells(nextRow, 4).Value = documentId
    ws.Cells(nextRow, 5).Value = wordSession
    ws.Cells(nextRow, 6).Value = Round(wordStartSeconds, 3)
    ws.Cells(nextRow, 7).Value = Round(openSeconds, 3)
    ws.Cells(nextRow, 8).Value = Round(fillSeconds, 3)
    ws.Cells(nextRow, 9).Value = Round(saveDocxSeconds, 3)
    ws.Cells(nextRow, 10).Value = Round(exportPdfSeconds, 3)
    ws.Cells(nextRow, 11).Value = Round(checkpointSeconds, 3)
    ws.Cells(nextRow, 12).Value = Round(totalSeconds, 3)
    ws.Cells(nextRow, 13).Value = resultText
    ws.Cells(nextRow, 14).Value = messageText
    ws.Cells(nextRow, 15).Value = Now
    AppendPerformanceLog = nextRow
End Function

Private Sub FinalizePerformanceLog(ByVal ws As Worksheet, _
                                   ByVal logRow As Long, _
                                   ByVal checkpointSeconds As Double, _
                                   ByVal totalSeconds As Double, _
                                   ByVal resultText As String, _
                                   ByVal messageText As String)
    If logRow < 2 Then
        Err.Raise vbObjectError + 4023, , _
            "A valid BatchPerformanceLog row was not returned."
    End If
    ws.Cells(logRow, 11).Value = Round(checkpointSeconds, 3)
    ws.Cells(logRow, 12).Value = Round(totalSeconds, 3)
    ws.Cells(logRow, 13).Value = resultText
    ws.Cells(logRow, 14).Value = messageText
    ws.Cells(logRow, 15).Value = Now
End Sub

Private Function ArrayRowIsBlank(ByVal values As Variant, _
                                 ByVal dataRow As Long) As Boolean
    Dim columnNumber As Long
    For columnNumber = 1 To COL_RECORD_STATUS
        If IsError(values(dataRow, columnNumber)) Then Exit Function
        If Len(CStr(values(dataRow, columnNumber))) > 0 Then Exit Function
    Next columnNumber
    ArrayRowIsBlank = True
End Function

Private Function ReadRequiredText(ByVal rawValue As Variant, _
                                  ByVal fieldName As String, _
                                  ByVal rowNumber As Long) As String
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 4023, , _
            fieldName & " contains an Excel error in row " & rowNumber & "."
    End If
    ReadRequiredText = CStr(rawValue)
    If Len(ReadRequiredText) = 0 Then
        Err.Raise vbObjectError + 4024, , _
            fieldName & " is required in row " & rowNumber & "."
    End If
    If StrComp(ReadRequiredText, Trim$(ReadRequiredText), _
               vbBinaryCompare) <> 0 Then
        Err.Raise vbObjectError + 4025, , _
            fieldName & " has leading or trailing spaces in row " & _
            rowNumber & "."
    End If
    ValidateSingleLine ReadRequiredText, fieldName, rowNumber
    ValidateControlCharacters ReadRequiredText, fieldName, rowNumber
End Function

Private Sub RequireStringValue(ByVal rawValue As Variant, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long)
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 4023, , _
            fieldName & " contains an Excel error in row " & rowNumber & "."
    End If
    If VarType(rawValue) <> vbString Then
        Err.Raise vbObjectError + 4026, , _
            fieldName & " must be stored as text in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateSingleLine(ByVal valueText As String, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long)
    If InStr(valueText, vbCr) > 0 Or InStr(valueText, vbLf) > 0 Or _
       InStr(valueText, vbTab) > 0 Then
        Err.Raise vbObjectError + 4027, , _
            fieldName & " must be one line in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateControlCharacters(ByVal valueText As String, _
                                      ByVal fieldName As String, _
                                      ByVal rowNumber As Long)
    Dim position As Long
    Dim characterCode As Long
    For position = 1 To Len(valueText)
        characterCode = AscW(Mid$(valueText, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            Err.Raise vbObjectError + 4028, , _
                fieldName & " contains a control character in row " & _
                rowNumber & "."
        End If
    Next position
End Sub

Private Sub ValidateIdentifier(ByVal valueText As String, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long)
    Dim position As Long
    Dim character As String
    For position = 1 To Len(valueText)
        character = Mid$(valueText, position, 1)
        If Not (character Like "[A-Za-z0-9_-]") Then
            Err.Raise vbObjectError + 4029, , _
                fieldName & " may use only letters, digits, underscore, and hyphen " & _
                "in row " & rowNumber & "."
        End If
    Next position
End Sub

Private Sub ValidateOutputFormat(ByVal valueText As String, _
                                 ByVal rowNumber As Long)
    If valueText <> FORMAT_DOCX And valueText <> FORMAT_PDF And _
       valueText <> FORMAT_BOTH Then
        Err.Raise vbObjectError + 4030, , _
            "OutputFormat must be DOCX, PDF, or BOTH in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateRecordStatus(ByVal valueText As String, _
                                 ByVal rowNumber As Long)
    If valueText <> STATUS_READY And valueText <> STATUS_HOLD And _
       valueText <> STATUS_DONE Then
        Err.Raise vbObjectError + 4031, , _
            "RecordStatus must be READY, HOLD, or DONE in row " & rowNumber & "."
    End If
End Sub

Private Sub ValidateFileStem(ByVal fileStem As String, _
                             ByVal rowNumber As Long)
    Dim forbidden As Variant
    Dim item As Variant
    Dim position As Long
    Dim characterCode As Long
    Dim baseName As String
    forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    If Len(fileStem) > 120 Then
        Err.Raise vbObjectError + 4032, , _
            "OutputName exceeds 120 characters in row " & rowNumber & "."
    End If
    If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
        Err.Raise vbObjectError + 4032, , _
            "OutputName ends with a dot or space in row " & rowNumber & "."
    End If
    If LCase$(Right$(fileStem, 5)) = ".docx" Or _
       LCase$(Right$(fileStem, 4)) = ".pdf" Then
        Err.Raise vbObjectError + 4032, , _
            "Store OutputName without .docx or .pdf in row " & rowNumber & "."
    End If
    For Each item In forbidden
        If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
            Err.Raise vbObjectError + 4032, , _
                "OutputName contains a forbidden character in row " & _
                rowNumber & "."
        End If
    Next item
    For position = 1 To Len(fileStem)
        characterCode = AscW(Mid$(fileStem, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            Err.Raise vbObjectError + 4032, , _
                "OutputName contains a control character in row " & _
                rowNumber & "."
        End If
    Next position
    baseName = fileStem
    If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
        baseName = Left$(baseName, InStr(1, baseName, ".", _
            vbBinaryCompare) - 1)
    End If
    If IsReservedWindowsName(UCase$(baseName)) Then
        Err.Raise vbObjectError + 4032, , _
            "OutputName starts with a reserved Windows name in row " & _
            rowNumber & "."
    End If
End Sub

Private Function IsReservedWindowsName(ByVal upperBase As String) As Boolean
    Dim finalCharacter As String
    Select Case upperBase
        Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
            IsReservedWindowsName = True
            Exit Function
    End Select
    If Left$(upperBase, 3) = "COM" Or Left$(upperBase, 3) = "LPT" Then
        If Len(upperBase) = 4 And Mid$(upperBase, 4, 1) Like "[1-9]" Then
            IsReservedWindowsName = True
            Exit Function
        End If
        If Len(upperBase) = 4 Then
            finalCharacter = Mid$(upperBase, 4, 1)
            If finalCharacter = ChrW$(&HB9) Or _
               finalCharacter = ChrW$(&HB2) Or _
               finalCharacter = ChrW$(&HB3) Then
                IsReservedWindowsName = True
            End If
        End If
    End If
End Function

Private Sub ValidateTemplateBookmarks(ByVal doc As Object)
    Dim names As Variant
    Dim item As Variant
    names = Array("DocumentID", "ClientName")
    For Each item In names
        If Not doc.Bookmarks.Exists(CStr(item)) Then
            Err.Raise vbObjectError + 4033, , _
                "Required Word bookmark is missing: " & item
        End If
        ValidateBookmarkContainer doc, CStr(item)
    Next item
End Sub

Private Sub ValidateBookmarkContainer(ByVal doc As Object, _
                                      ByVal bookmarkName As String)
    Dim targetRange As Object
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    If targetRange.Fields.Count > 0 Or _
       targetRange.ContentControls.Count > 0 Or _
       targetRange.InlineShapes.Count > 0 Then
        Err.Raise vbObjectError + 4034, , _
            "Bookmark contains a field, content control, or image: " & _
            bookmarkName
    End If
End Sub

Private Sub WriteRequiredBookmark(ByVal doc As Object, _
                                  ByVal bookmarkName As String, _
                                  ByVal replacementText As String)
    Dim targetRange As Object
    Dim targetStyle As Variant
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 4035, , _
            "Required Word bookmark is missing: " & bookmarkName
    End If
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    ValidateBookmarkContainer doc, bookmarkName
    targetStyle = targetRange.Style
    targetRange.Text = replacementText
    targetRange.Style = targetStyle
    doc.Bookmarks.Add bookmarkName, targetRange
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 4036, , _
            "Bookmark was not restored: " & bookmarkName
    End If
End Sub

Private Sub RequireTargetAvailable(ByVal filePath As String, _
                                   ByVal rowNumber As Long)
    If FileExists(filePath) Then
        Err.Raise vbObjectError + 4037, , _
            "Output already exists for row " & rowNumber & ": " & filePath
    End If
End Sub

Private Sub RequireNonEmptyFile(ByVal filePath As String, _
                                ByVal labelText As String)
    Dim fileSize As Long
    If Not FileExists(filePath) Then
        Err.Raise vbObjectError + 4038, , _
            labelText & " is missing: " & filePath
    End If
    On Error GoTo SizeFailed
    fileSize = FileLen(filePath)
    If fileSize <= 0 Then
        Err.Raise vbObjectError + 4038, , _
            labelText & " is empty: " & filePath
    End If
    Exit Sub
SizeFailed:
    If Err.Number >= vbObjectError + 4000 And _
       Err.Number <= vbObjectError + 4099 Then
        Err.Raise Err.Number, , Err.Description
    End If
    Err.Raise vbObjectError + 4039, , _
        "Could not read " & labelText & ": " & filePath
End Sub

Private Function FileExists(ByVal filePath As String) As Boolean
    Dim foundName As String
    Dim checkErrorNumber As Long
    Dim checkErrorDescription As String
    On Error GoTo CheckFailed
    foundName = Dir$(filePath, FILE_ATTRIBUTES)
    FileExists = (Len(foundName) > 0)
    Exit Function
CheckFailed:
    checkErrorNumber = Err.Number
    checkErrorDescription = Err.Description
    Err.Clear
    Err.Raise vbObjectError + 4040, "FileExists", _
        "Could not check path: " & filePath & vbCrLf & _
        "File-system error " & checkErrorNumber & ": " & _
        checkErrorDescription
End Function

Private Sub EnsureFolder(ByVal folderPath As String)
    Dim attributes As Long
    If Len(Dir$(folderPath, vbDirectory)) = 0 Then
        MkDir folderPath
    Else
        attributes = GetAttr(folderPath)
        If (attributes And vbDirectory) <> vbDirectory Then
            Err.Raise vbObjectError + 4041, , _
                "Expected a folder but found a file: " & folderPath
        End If
    End If
End Sub

Microsoft documents the lifecycle and export methods used here: Document.Close, Application.Quit, and Document.ExportAsFixedFormat.

Interpret the log without confusing correlation with cause

Compare within one session

Compare documents 2–10 with 31–40. A rising slope inside each session points to accumulated session cost or growing external work.

Compare session boundaries

If document 41 returns close to the early baseline after Word restarts, the session lifecycle is a useful control.

Compare DOCX and PDF rows

If ExportPdfSeconds grows while SaveDocxSeconds remains stable, investigate fixed-format export and document content.

Compare checkpoint rows

If only every tenth document spikes, inspect Excel saving, workbook size, sync, and storage before shortening Word sessions.

Recover from partial work without erasing evidence

Output can exist before DONE is saved

A failure may occur after a file is committed but before the next Excel checkpoint. Inspect the folder before resetting the row.

DOCX and PDF are separate commits

For BOTH, DOCX may succeed before PDF fails. Do not call the pair atomic.

The log is diagnostic evidence

Keep the RunID and session columns. Do not average unrelated templates, machines, storage locations, or output formats together.

Desktop timing is environment-specific

The constants are starting controls, not universal performance promises. Test the real template and production machine.

Use the correct DocxForge Pro module for the workload

DocxForge Pro includes Contractor and Factory. Both run locally, but only Factory is designed around a large row-by-row batch.

Contractor: one case, several different documents

Review one case dataset, then generate several different documents for that case from different Word templates. Use it for a case pack, not for treating hundreds of independent rows as one continuous queue.

Factory: many rows, one standard template

Select many independent Excel rows and generate each row through one standard Word template. Each selected row creates its own document, which directly matches the high-volume workflow diagnosed in this article.

Keep the unit of work visible

In Contractor, performance is evaluated per reviewed case and its different templates. In Factory, evaluate throughput per selected row and per batch using the one standard template.

Contractor: reviewed case workflow

Review one case, then generate its document set from different Word templates.

DocxForge Pro Contractor Case Data screen for reviewing one case
Contractor Case Data: review the shared values for one case.
DocxForge Pro Contractor Documents screen for different Word templates
Contractor Documents: choose the different templates required for the case.

Factory: Excel row batch

Review many independent rows, then generate each row through one standard Word template.

DocxForge Pro Factory Excel Data screen for a high-volume row batch
Factory Excel Data: review the selected rows before generation.
DocxForge Pro Factory Documents screen for one standard Word template
Factory Documents: use one tested template for the row batch.
Use a structured local workflow instead of one indefinite Word sessionTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Contractor case packsFactory row batchesLocal processingDOCX + PDF
Start Free 7-Day Trial

Frequently asked questions

Why does Word generation get slower after many documents?

The cause is not always Word alone. Time may accumulate in template opening, image or field work, DOCX saving, PDF export, Excel checkpoints, antivirus scanning, sync clients, or network storage. Measure each stage before choosing a fix.

How often should the VBA example restart Word?

The example restarts its own hidden Word instance after 40 completed documents. That is a diagnostic operating limit, not a universal optimum. Compare the logged sessions on the actual machine and template before changing it.

Why does one run stop after 200 documents?

The cap creates a bounded, recoverable unit of work. The macro changes completed rows from READY to DONE, saves checkpoints, and leaves additional READY rows for the next run instead of keeping one desktop process alive indefinitely.

Does closing every document guarantee stable performance?

No. Closing each document is necessary, but it does not prove that PDF export, images, fonts, add-ins, storage, security scanning, or Excel saving will remain constant. Use the performance log to identify the stage that actually grows.

Can I delete partial output automatically after an error?

The example does not delete committed files. If a failure happens after DOCX creation but before PDF export or status saving, inspect the output folder and log before retrying. Automatic deletion could remove useful evidence or a file created by another process.

Which DocxForge Pro module fits large row-based batches?

Factory is the direct fit: many independent Excel rows use one standard Word template and each selected row creates its own document. Contractor serves a different workflow: one reviewed case creates several different documents from different templates.

Keep large document workloads bounded, visible, and recoverableRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Controlled batchesLocal Word outputFactory throughputDOCX + PDF
Start Free 7-Day Trial