Automation & Batch Operations

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.

Approved template source
Run snapshot
Managed batch folder
Reviewed outputs
A release label and file timestamp support traceability, but they do not authenticate the template. Add hashing, signatures, or repository controls when the approval standard requires them.

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.

Recommended boundary

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.

Template bookmarks

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.

Excel VBA — managed batch workspace from a shared templateStatic example
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 Function

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

Exit criteria

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

Factory logic

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.

DocxForge Pro Factory Excel Data screen with independent batch rows
Factory Excel Data: review the independent rows selected for generation.
DocxForge Pro Factory Documents screen with one standard Word template
Factory Documents: apply one tested template to the selected rows.
Generate a controlled batch from one local Word templateTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Many Excel rowsOne Word templateSeparate outputsDOCX + PDF
Start Free 7-Day Trial

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.

Keep the template source separate from each batch’s output workspaceRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Reviewed Excel rowsOne standard templateSeparate DOCXFinal PDF
Start Free 7-Day Trial