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


Factory: Excel row batch
Review many independent rows, then generate each row through one standard Word template.


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.