How to Use Word Templates Instead of Rebuilding Reports Every Time
Separate the approved report design from each report instance. Keep fixed layout and wording in Word, keep variable report content in Excel, and create every DOCX and PDF from a fresh template copy.
Quick answer: create a new report instance, not a copy of the last report
Put reusable layout, styles, fixed notices, headers, footers, and bookmarks in one approved Word template. Put each report’s variable values in one Excel row. For every READY row, create a fresh document from the template and save it under a validated unique name.
The Word template is a controlled design asset. A generated report is a dated business record. Do not promote an old output file into the next cycle’s template.
What survives when teams copy the previous report
Stale identity
Old reporting periods, recipients, record IDs, filenames, and document properties can remain after the visible body is edited.
Stale conclusions
Prior findings or recommendations can survive in a paragraph, table, header, footer, note, or unreviewed section.
Formatting drift
Manual fixes accumulate across copies until teams are no longer producing the same report design.
Unclear release source
The team cannot easily prove which approved template version or fixed wording produced the report.
Keep three assets separate
The Word file owns layout, styles, fixed wording, headers, footers, and exact bookmark destinations.
2. Report data
The Excel row owns the report ID, period display, recipients, prepared-by value, report text, status, and output name.
3. Generated record
The finished DOCX and PDF are outputs for review, distribution, and retention. They are not reused as the design source.
Change the approved template when the design or fixed wording changes. Change the Excel row when the report-specific facts change.
Prepare the ReportRuns sheet
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create ReportRuns with exact A–K headers.
A–E: identity and audience
ReportID; ReportTitle; ReportingPeriodDisplay; PreparedFor; PreparedBy.
F–K: content and output
SummaryText; FindingsText; RecommendationsText; OutputName; RecordStatus; Notes.
Store ReportID and ReportingPeriodDisplay as true text values. Use READY or HOLD exactly. OutputName is a filename stem without .docx or .pdf. This example permits at most 100 READY reports per run.
Prepare the approved Word template
Save RecurringReportTemplate.docx in Templates beside the .xlsm workbook.
Identity bookmarks
ReportID; ReportTitle; ReportingPeriodDisplay.
Audience bookmarks
PreparedFor; PreparedBy.
Content bookmarks
SummaryText; FindingsText; RecommendationsText.
Plain-text boundary
Each bookmark must contain plain placeholder text, not a nested field, content control, or image.
Free VBA example: generate fresh reports from the approved template
The macro performs a full preflight before Word starts, caches the validated values in memory, creates every report from a fresh template instance, restores bookmark ranges, stages DOCX and PDF files, and rechecks final destinations before commit.
Exact input contract
Checks headers, READY/HOLD, required values, true text IDs and period displays, output names, length limits, and single-paragraph replacement text.
Fresh document every time
Uses Documents.Add with the approved template for each row. It never opens last period’s report as the source.
Formatting guard
Rejects bookmarks that contain nested fields, content controls, or images, then restores the original bookmark style after replacement.
Staged commit
Creates files in a unique run folder and checks all final paths again. The multi-file move remains sequential rather than atomic.
Option Explicit
Private Const DATA_SHEET As String = "ReportRuns"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "RecurringReportTemplate.docx"
Private Const OUTPUT_FOLDER As String = "\GeneratedReports"
Private Const FIRST_DATA_ROW As Long = 2
Private Const MAX_REPORTS_PER_RUN As Long = 100
Private Const FILE_ATTRIBUTES As Long = 39
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_FORMAT_DOCX As Long = 16
Private Const WD_EXPORT_PDF As Long = 17
Public Sub GenerateReportsFromApprovedTemplate()
Dim wb As Workbook
Dim dataWs As Worksheet
Dim templatePath As String
Dim outputPath As String
Dim runFolder As String
Dim reportRows As Collection
Dim reportIds As Collection
Dim reportTitles As Collection
Dim reportingPeriods As Collection
Dim preparedForValues As Collection
Dim preparedByValues As Collection
Dim summaryValues As Collection
Dim findingsValues As Collection
Dim recommendationValues As Collection
Dim outputNameValues As Collection
Dim stagedPaths As Collection
Dim finalPaths As Collection
Dim outputNames As Object
Dim reportIdRows As Object
Dim lastRow As Long
Dim rowNumber As Long
Dim readyCount As Long
Dim reportId As String
Dim reportTitle As String
Dim reportingPeriod As String
Dim preparedFor As String
Dim preparedBy As String
Dim summaryText As String
Dim findingsText As String
Dim recommendationsText As String
Dim outputName As String
Dim recordStatus As String
Dim wdApp As Object
Dim doc As Object
Dim itemNumber As Long
Dim errorMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 3700, , _
"Save the workbook as .xlsm before generating reports."
End If
Set dataWs = RequireWorksheet(wb, DATA_SHEET)
ValidateHeaders dataWs
templatePath = wb.Path & TEMPLATE_FOLDER & "\" & TEMPLATE_FILE
outputPath = wb.Path & OUTPUT_FOLDER
RequireFileEntry templatePath, "Approved report template"
Set reportRows = New Collection
Set reportIds = New Collection
Set reportTitles = New Collection
Set reportingPeriods = New Collection
Set preparedForValues = New Collection
Set preparedByValues = New Collection
Set summaryValues = New Collection
Set findingsValues = New Collection
Set recommendationValues = New Collection
Set outputNameValues = New Collection
Set outputNames = CreateObject("Scripting.Dictionary")
outputNames.CompareMode = vbTextCompare
Set reportIdRows = CreateObject("Scripting.Dictionary")
reportIdRows.CompareMode = vbTextCompare
lastRow = LastUsedRow(dataWs, 11)
For rowNumber = FIRST_DATA_ROW To lastRow
If WorksheetFunction.CountA(dataWs.Range( _
dataWs.Cells(rowNumber, 1), dataWs.Cells(rowNumber, 11))) = 0 Then
GoTo NextRow
End If
recordStatus = ReadRequiredText(dataWs.Cells(rowNumber, 10), _
"RecordStatus row " & rowNumber)
ValidateExactValue recordStatus, Array(STATUS_READY, STATUS_HOLD), _
"RecordStatus row " & rowNumber
If recordStatus = STATUS_HOLD Then GoTo NextRow
reportId = ReadRequiredText(dataWs.Cells(rowNumber, 1), _
"ReportID row " & rowNumber)
reportTitle = ReadRequiredText(dataWs.Cells(rowNumber, 2), _
"ReportTitle row " & rowNumber)
reportingPeriod = ReadRequiredText(dataWs.Cells(rowNumber, 3), _
"ReportingPeriodDisplay row " & rowNumber)
preparedFor = ReadRequiredText(dataWs.Cells(rowNumber, 4), _
"PreparedFor row " & rowNumber)
preparedBy = ReadRequiredText(dataWs.Cells(rowNumber, 5), _
"PreparedBy row " & rowNumber)
summaryText = ReadRequiredText(dataWs.Cells(rowNumber, 6), _
"SummaryText row " & rowNumber)
findingsText = ReadRequiredText(dataWs.Cells(rowNumber, 7), _
"FindingsText row " & rowNumber)
recommendationsText = ReadRequiredText(dataWs.Cells(rowNumber, 8), _
"RecommendationsText row " & rowNumber)
outputName = ReadRequiredText(dataWs.Cells(rowNumber, 9), _
"OutputName row " & rowNumber)
RequireStringValue dataWs.Cells(rowNumber, 1), _
"ReportID row " & rowNumber
RequireStringValue dataWs.Cells(rowNumber, 3), _
"ReportingPeriodDisplay row " & rowNumber
If reportIdRows.Exists(reportId) Then
Err.Raise vbObjectError + 3704, , _
"Duplicate ReportID on READY rows " & _
reportIdRows(reportId) & " and " & rowNumber & _
": " & reportId
End If
reportIdRows.Add reportId, rowNumber
ValidateReplacementText reportTitle, "ReportTitle", rowNumber, 500
ValidateReplacementText reportingPeriod, _
"ReportingPeriodDisplay", rowNumber, 200
ValidateReplacementText preparedFor, "PreparedFor", rowNumber, 500
ValidateReplacementText preparedBy, "PreparedBy", rowNumber, 500
ValidateReplacementText summaryText, "SummaryText", rowNumber, 5000
ValidateReplacementText findingsText, "FindingsText", rowNumber, 5000
ValidateReplacementText recommendationsText, _
"RecommendationsText", rowNumber, 5000
ValidateFileStem outputName, "OutputName row " & rowNumber
If outputNames.Exists(outputName) Then
Err.Raise vbObjectError + 3701, , _
"Duplicate OutputName on rows " & outputNames(outputName) & _
" and " & rowNumber & ": " & outputName
End If
outputNames.Add outputName, rowNumber
ValidateOutputPair outputPath, outputName, _
"report row " & rowNumber
reportRows.Add rowNumber
reportIds.Add reportId
reportTitles.Add reportTitle
reportingPeriods.Add reportingPeriod
preparedForValues.Add preparedFor
preparedByValues.Add preparedBy
summaryValues.Add summaryText
findingsValues.Add findingsText
recommendationValues.Add recommendationsText
outputNameValues.Add outputName
readyCount = readyCount + 1
If readyCount > MAX_REPORTS_PER_RUN Then
Err.Raise vbObjectError + 3702, , _
"This example allows at most " & MAX_REPORTS_PER_RUN & _
" READY reports per run."
End If
NextRow:
Next rowNumber
If reportRows.Count = 0 Then
Err.Raise vbObjectError + 3703, , _
"No READY report rows were found."
End If
EnsureFolder outputPath
runFolder = CreateRunFolder(outputPath)
Set stagedPaths = New Collection
Set finalPaths = New Collection
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = WD_ALERTS_NONE
For itemNumber = 1 To reportRows.Count
rowNumber = CLng(reportRows(itemNumber))
Set doc = wdApp.Documents.Add(templatePath, False, 0, False)
ValidateBookmarks doc, Array("ReportID", "ReportTitle", _
"ReportingPeriodDisplay", "PreparedFor", "PreparedBy", _
"SummaryText", "FindingsText", "RecommendationsText")
WriteRequiredBookmark doc, "ReportID", _
CStr(reportIds(itemNumber))
WriteRequiredBookmark doc, "ReportTitle", _
CStr(reportTitles(itemNumber))
WriteRequiredBookmark doc, "ReportingPeriodDisplay", _
CStr(reportingPeriods(itemNumber))
WriteRequiredBookmark doc, "PreparedFor", _
CStr(preparedForValues(itemNumber))
WriteRequiredBookmark doc, "PreparedBy", _
CStr(preparedByValues(itemNumber))
WriteRequiredBookmark doc, "SummaryText", _
CStr(summaryValues(itemNumber))
WriteRequiredBookmark doc, "FindingsText", _
CStr(findingsValues(itemNumber))
WriteRequiredBookmark doc, "RecommendationsText", _
CStr(recommendationValues(itemNumber))
StageDocument doc, runFolder, Format$(rowNumber, "000000"), _
CStr(outputNameValues(itemNumber)), outputPath, _
stagedPaths, finalPaths
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
Next itemNumber
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
CommitStagedFiles stagedPaths, finalPaths
RemoveRunFolder runFolder
MsgBox readyCount & " reports generated from " & TEMPLATE_FILE & ".", _
vbInformation, "Recurring report generation"
Exit Sub
CleanFail:
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.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If Len(runFolder) > 0 Then RemoveRunFolder runFolder
On Error GoTo 0
MsgBox "Generation stopped: " & errorMessage & vbCrLf & _
"Review the final folder for any files already moved during commit.", _
vbCritical, "Recurring report generation"
End Sub
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(sheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 3710, , _
"Required worksheet not found: " & sheetName
End If
End Function
Private Sub ValidateHeaders(ByVal ws As Worksheet)
Dim expected As Variant
Dim columnNumber As Long
Dim actualText As String
expected = Array("ReportID", "ReportTitle", _
"ReportingPeriodDisplay", "PreparedFor", "PreparedBy", _
"SummaryText", "FindingsText", "RecommendationsText", _
"OutputName", "RecordStatus", "Notes")
For columnNumber = LBound(expected) To UBound(expected)
actualText = CStr(ws.Cells(1, columnNumber + 1).Value2)
If StrComp(actualText, CStr(expected(columnNumber)), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3711, , _
"Header " & ws.Cells(1, columnNumber + 1).Address(False, False) & _
" must match exactly: " & expected(columnNumber)
End If
Next columnNumber
End Sub
Private Function LastUsedRow(ByVal ws As Worksheet, _
ByVal lastColumn As Long) As Long
Dim lastCell As Range
Set lastCell = ws.Range(ws.Cells(1, 1), _
ws.Cells(ws.Rows.Count, lastColumn)).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 + 3712, , _
"The ReportRuns sheet is empty."
End If
LastUsedRow = lastCell.Row
End Function
Private Function ReadRequiredText(ByVal sourceCell As Range, _
ByVal fieldLabel As String) As String
Dim rawValue As Variant
rawValue = sourceCell.Value2
If IsError(rawValue) Then
Err.Raise vbObjectError + 3713, , _
fieldLabel & " contains an Excel error."
End If
ReadRequiredText = CStr(rawValue)
If Len(ReadRequiredText) = 0 Then
Err.Raise vbObjectError + 3714, , _
fieldLabel & " is required."
End If
If StrComp(ReadRequiredText, Trim$(ReadRequiredText), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3715, , _
fieldLabel & " has leading or trailing spaces."
End If
If ContainsControlCharacter(ReadRequiredText) Then
Err.Raise vbObjectError + 3716, , _
fieldLabel & " contains a control character."
End If
End Function
Private Sub RequireStringValue(ByVal sourceCell As Range, _
ByVal fieldLabel As String)
Dim rawValue As Variant
rawValue = sourceCell.Value2
If IsError(rawValue) Then Exit Sub
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 3717, , _
fieldLabel & " must be stored as text."
End If
End Sub
Private Sub ValidateExactValue(ByVal actualValue As String, _
ByVal allowedValues As Variant, _
ByVal fieldLabel As String)
Dim item As Variant
For Each item In allowedValues
If StrComp(actualValue, CStr(item), vbBinaryCompare) = 0 Then Exit Sub
Next item
Err.Raise vbObjectError + 3718, , _
fieldLabel & " has an unsupported value: " & actualValue
End Sub
Private Sub ValidateReplacementText(ByVal textValue As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long, _
ByVal maximumLength As Long)
If Len(textValue) > maximumLength Then
Err.Raise vbObjectError + 3720, , _
fieldName & " exceeds " & maximumLength & _
" characters on row " & rowNumber & "."
End If
If InStr(1, textValue, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, textValue, vbLf, vbBinaryCompare) > 0 Or _
InStr(1, textValue, vbTab, vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 3721, , _
fieldName & " contains a tab or line break on row " & _
rowNumber & ". This example supports one paragraph per bookmark."
End If
End Sub
Private Sub ValidateBookmarks(ByVal doc As Object, _
ByVal bookmarkNames As Variant)
Dim item As Variant
For Each item In bookmarkNames
If Not doc.Bookmarks.Exists(CStr(item)) Then
Err.Raise vbObjectError + 3722, , _
"Approved template is missing bookmark: " & item
End If
Next item
End Sub
Private Sub WriteRequiredBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim savedStyle As Variant
If Len(replacementText) = 0 Then
Err.Raise vbObjectError + 3723, , _
"Required bookmark value is blank: " & bookmarkName
End If
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 + 3724, , _
"Bookmark contains a field, content control, or image: " & _
bookmarkName
End If
savedStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = savedStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3725, , _
"Bookmark was not restored: " & bookmarkName
End If
If Len(Trim$(doc.Bookmarks(bookmarkName).Range.Text)) = 0 Then
Err.Raise vbObjectError + 3726, , _
"Bookmark is empty after replacement: " & bookmarkName
End If
End Sub
Private Sub StageDocument(ByVal doc As Object, _
ByVal runFolder As String, _
ByVal stageKey As String, _
ByVal outputName As String, _
ByVal finalFolder As String, _
ByVal stagedPaths As Collection, _
ByVal finalPaths As Collection)
Dim stagedDocx As String
Dim stagedPdf As String
Dim finalDocx As String
Dim finalPdf As String
stagedDocx = runFolder & "\" & stageKey & ".docx"
stagedPdf = runFolder & "\" & stageKey & ".pdf"
finalDocx = finalFolder & "\" & outputName & ".docx"
finalPdf = finalFolder & "\" & outputName & ".pdf"
doc.SaveAs2 stagedDocx, WD_FORMAT_DOCX
doc.ExportAsFixedFormat stagedPdf, WD_EXPORT_PDF
If Not FileExistsChecked(stagedDocx) Or _
Not FileExistsChecked(stagedPdf) Then
Err.Raise vbObjectError + 3730, , _
"Word did not create both staged files for " & outputName & "."
End If
stagedPaths.Add stagedDocx
finalPaths.Add finalDocx
stagedPaths.Add stagedPdf
finalPaths.Add finalPdf
End Sub
Private Sub CommitStagedFiles(ByVal stagedPaths As Collection, _
ByVal finalPaths As Collection)
Dim itemNumber As Long
Dim sourcePath As String
Dim destinationPath As String
If stagedPaths.Count <> finalPaths.Count Then
Err.Raise vbObjectError + 3731, , _
"Internal staged-file mapping is inconsistent."
End If
For itemNumber = 1 To finalPaths.Count
If FileExistsChecked(CStr(finalPaths(itemNumber))) Then
Err.Raise vbObjectError + 3732, , _
"Final file appeared during generation; nothing was overwritten: " & _
finalPaths(itemNumber)
End If
If Not FileExistsChecked(CStr(stagedPaths(itemNumber))) Then
Err.Raise vbObjectError + 3733, , _
"Staged file is missing: " & stagedPaths(itemNumber)
End If
Next itemNumber
For itemNumber = 1 To finalPaths.Count
sourcePath = CStr(stagedPaths(itemNumber))
destinationPath = CStr(finalPaths(itemNumber))
Name sourcePath As destinationPath
Next itemNumber
End Sub
Private Sub ValidateOutputPair(ByVal folderPath As String, _
ByVal outputName As String, _
ByVal outputLabel As String)
If FileExistsChecked(folderPath & "\" & outputName & ".docx") Or _
FileExistsChecked(folderPath & "\" & outputName & ".pdf") Then
Err.Raise vbObjectError + 3734, , _
"Output already exists for " & outputLabel & ": " & outputName
End If
End Sub
Private Sub ValidateFileStem(ByVal fileStem As String, _
ByVal fieldLabel As String)
Dim forbidden As Variant
Dim item As Variant
Dim baseName As String
Dim lowerStem As String
If Len(fileStem) = 0 Or Len(fileStem) > 120 Then
Err.Raise vbObjectError + 3740, , _
fieldLabel & " must contain 1 to 120 characters."
End If
If StrComp(fileStem, Trim$(fileStem), vbBinaryCompare) <> 0 Or _
Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
Err.Raise vbObjectError + 3741, , _
fieldLabel & " has unsafe outer characters."
End If
forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
For Each item In forbidden
If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 3742, , _
fieldLabel & " contains a forbidden Windows character."
End If
Next item
If ContainsControlCharacter(fileStem) Then
Err.Raise vbObjectError + 3743, , _
fieldLabel & " contains a Windows control character."
End If
lowerStem = LCase$(fileStem)
If Right$(lowerStem, 5) = ".docx" Or _
Right$(lowerStem, 4) = ".pdf" Then
Err.Raise vbObjectError + 3744, , _
fieldLabel & " must be a filename stem without .docx or .pdf."
End If
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 + 3745, , _
fieldLabel & " starts with a reserved Windows device name."
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 Function ContainsControlCharacter(ByVal textValue As String) As Boolean
Dim position As Long
Dim characterCode As Long
For position = 1 To Len(textValue)
characterCode = AscW(Mid$(textValue, position, 1))
If characterCode >= 0 And characterCode < 32 Then
ContainsControlCharacter = True
Exit Function
End If
Next position
End Function
Private Sub RequireFileEntry(ByVal filePath As String, _
ByVal fileLabel As String)
Dim foundName As String
Dim attributes As Long
On Error GoTo CheckFailed
foundName = Dir$(filePath, FILE_ATTRIBUTES)
If Len(foundName) = 0 Then
Err.Raise vbObjectError + 3750, , _
fileLabel & " was not found: " & filePath
End If
attributes = GetAttr(filePath)
If (attributes And vbDirectory) = vbDirectory Then
Err.Raise vbObjectError + 3751, , _
fileLabel & " is a folder, not a file: " & filePath
End If
Exit Sub
CheckFailed:
If Err.Number >= vbObjectError + 3750 And _
Err.Number <= vbObjectError + 3751 Then
Err.Raise Err.Number, , Err.Description
End If
Err.Raise vbObjectError + 3752, , _
"Could not check " & fileLabel & ": " & filePath & _
". " & Err.Description
End Sub
Private Function FileExistsChecked(ByVal filePath As String) As Boolean
Dim foundName As String
Dim attributes As Long
On Error GoTo CheckFailed
foundName = Dir$(filePath, FILE_ATTRIBUTES)
If Len(foundName) = 0 Then
FileExistsChecked = False
Exit Function
End If
attributes = GetAttr(filePath)
If (attributes And vbDirectory) = vbDirectory Then
Err.Raise vbObjectError + 3753, , _
"Expected a file but found a folder: " & filePath
End If
FileExistsChecked = True
Exit Function
CheckFailed:
Err.Raise vbObjectError + 3754, , _
"Could not check file path: " & filePath & ". " & Err.Description
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 + 3755, , _
"Expected a folder but found a file: " & folderPath
End If
End If
End Sub
Private Function CreateRunFolder(ByVal outputPath As String) As String
Dim attempt As Long
Dim candidate As String
For attempt = 0 To 99
candidate = outputPath & "\_run_" & _
Format$(Now, "yyyymmdd_hhnnss") & "_" & Format$(attempt, "00")
If Len(Dir$(candidate, vbDirectory)) = 0 Then
MkDir candidate
CreateRunFolder = candidate
Exit Function
End If
Next attempt
Err.Raise vbObjectError + 3756, , _
"Could not create a unique temporary run folder."
End Function
Private Sub RemoveRunFolder(ByVal runFolder As String)
Dim itemName As String
If Len(runFolder) = 0 Then Exit Sub
itemName = Dir$(runFolder & "\*", FILE_ATTRIBUTES)
Do While Len(itemName) > 0
If itemName <> "." And itemName <> ".." Then
If (GetAttr(runFolder & "\" & itemName) And vbDirectory) = 0 Then
Kill runFolder & "\" & itemName
End If
End If
itemName = Dir$()
Loop
RmDir runFolder
End SubOfficial Word VBA references: Bookmarks.Add and SaveAs2.
Know what this bounded example does not solve
Multi-paragraph sections
Tabs and line breaks are rejected. Use a separately tested structured-section method when report content needs several paragraphs, tables, or lists.
Template authentication
The macro checks the configured file and bookmarks, but it does not prove that the DOCX is the approved release.
Atomic batch commit
Files move sequentially. If commit is interrupted, review the final folder before rerunning.
Business approval
READY controls processing only. It does not prove that source facts, conclusions, or distribution are approved.
Use Factory for the repeating one-template report pattern
DocxForge Pro Factory matches this workflow shape: many independent Excel rows, one standard Word template, and one separate output for each selected row.


Review each reporting cycle as a controlled release
Source identity
Confirm ReportID, reporting period, recipient, preparer, and READY status.
Content approval
Review summary, findings, and recommendations against the approved source evidence.
Template release
Record which approved template version produced the reports.
Formatting and pagination
Inspect long values, page breaks, headings, headers, footers, and PDF pagination.
Output reconciliation
Compare the READY-row count with the DOCX and PDF inventory.
Distribution and retention
Apply the separate approval, access, delivery, and retention process after generation.
Frequently asked questions
Why is copying last period’s report risky?
The copied file can retain an old period, recipient, conclusion, hidden field, header, footer, link, comment, or manual edit. A fresh document from the approved template starts from the intended fixed content instead of the previous report instance.
What does one Excel row represent in this example?
Each READY row represents one independent report instance. It contains the report identity, display-ready period, recipient and author, three single-paragraph content fields, and one output filename stem.
Can SummaryText, FindingsText, and RecommendationsText contain multiple paragraphs?
Not in this bounded example. The preflight rejects tabs and line breaks because direct bookmark replacement is intended for one paragraph per field. Use a separately tested content-control or structured-section method for multi-paragraph content.
Does the macro overwrite existing reports?
No during the normal single-process workflow. It checks DOCX and PDF destinations before Word starts, stages all files, rechecks final destinations, and uses moves that fail when a destination exists. The final multi-file commit is sequential, so inspect the folder if it is interrupted.
Does the VBA verify that the template is the approved release?
No. It checks the configured filename and required bookmarks, but it does not authenticate the DOCX content. Keep the approved template in a controlled location and apply your normal release and access controls.
Where does DocxForge Pro Factory fit?
Factory fits this exact shape: many independent Excel rows, one standard Word template, and a separate output for each selected row. Use a different workflow when one case needs several different documents from different templates.