Shared Drive Templates vs Managed Output Folders in Batch Generation
Use the shared drive as a controlled source for approved templates and a separate managed folder as the workspace and evidence boundary for every document batch.
Quick answer: use both locations, but give them different jobs
A shared drive is useful for one approved template source. It is a poor live workspace for temporary files, partially completed outputs, retries, and concurrent batches. At the start of a run, copy the approved template into an isolated batch folder. Generate against that snapshot, move verified pairs into Completed, and retain a manifest in Evidence.
Shared template repository → isolated run snapshot → Staging → Completed → review → controlled archive or publication.
Compare the two folder roles
Shared template repository
Owns approved Word templates, release discipline, access control, and a clear source path. Operators should read from it, not mix generated files into it.
Managed output root
Owns run-specific folders, temporary files, completed DOCX/PDF pairs, manifests, review evidence, and restart decisions.
What should be shared
The approved template, documented release identity, required bookmarks, and the policy for selecting the current version.
What should be isolated
Each batch’s snapshot, filenames, staging state, completed files, manifest, operator review, and failure evidence.
Avoid four common shared-folder failures
Template changes during the run
Different records can be created from different template states if Word reopens the live shared file for every row. Use one snapshot for the complete run.
Partial files look complete
A DOCX or PDF written directly into a shared release folder can become visible before its pair and review evidence are ready.
Two writers collide
Timestamp-only folder names and repeated output names can still collide. Use a unique run folder and reject duplicate RecordID and OutputName values before Word starts.
Retries erase the evidence
Automatic cleanup can remove the files needed to diagnose a failure. Retain staging and completed files until an operator decides what to archive or discard.
Define one managed folder contract
Batch root
Create a unique Batch_YYYYMMDD_HHMMSS_ID folder under an approved output root that is separate from the template repository.
Staging
Word writes the current DOCX and PDF here. A pair moves forward only after both files exist and have nonzero size.
Completed
Contains committed output pairs for the current run. Filenames come from validated unique OutputName values.
Evidence
Contains the exact template snapshot and a manifest recording the source, operator release label, file metadata, workbook, and PENDING and COMPLETED pair records.
Review boundary
Completed does not mean approved for external release. Add the organization’s required visual, data, privacy, and authorization checks.
Archive boundary
Move or copy the reviewed batch through a controlled publication step. Do not make the generator silently delete or reorganize evidence.
Prepare the setup and data sheets
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). The example uses exact labels and text values rather than silently trimming or normalizing the source.
BatchSetup
Put ApprovedTemplatePath in A2, TemplateReleaseID in A3, and ManagedOutputRoot in A4. Enter their exact text values in B2:B4.
Example paths
Template: \\SERVER\DocumentTemplates\Approved\StandardLetter.docx. Output root: D:\ManagedBatchOutput or a separately controlled share.
BatchData A–C
A — RecordID; B — CustomerName; C — Address.
BatchData D–F
D — DocumentDateDisplay; E — OutputName; F — RecordStatus. Use exactly READY, HOLD, or DONE.
The Word file must contain RecordID, CustomerName, Address, and DocumentDateDisplay. Keep each bookmark outside fields, content controls, and images.
Run the batch through six controlled stages
1. Validate setup
Confirm absolute paths, a nonempty DOCX template, an existing output root, and no overlap between template and output trees.
2. Preflight all rows
Validate exact status values, required text, unique RecordID, unique OutputName, Windows-safe names, and no existing run output.
3. Snapshot the template
Copy the source into Evidence without overwrite, compare its size, and confirm the source metadata did not change during the copy.
4. Validate the snapshot
Start Word once, create a probe document from the snapshot, and confirm every required bookmark before the first record runs.
5. Generate staged pairs
Recheck each READY row against the preflight snapshot, create DOCX and PDF in Staging, validate both, then move them into Completed.
6. Append the manifest
Write PENDING before the move and COMPLETED afterward. A final COMPLETE row appears only after the full selected batch finishes.
Free Excel VBA helper: snapshot, stage, complete, and record
Run GenerateManagedBatchFromSharedTemplate from the saved .xlsm workbook. The macro uses one template snapshot for all READY rows, creates separate DOCX/PDF files, and leaves run evidence in place if anything stops.
Stable template input
The live shared template is copied once. Every Word document is created from the run snapshot, not reopened from the network source.
Exact source contract
Headers, statuses, text types, whitespace, identifiers, and filenames are checked before Word starts.
Current-row protection
Immediately before generation, all six cells are re-read and compared by data type and exact value with the preflight snapshot.
Safe bookmark replacement
Each required bookmark rejects overlapping fields, controls, and images, preserves its style, and is restored after replacement.
Pair commit
Both staged files must be nonempty. The macro then rechecks final targets and moves the DOCX and PDF separately into Completed.
Persistent manifest
The Unicode CSV records PENDING before each move and COMPLETED afterward. After a failure, reconcile both states with the actual files in Completed.
Option Explicit
Private Const wdFormatXMLDocument As Long = 12
Private Const wdExportFormatPDF As Long = 17
Private Const wdExportOptimizeForPrint As Long = 0
Private Const wdDoNotSaveChanges As Long = 0
Private Const wdAlertsNone As Long = 0
Private Const FOR_APPENDING As Long = 8
Private Const TRISTATE_TRUE As Long = -1
Public Sub GenerateManagedBatchFromSharedTemplate()
Const SETUP_SHEET As String = "BatchSetup"
Const DATA_SHEET As String = "BatchData"
Const READY_STATUS As String = "READY"
Const HOLD_STATUS As String = "HOLD"
Const DONE_STATUS As String = "DONE"
Dim setupWs As Worksheet
Dim dataWs As Worksheet
Dim fso As Object
Dim sourceTemplateFile As Object
Dim sourceTemplateFileAfterCopy As Object
Dim approvedTemplatePath As String
Dim templateReleaseId As String
Dim managedOutputRoot As String
Dim templateFolder As String
Dim sourceTemplateSize As Double
Dim sourceTemplateModified As Date
Dim runId As String
Dim runFolder As String
Dim stagingFolder As String
Dim completedFolder As String
Dim evidenceFolder As String
Dim snapshotTemplatePath As String
Dim manifestPath As String
Dim lastCell As Range
Dim lastDataRow As Long
Dim batchData As Variant
Dim readyRows() As Long
Dim readyRecordIds() As String
Dim readyCustomerNames() As String
Dim readyAddresses() As String
Dim readyDates() As String
Dim readyOutputNames() As String
Dim readyCount As Long
Dim dataRow As Long
Dim excelRow As Long
Dim recordId As String
Dim customerName As String
Dim addressText As String
Dim documentDateText As String
Dim outputName As String
Dim recordStatus As String
Dim recordIds As Object
Dim outputNames As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim processedCount As Long
Dim previousScreenUpdating As Boolean
Dim screenStateCaptured As Boolean
Dim currentStep As String
Dim errorNumber As Long
Dim errorDescription As String
On Error GoTo CleanFail
previousScreenUpdating = Application.ScreenUpdating
screenStateCaptured = True
Application.ScreenUpdating = False
currentStep = "checking the workbook and setup sheets"
If Len(ThisWorkbook.Path) = 0 Then
Err.Raise vbObjectError + 4500, , _
"Save this workbook as a macro-enabled file before running the batch."
End If
Set setupWs = ThisWorkbook.Worksheets(SETUP_SHEET)
Set dataWs = ThisWorkbook.Worksheets(DATA_SHEET)
Set fso = CreateObject("Scripting.FileSystemObject")
RequireSetupLabel setupWs, 2, "ApprovedTemplatePath"
RequireSetupLabel setupWs, 3, "TemplateReleaseID"
RequireSetupLabel setupWs, 4, "ManagedOutputRoot"
approvedTemplatePath = ReadRequiredCellText(setupWs.Cells(2, 2), _
"ApprovedTemplatePath", 2)
templateReleaseId = ReadRequiredCellText(setupWs.Cells(3, 2), _
"TemplateReleaseID", 3)
managedOutputRoot = ReadRequiredCellText(setupWs.Cells(4, 2), _
"ManagedOutputRoot", 4)
ValidateConfiguredPath approvedTemplatePath, "ApprovedTemplatePath"
ValidateIdentifier templateReleaseId, "TemplateReleaseID", 3
ValidateConfiguredPath managedOutputRoot, "ManagedOutputRoot"
If Not IsAbsolutePath(approvedTemplatePath) Then
Err.Raise vbObjectError + 4501, , _
"ApprovedTemplatePath must be an absolute UNC or drive path."
End If
If Not IsAbsolutePath(managedOutputRoot) Then
Err.Raise vbObjectError + 4502, , _
"ManagedOutputRoot must be an absolute UNC or drive path."
End If
If LCase$(Right$(approvedTemplatePath, 5)) <> ".docx" Then
Err.Raise vbObjectError + 4503, , _
"ApprovedTemplatePath must point to a .docx file."
End If
RequireExistingFile approvedTemplatePath, "approved Word template"
RequireExistingFolder managedOutputRoot, "managed output root"
templateFolder = fso.GetParentFolderName( _
fso.GetAbsolutePathName(approvedTemplatePath))
If PathsOverlap(fso, templateFolder, managedOutputRoot) Then
Err.Raise vbObjectError + 4504, , _
"ManagedOutputRoot must be separate from the template repository."
End If
Set sourceTemplateFile = fso.GetFile(approvedTemplatePath)
sourceTemplateSize = CDbl(sourceTemplateFile.Size)
sourceTemplateModified = sourceTemplateFile.DateLastModified
If sourceTemplateSize <= 0 Then
Err.Raise vbObjectError + 4505, , _
"The approved Word template is empty: " & approvedTemplatePath
End If
RequireHeader dataWs, 1, "RecordID"
RequireHeader dataWs, 2, "CustomerName"
RequireHeader dataWs, 3, "Address"
RequireHeader dataWs, 4, "DocumentDateDisplay"
RequireHeader dataWs, 5, "OutputName"
RequireHeader dataWs, 6, "RecordStatus"
Set lastCell = dataWs.Range("A:F").Find( _
What:="*", _
After:=dataWs.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 4506, , _
"No rows were found on BatchData."
End If
If lastCell.Row < 2 Then
Err.Raise vbObjectError + 4506, , _
"No rows were found on BatchData."
End If
lastDataRow = lastCell.Row
batchData = dataWs.Range("A2:F" & lastDataRow).Value2
Set recordIds = CreateObject("Scripting.Dictionary")
recordIds.CompareMode = vbTextCompare
Set outputNames = CreateObject("Scripting.Dictionary")
outputNames.CompareMode = vbTextCompare
currentStep = "preflighting BatchData"
For dataRow = 1 To UBound(batchData, 1)
excelRow = dataRow + 1
If Not ArrayRowIsBlank(batchData, dataRow, 6) Then
recordId = ReadRequiredArrayText(batchData(dataRow, 1), _
"RecordID", excelRow)
recordStatus = ReadRequiredArrayText(batchData(dataRow, 6), _
"RecordStatus", excelRow)
ValidateIdentifier recordId, "RecordID", excelRow
ValidateRecordStatus recordStatus, excelRow, READY_STATUS, _
HOLD_STATUS, DONE_STATUS
If recordIds.Exists(recordId) Then
Err.Raise vbObjectError + 4507, , _
"Duplicate RecordID on BatchData rows " & _
recordIds(recordId) & " and " & excelRow & ": " & recordId
End If
recordIds.Add recordId, excelRow
If StrComp(recordStatus, READY_STATUS, vbBinaryCompare) = 0 Then
customerName = ReadRequiredArrayText( _
batchData(dataRow, 2), "CustomerName", excelRow)
addressText = ReadRequiredArrayText( _
batchData(dataRow, 3), "Address", excelRow)
documentDateText = ReadRequiredArrayText( _
batchData(dataRow, 4), "DocumentDateDisplay", excelRow)
outputName = ReadRequiredArrayText( _
batchData(dataRow, 5), "OutputName", excelRow)
ValidateFileStem outputName, "OutputName", excelRow
If outputNames.Exists(outputName) Then
Err.Raise vbObjectError + 4508, , _
"Duplicate OutputName on BatchData rows " & _
outputNames(outputName) & " and " & excelRow & _
": " & outputName
End If
outputNames.Add outputName, excelRow
readyCount = readyCount + 1
ReDim Preserve readyRows(1 To readyCount)
ReDim Preserve readyRecordIds(1 To readyCount)
ReDim Preserve readyCustomerNames(1 To readyCount)
ReDim Preserve readyAddresses(1 To readyCount)
ReDim Preserve readyDates(1 To readyCount)
ReDim Preserve readyOutputNames(1 To readyCount)
readyRows(readyCount) = dataRow
readyRecordIds(readyCount) = recordId
readyCustomerNames(readyCount) = customerName
readyAddresses(readyCount) = addressText
readyDates(readyCount) = documentDateText
readyOutputNames(readyCount) = outputName
End If
End If
Next dataRow
If readyCount = 0 Then
Err.Raise vbObjectError + 4509, , _
"No BatchData rows are marked exactly READY."
End If
currentStep = "creating an isolated batch workspace"
runId = CreateRunId()
runFolder = CreateRunFolder(fso, managedOutputRoot, runId)
stagingFolder = JoinPath(runFolder, "Staging")
completedFolder = JoinPath(runFolder, "Completed")
evidenceFolder = JoinPath(runFolder, "Evidence")
fso.CreateFolder stagingFolder
fso.CreateFolder completedFolder
fso.CreateFolder evidenceFolder
snapshotTemplatePath = JoinPath(evidenceFolder, _
"TemplateSnapshot.docx")
manifestPath = JoinPath(evidenceFolder, "BatchManifest.csv")
currentStep = "copying the approved template snapshot"
fso.CopyFile approvedTemplatePath, snapshotTemplatePath, False
RequireNonemptyFile snapshotTemplatePath, "template snapshot"
If CDbl(fso.GetFile(snapshotTemplatePath).Size) <> _
sourceTemplateSize Then
Err.Raise vbObjectError + 4510, , _
"Template snapshot size differs from the approved source."
End If
Set sourceTemplateFileAfterCopy = fso.GetFile(approvedTemplatePath)
If CDbl(sourceTemplateFileAfterCopy.Size) <> sourceTemplateSize Or _
CDbl(sourceTemplateFileAfterCopy.DateLastModified) <> _
CDbl(sourceTemplateModified) Then
Err.Raise vbObjectError + 4511, , _
"The approved template changed while its snapshot was copied."
End If
CreateManifest fso, manifestPath, runId, templateReleaseId, _
approvedTemplatePath, sourceTemplateSize, _
sourceTemplateModified, snapshotTemplatePath, ThisWorkbook.FullName
currentStep = "starting Microsoft Word"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone
wdApp.ScreenUpdating = False
currentStep = "validating the template snapshot"
Set wdDoc = wdApp.Documents.Add(snapshotTemplatePath, False, 0, False)
ValidateTemplateBookmarks wdDoc
wdDoc.Close wdDoNotSaveChanges
Set wdDoc = Nothing
For dataRow = 1 To readyCount
excelRow = readyRows(dataRow) + 1
currentStep = "generating READY row " & excelRow & _
" (" & dataRow & " of " & readyCount & ")"
RequireBatchRowUnchanged dataWs, excelRow, batchData, _
readyRows(dataRow)
recordId = readyRecordIds(dataRow)
customerName = readyCustomerNames(dataRow)
addressText = readyAddresses(dataRow)
documentDateText = readyDates(dataRow)
outputName = readyOutputNames(dataRow)
tempDocxPath = JoinPath(stagingFolder, outputName & ".docx")
tempPdfPath = JoinPath(stagingFolder, outputName & ".pdf")
finalDocxPath = JoinPath(completedFolder, outputName & ".docx")
finalPdfPath = JoinPath(completedFolder, outputName & ".pdf")
RequireTargetAbsent tempDocxPath
RequireTargetAbsent tempPdfPath
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
Set wdDoc = wdApp.Documents.Add(snapshotTemplatePath, False, 0, False)
WriteRequiredBookmark wdDoc, "RecordID", recordId
WriteRequiredBookmark wdDoc, "CustomerName", customerName
WriteRequiredBookmark wdDoc, "Address", addressText
WriteRequiredBookmark wdDoc, "DocumentDateDisplay", _
documentDateText
wdDoc.SaveAs2 tempDocxPath, wdFormatXMLDocument, False, "", False
wdDoc.ExportAsFixedFormat tempPdfPath, wdExportFormatPDF, False, _
wdExportOptimizeForPrint
wdDoc.Close wdDoNotSaveChanges
Set wdDoc = Nothing
RequireNonemptyFile tempDocxPath, "staged DOCX"
RequireNonemptyFile tempPdfPath, "staged PDF"
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
AppendManifestPending fso, manifestPath, runId, recordId, _
outputName, templateReleaseId, finalDocxPath, finalPdfPath
Name tempDocxPath As finalDocxPath
Name tempPdfPath As finalPdfPath
AppendManifestDocument fso, manifestPath, runId, recordId, _
outputName, templateReleaseId, finalDocxPath, finalPdfPath
processedCount = processedCount + 1
Next dataRow
If Not wdApp Is Nothing Then wdApp.NormalTemplate.Saved = True
wdApp.Quit wdDoNotSaveChanges
Set wdApp = Nothing
RemoveEmptyFolder stagingFolder
AppendManifestComplete fso, manifestPath, runId, processedCount
MsgBox "Batch completed with " & processedCount & _
" document pair(s)." & vbCrLf & _
"Run folder: " & runFolder, vbInformation
CleanExit:
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit wdDoNotSaveChanges
End If
If screenStateCaptured Then Application.ScreenUpdating = _
previousScreenUpdating
Set wdDoc = Nothing
Set wdApp = Nothing
Set recordIds = Nothing
Set outputNames = Nothing
Set sourceTemplateFile = Nothing
Set sourceTemplateFileAfterCopy = Nothing
Set fso = Nothing
On Error GoTo 0
Exit Sub
CleanFail:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit wdDoNotSaveChanges
End If
If screenStateCaptured Then Application.ScreenUpdating = _
previousScreenUpdating
Set wdDoc = Nothing
Set wdApp = Nothing
Set recordIds = Nothing
Set outputNames = Nothing
Set sourceTemplateFile = Nothing
Set sourceTemplateFileAfterCopy = Nothing
Set fso = Nothing
On Error GoTo 0
MsgBox "Batch stopped while " & currentStep & "." & vbCrLf & _
"Error " & errorNumber & ": " & errorDescription & vbCrLf & vbCrLf & _
IIf(Len(runFolder) > 0, _
"Inspect the retained run folder: " & runFolder, _
"No run folder was created.") & vbCrLf & _
"Existing completed files were not deleted.", vbCritical
End Sub
Private Sub RequireSetupLabel(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal expectedLabel As String)
Dim labelValue As Variant
labelValue = ws.Cells(rowNumber, 1).Value2
If IsError(labelValue) Then
Err.Raise vbObjectError + 4512, , _
"BatchSetup label contains an Excel error on row " & _
rowNumber & "."
End If
If VarType(labelValue) <> vbString Then
Err.Raise vbObjectError + 4513, , _
"Expected BatchSetup label '" & expectedLabel & _
"' in A" & rowNumber & "."
End If
If StrComp(CStr(labelValue), expectedLabel, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4513, , _
"Expected BatchSetup label '" & expectedLabel & _
"' in A" & rowNumber & "."
End If
End Sub
Private Sub RequireHeader(ByVal ws As Worksheet, _
ByVal columnNumber As Long, _
ByVal expectedHeader As String)
Dim headerValue As Variant
headerValue = ws.Cells(1, columnNumber).Value2
If IsError(headerValue) Then
Err.Raise vbObjectError + 4514, , _
"Header contains an Excel error on BatchData column " & _
columnNumber & "."
End If
If VarType(headerValue) <> vbString Then
Err.Raise vbObjectError + 4515, , _
"Expected exact header '" & expectedHeader & _
"' in BatchData column " & columnNumber & "."
End If
If StrComp(CStr(headerValue), expectedHeader, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4515, , _
"Expected exact header '" & expectedHeader & _
"' in BatchData column " & columnNumber & "."
End If
End Sub
Private Function ReadRequiredCellText(ByVal sourceCell As Range, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadRequiredCellText = ReadExactTextValue(sourceCell.Value2, _
fieldName, excelRow)
End Function
Private Function ReadRequiredArrayText(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadRequiredArrayText = ReadExactTextValue(sourceValue, _
fieldName, excelRow)
End Function
Private Function ReadExactTextValue(ByVal sourceValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
Dim valueText As String
If IsError(sourceValue) Then
Err.Raise vbObjectError + 4516, , _
fieldName & " contains an Excel error on row " & excelRow & "."
End If
If IsEmpty(sourceValue) Then
Err.Raise vbObjectError + 4517, , _
fieldName & " is required on row " & excelRow & "."
End If
If VarType(sourceValue) <> vbString Then
Err.Raise vbObjectError + 4518, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
valueText = CStr(sourceValue)
If Len(valueText) = 0 Then
Err.Raise vbObjectError + 4517, , _
fieldName & " is required on row " & excelRow & "."
End If
If valueText <> Trim$(valueText) Then
Err.Raise vbObjectError + 4519, , _
fieldName & " has leading or trailing whitespace on row " & _
excelRow & "."
End If
If ContainsControlCharacter(valueText) Then
Err.Raise vbObjectError + 4520, , _
fieldName & " contains a control character on row " & _
excelRow & "."
End If
ReadExactTextValue = valueText
End Function
Private Function ArrayRowIsBlank(ByRef values As Variant, _
ByVal dataRow As Long, _
ByVal columnCount As Long) As Boolean
Dim columnNumber As Long
Dim rawValue As Variant
ArrayRowIsBlank = True
For columnNumber = 1 To columnCount
rawValue = values(dataRow, columnNumber)
If IsError(rawValue) Then
ArrayRowIsBlank = False
Exit Function
End If
If Not IsEmpty(rawValue) Then
If Len(CStr(rawValue)) > 0 Then
ArrayRowIsBlank = False
Exit Function
End If
End If
Next columnNumber
End Function
Private Sub ValidateRecordStatus(ByVal statusValue As String, _
ByVal excelRow As Long, _
ByVal readyValue As String, _
ByVal holdValue As String, _
ByVal doneValue As String)
If StrComp(statusValue, readyValue, vbBinaryCompare) <> 0 And _
StrComp(statusValue, holdValue, vbBinaryCompare) <> 0 And _
StrComp(statusValue, doneValue, vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4521, , _
"RecordStatus must be exactly READY, HOLD, or DONE on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateIdentifier(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
If Len(valueText) > 120 Then
Err.Raise vbObjectError + 4522, , _
fieldName & " exceeds 120 characters on row " & excelRow & "."
End If
If InStr(1, valueText, "<", vbBinaryCompare) > 0 Or _
InStr(1, valueText, ">", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4523, , _
fieldName & " contains an unsupported bracket on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateConfiguredPath(ByVal pathText As String, _
ByVal fieldName As String)
If InStr(1, pathText, "*", vbBinaryCompare) > 0 Or _
InStr(1, pathText, "?", vbBinaryCompare) > 0 Or _
InStr(1, pathText, Chr$(34), vbBinaryCompare) > 0 Or _
InStr(1, pathText, "<", vbBinaryCompare) > 0 Or _
InStr(1, pathText, ">", vbBinaryCompare) > 0 Or _
InStr(1, pathText, "|", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4523, , _
fieldName & " contains a wildcard or forbidden path character."
End If
End Sub
Private Sub ValidateFileStem(ByVal fileStem As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
Dim invalidCharacters As Variant
Dim invalidCharacter As Variant
Dim baseName As String
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", _
"\", "|", "?", "*")
For Each invalidCharacter In invalidCharacters
If InStr(1, fileStem, CStr(invalidCharacter), _
vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 4524, , _
fieldName & " contains a forbidden Windows character on row " & _
excelRow & "."
End If
Next invalidCharacter
If ContainsControlCharacter(fileStem) Then
Err.Raise vbObjectError + 4525, , _
fieldName & " contains a control character on row " & _
excelRow & "."
End If
If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
Err.Raise vbObjectError + 4526, , _
fieldName & " cannot end with a period or space on row " & _
excelRow & "."
End If
If Len(fileStem) > 120 Then
Err.Raise vbObjectError + 4527, , _
fieldName & " exceeds 120 characters on row " & excelRow & "."
End If
baseName = fileStem
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(baseName) Then
Err.Raise vbObjectError + 4528, , _
fieldName & " uses a reserved Windows name on row " & _
excelRow & "."
End If
End Sub
Private Sub RequireBatchRowUnchanged(ByVal dataWs As Worksheet, _
ByVal excelRow As Long, _
ByRef originalData As Variant, _
ByVal originalDataRow As Long)
Dim columnNumber As Long
Dim originalValue As Variant
Dim currentValue As Variant
For columnNumber = 1 To 6
originalValue = originalData(originalDataRow, columnNumber)
currentValue = dataWs.Cells(excelRow, columnNumber).Value2
If Not VariantValuesExactlyEqual(originalValue, currentValue) Then
Err.Raise vbObjectError + 4529, , _
"BatchData row " & excelRow & ", column " & _
columnNumber & " changed after preflight. Run the batch again."
End If
Next columnNumber
End Sub
Private Function VariantValuesExactlyEqual(ByVal originalValue As Variant, _
ByVal currentValue As Variant) As Boolean
If VarType(originalValue) <> VarType(currentValue) Then Exit Function
If IsError(originalValue) Or IsError(currentValue) Then Exit Function
Select Case VarType(originalValue)
Case vbEmpty, vbNull
VariantValuesExactlyEqual = True
Case vbString
VariantValuesExactlyEqual = _
(StrComp(CStr(originalValue), CStr(currentValue), _
vbBinaryCompare) = 0)
Case Else
VariantValuesExactlyEqual = (originalValue = currentValue)
End Select
End Function
Private Sub ValidateTemplateBookmarks(ByVal doc As Object)
RequireBookmark doc, "RecordID"
RequireBookmark doc, "CustomerName"
RequireBookmark doc, "Address"
RequireBookmark doc, "DocumentDateDisplay"
End Sub
Private Sub RequireBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 4530, , _
"Required Word bookmark is missing: " & 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 originalStyle As Variant
RequireBookmark doc, bookmarkName
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 + 4531, , _
"Bookmark overlaps a field, content control, or image: " & _
bookmarkName
End If
originalStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = originalStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 4532, , _
"Word bookmark was not restored: " & bookmarkName
End If
If StrComp(doc.Bookmarks(bookmarkName).Range.Text, replacementText, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 4533, , _
"Bookmark value differs after replacement: " & bookmarkName
End If
End Sub
Private Sub CreateManifest(ByVal fso As Object, _
ByVal manifestPath As String, _
ByVal runId As String, _
ByVal templateReleaseId As String, _
ByVal templateSource As String, _
ByVal templateSize As Double, _
ByVal templateModified As Date, _
ByVal snapshotPath As String, _
ByVal workbookPath As String)
Dim stream As Object
Set stream = fso.CreateTextFile(manifestPath, False, True)
stream.WriteLine "RecordType,RunID,RecordID,OutputName," & _
"TemplateReleaseID,TemplateSource,TemplateSize," & _
"TemplateModified,SnapshotPath,WorkbookPath,DOCXPath," & _
"PDFPath,Status,CompletedCount"
stream.WriteLine CsvField("RUN") & "," & CsvField(runId) & _
",,," & CsvField(templateReleaseId) & "," & _
CsvField(templateSource) & "," & CsvField(CStr(templateSize)) & _
"," & CsvField(Format$(templateModified, _
"yyyy-mm-dd hh:nn:ss")) & "," & CsvField(snapshotPath) & "," & _
CsvField(workbookPath) & ",,," & CsvField("STARTED") & ","
stream.Close
Set stream = Nothing
End Sub
Private Sub AppendManifestPending(ByVal fso As Object, _
ByVal manifestPath As String, _
ByVal runId As String, _
ByVal recordId As String, _
ByVal outputName As String, _
ByVal templateReleaseId As String, _
ByVal intendedDocxPath As String, _
ByVal intendedPdfPath As String)
Dim stream As Object
Set stream = fso.OpenTextFile(manifestPath, FOR_APPENDING, _
False, TRISTATE_TRUE)
stream.WriteLine CsvField("DOCUMENT") & "," & CsvField(runId) & _
"," & CsvField(recordId) & "," & CsvField(outputName) & "," & _
CsvField(templateReleaseId) & ",,,,,," & _
CsvField(intendedDocxPath) & "," & CsvField(intendedPdfPath) & _
"," & CsvField("PENDING") & ","
stream.Close
Set stream = Nothing
End Sub
Private Sub AppendManifestDocument(ByVal fso As Object, _
ByVal manifestPath As String, _
ByVal runId As String, _
ByVal recordId As String, _
ByVal outputName As String, _
ByVal templateReleaseId As String, _
ByVal docxPath As String, _
ByVal pdfPath As String)
Dim stream As Object
Set stream = fso.OpenTextFile(manifestPath, FOR_APPENDING, _
False, TRISTATE_TRUE)
stream.WriteLine CsvField("DOCUMENT") & "," & CsvField(runId) & _
"," & CsvField(recordId) & "," & CsvField(outputName) & "," & _
CsvField(templateReleaseId) & ",,,,,," & CsvField(docxPath) & _
"," & CsvField(pdfPath) & "," & CsvField("COMPLETED") & ","
stream.Close
Set stream = Nothing
End Sub
Private Sub AppendManifestComplete(ByVal fso As Object, _
ByVal manifestPath As String, _
ByVal runId As String, _
ByVal completedCount As Long)
Dim stream As Object
Set stream = fso.OpenTextFile(manifestPath, FOR_APPENDING, _
False, TRISTATE_TRUE)
stream.WriteLine CsvField("BATCH") & "," & CsvField(runId) & _
String$(11, ",") & CsvField("COMPLETE") & "," & _
CsvField(CStr(completedCount))
stream.Close
Set stream = Nothing
End Sub
Private Function CsvField(ByVal valueText As String) As String
Dim safeText As String
safeText = valueText
If Len(safeText) > 0 Then
Select Case Left$(safeText, 1)
Case "=", "+", "-", "@"
safeText = "'" & safeText
End Select
End If
CsvField = Chr$(34) & Replace$(safeText, Chr$(34), _
Chr$(34) & Chr$(34)) & Chr$(34)
End Function
Private Function CreateRunId() As String
CreateRunId = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 100), "00000000")
End Function
Private Function CreateRunFolder(ByVal fso As Object, _
ByVal outputRoot As String, _
ByVal runId As String) As String
Dim attempt As Long
Dim candidate As String
For attempt = 0 To 99
candidate = JoinPath(outputRoot, "Batch_" & runId & _
IIf(attempt = 0, "", "_" & Format$(attempt, "00")))
If Not fso.FolderExists(candidate) Then
fso.CreateFolder candidate
CreateRunFolder = candidate
Exit Function
End If
Next attempt
Err.Raise vbObjectError + 4534, , _
"Could not create a unique batch folder under " & outputRoot & "."
End Function
Private Function IsAbsolutePath(ByVal pathText As String) As Boolean
If Len(pathText) >= 2 Then
If Left$(pathText, 2) = "\\" Then
IsAbsolutePath = True
Exit Function
End If
End If
If Len(pathText) >= 3 Then
If Mid$(pathText, 2, 2) = ":\" Then
IsAbsolutePath = True
End If
End If
End Function
Private Function PathsOverlap(ByVal fso As Object, _
ByVal firstPath As String, _
ByVal secondPath As String) As Boolean
Dim firstNormalized As String
Dim secondNormalized As String
firstNormalized = NormalizeFolderPath(fso.GetAbsolutePathName(firstPath))
secondNormalized = NormalizeFolderPath(fso.GetAbsolutePathName(secondPath))
PathsOverlap = PathIsSameOrChild(firstNormalized, secondNormalized) Or _
PathIsSameOrChild(secondNormalized, firstNormalized)
End Function
Private Function PathIsSameOrChild(ByVal parentPath As String, _
ByVal candidatePath As String) As Boolean
Dim parentPrefix As String
If StrComp(parentPath, candidatePath, vbTextCompare) = 0 Then
PathIsSameOrChild = True
Exit Function
End If
parentPrefix = parentPath
If Right$(parentPrefix, 1) <> "\" Then _
parentPrefix = parentPrefix & "\"
If Len(candidatePath) <= Len(parentPrefix) Then Exit Function
PathIsSameOrChild = _
(StrComp(Left$(candidatePath, Len(parentPrefix)), _
parentPrefix, vbTextCompare) = 0)
End Function
Private Function NormalizeFolderPath(ByVal folderPath As String) As String
NormalizeFolderPath = folderPath
Do While Len(NormalizeFolderPath) > 3 And _
Right$(NormalizeFolderPath, 1) = "\"
NormalizeFolderPath = Left$(NormalizeFolderPath, _
Len(NormalizeFolderPath) - 1)
Loop
End Function
Private Sub RequireTargetAbsent(ByVal filePath As String)
If FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 4535, , _
"File already exists and will not be overwritten: " & filePath
End If
End Sub
Private Sub RequireExistingFile(ByVal filePath As String, _
ByVal fileLabel As String)
If Not FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 4536, , _
fileLabel & " was not found: " & filePath
End If
End Sub
Private Sub RequireNonemptyFile(ByVal filePath As String, _
ByVal fileLabel As String)
RequireExistingFile filePath, fileLabel
If FileLen(filePath) <= 0 Then
Err.Raise vbObjectError + 4537, , _
fileLabel & " is empty: " & filePath
End If
End Sub
Private Function FileExistsStrict(ByVal filePath As String) As Boolean
On Error GoTo CheckFailed
FileExistsStrict = (Len(Dir$(filePath, vbNormal Or vbHidden Or _
vbSystem Or vbReadOnly Or vbArchive)) > 0)
Exit Function
CheckFailed:
Err.Raise vbObjectError + 4538, , _
"Could not check file path: " & filePath & ". " & Err.Description
End Function
Private Sub RequireExistingFolder(ByVal folderPath As String, _
ByVal folderLabel As String)
If Not FolderExistsStrict(folderPath) Then
Err.Raise vbObjectError + 4539, , _
folderLabel & " was not found: " & folderPath
End If
End Sub
Private Function FolderExistsStrict(ByVal folderPath As String) As Boolean
Dim attributes As Long
On Error GoTo NotFound
attributes = GetAttr(folderPath)
FolderExistsStrict = ((attributes And vbDirectory) = vbDirectory)
Exit Function
NotFound:
If Err.Number = 53 Or Err.Number = 76 Then
Err.Clear
FolderExistsStrict = False
Else
Err.Raise vbObjectError + 4540, , _
"Could not check folder path: " & folderPath & ". " & _
Err.Description
End If
End Function
Private Sub RemoveEmptyFolder(ByVal folderPath As String)
If Len(folderPath) = 0 Then Exit Sub
If Not FolderExistsStrict(folderPath) Then Exit Sub
If Len(Dir$(JoinPath(folderPath, "*"), vbNormal Or vbHidden Or _
vbSystem Or vbReadOnly Or vbArchive Or vbDirectory)) > 0 Then
Exit Sub
End If
RmDir folderPath
End Sub
Private Function JoinPath(ByVal parentPath As String, _
ByVal childName As String) As String
If Right$(parentPath, 1) = "\" Then
JoinPath = parentPath & childName
Else
JoinPath = parentPath & "\" & childName
End If
End Function
Private Function ContainsControlCharacter(ByVal valueText As String) As Boolean
Dim characterIndex As Long
Dim characterCode As Long
For characterIndex = 1 To Len(valueText)
characterCode = AscW(Mid$(valueText, characterIndex, 1))
If characterCode >= 0 And characterCode <= 31 Then
ContainsControlCharacter = True
Exit Function
End If
Next characterIndex
End Function
Private Function IsReservedWindowsName(ByVal baseName As String) As Boolean
Dim upperName As String
Dim prefix As String
Dim finalCharacter As String
upperName = UCase$(baseName)
Select Case upperName
Case "CON", "PRN", "AUX", "NUL", _
"COM1", "COM2", "COM3", "COM4", "COM5", _
"COM6", "COM7", "COM8", "COM9", _
"LPT1", "LPT2", "LPT3", "LPT4", "LPT5", _
"LPT6", "LPT7", "LPT8", "LPT9"
IsReservedWindowsName = True
Exit Function
End Select
If Len(upperName) = 4 Then
prefix = Left$(upperName, 3)
finalCharacter = Right$(upperName, 1)
If (prefix = "COM" Or prefix = "LPT") And _
(finalCharacter = ChrW$(&HB9) Or _
finalCharacter = ChrW$(&HB2) Or _
finalCharacter = ChrW$(&HB3)) Then
IsReservedWindowsName = True
End If
End If
End FunctionMicrosoft documents that FileSystemObject.CopyFile can copy without overwrite, Documents.Add creates a new document from a supplied template, and ExportAsFixedFormat creates PDF output.
Understand what the folder model does not prove
No cryptographic identity
Size and modified time can detect some changes, but they are not a hash or digital signature. TemplateReleaseID is only a recorded label.
No approval authentication
The code cannot prove who approved the source DOCX or whether the shared repository’s access policy was followed.
No atomic DOCX/PDF pair
The two files move separately. A failure between moves can leave one completed file and one staged file requiring manual review.
No universal concurrency lock
A unique run folder reduces collisions, but multiple writers still need repository and operating controls.
No automatic release
A COMPLETE manifest means the selected generation loop finished. It does not replace content, privacy, layout, or authorization review.
No silent cleanup
Partial files and run evidence remain on failure. Define retention and disposal outside the generator.
Run one folder and failure pilot
Five distinct records
Use obvious RecordID and OutputName values so every pair can be matched to its source row.
One HOLD and one DONE row
Confirm only READY rows enter the current run and the others remain untouched.
Template change test
On a disposable copy, change the source during snapshot creation and confirm the run stops rather than mixing versions.
Duplicate name test
Use two READY rows with the same OutputName and confirm preflight stops before the run folder is created.
Permission failure test
Use a disposable output root without write permission and confirm the batch stops before Word generates documents.
Mid-run stop test
Interrupt a disposable run and reconcile the actual files in Completed with the PENDING and COMPLETED manifest rows.
Approve the folder model only when one template snapshot drives every selected record, reviewers reconcile Completed with PENDING and COMPLETED manifest rows, failures preserve evidence, and reviewers can distinguish generated, completed, approved, and archived states.
Use Factory for many rows and one standard template
DocxForge Pro Factory applies one standard Word template to many independent Excel rows and creates a separate document for each selected row. Keep the template, output folder, naming, and review responsibilities explicit around that generation step.
Where it fits
Use it for repeatable document types such as standard letters, certificates, notices, or reports when each row is an independent record.
Where it does not fit
Factory does not authenticate a shared template repository, decide retention, approve outputs, or replace operating controls for concurrent writers and publication.
Factory: reviewed rows and one standard template
Review the independent Excel rows, then apply the tested Word template to the selected batch.


Frequently asked questions
Should a batch write documents into the shared template folder?
No. Treat the template repository as a controlled input location. Write each run into its own managed batch folder with separate staging, completed, and evidence areas.
Why copy the shared template before Word starts?
A run-specific snapshot gives every record in the batch the same template bytes and removes the live network file from the generation loop. The source path, size, modified time, release label, and snapshot path can then be recorded in the manifest.
Does TemplateReleaseID prove which DOCX was approved?
No. It is an operator-supplied label recorded with the batch. The example also records source file metadata and copies a snapshot, but it does not calculate a cryptographic hash, verify a digital signature, or authenticate approval.
Can the managed output root still be on a network share?
Yes, if the location has reliable permissions and one controlled writer per run. It must be separate from the template repository. For unstable connections, create the batch locally and publish reviewed results through a controlled transfer step.
What remains after a failed batch?
The macro does not delete completed or staged evidence automatically. It records PENDING before moving a pair and COMPLETED afterward. A crash after the files move can still leave a PENDING row without the later COMPLETED row, so recovery must compare the files in Completed with both manifest states.
Where does DocxForge Pro Factory fit?
Factory is designed for many independent Excel rows processed through one standard Word template, with a separate output per selected row. Use a reviewed template, verify the row mapping and filenames, and inspect the generated DOCX/PDF files before release.