Document Automation

How to Migrate from Manual Copy-Paste to Structured Document Generation

Replace manual copy-paste through a controlled migration: inventory the real workflow, define data and template contracts, prove a pilot, compare parallel output, prepare rollback, and cut over only after the evidence passes.

Process inventory
Contracted data
Parallel validation
Controlled cutover
Do not switch off the manual process because one sample succeeded. Cut over only after representative output, ownership, recovery, and rollback are approved.

Quick answer: migrate the process, not only the typing

1. Capture the current process

Record the real inputs, templates, owners, exceptions, review decisions, filenames, delivery steps, and rework—not merely the visible copy-paste.

2. Build and prove the target

Define structured data and template contracts, then test representative records against explicit acceptance criteria.

3. Cut over with rollback

Compare controlled parallel runs, freeze the approved release, train owners, preserve rollback, and retire the old path deliberately.

Inventory the manual workflow before redesigning it

Trigger

Record what starts the work, who requests it, and how urgency or priority is decided.

Source data

List spreadsheets, emails, forms, shared folders, calculated values, and unofficial notes currently consulted.

Templates

Identify every Word file, local copy, departmental variant, and fixed paragraph that operators reuse.

Manual decisions

Separate deterministic copying from judgment, approval, exception handling, and legal or operational review.

Output and delivery

Record filenames, folders, DOCX/PDF rules, printing, sending, archiving, and downstream handoffs.

Failure and rework

Capture common defects, correction loops, duplicate work, missing files, and how operators recover today.

Define six contracts for a controlled migration

Data contract

Define required fields, optional fields, IDs, document-ready dates and numbers, image references, statuses, and allowed values.

Template contract

Define the approved Word release, exact destinations, styles, tables, images, blank behavior, and maximum tested content.

Generation contract

Define record selection, workflow module, output formats, naming, overwrite behavior, error handling, and completion status.

Review contract

Name who compares source and output, which defects block release, and what evidence is retained.

Operating contract

Document folders, permissions, desktop requirements, ownership, support route, and change approval.

Rollback contract

Preserve the last approved source, templates, configuration, instructions, and decision authority required to revert.

Choose Factory or Contractor before mapping fields

Factory

Many independent Excel rows use one standard Word template. Each selected row creates its own repeatable document output.

Contractor

One case reuses shared data across several different documents created from different Word templates.

Do not add the metrics

If Factory and Contractor replace the same manual effort, do not count the saved time twice. Define one process boundary and one baseline for the migration.

Advance through five migration stages

ASSESSMENT

Inventory the current process, choose the owner and workflow shape, and identify the source and template candidates.

PILOT

Require approved data and template contracts before generating representative test output.

PARALLEL

Require a passed pilot and rollback plan, then compare old and new output under controlled conditions.

CUTOVER

Require passed parallel evidence, trained owners, frozen releases, monitoring, and an approved transition date.

HOLD

Stop progression and document the blocker. Do not disguise an unresolved dependency as a later stage.

After cutover

Monitor defects, output time, exceptions, user feedback, and rollback triggers before retiring the previous assets.

Free VBA helper: audit the migration register

Save a writable workbook as .xlsm. Create MigrationRegister in the exact A–M order described below. Put XLSX, XLSM, or CSV source candidates in MigrationAssets\Sources and DOCX templates in MigrationAssets\Templates. Separate multiple Contractor templates with |.

Exact register

Checks headers, required values, ProcessID uniqueness, exact flags, stage values, spaces, and control characters.

Workflow shape

Requires one template for Factory and at least two different templates for Contractor.

Stage gates

Prevents PILOT, PARALLEL, or CUTOVER from being declared before their required evidence flags are YES.

Read-only scope

Checks referenced filenames and file entries, then writes an audit sheet. It does not open or validate file contents, or migrate, generate, overwrite, move, or delete production files.

Required A–M headers

ProcessID; ProcessName; Owner; WorkflowShape; SourceFile; TemplateFiles; DataContractReady; TemplateContractReady; PilotPassed; ParallelRunPassed; RollbackReady; MigrationStage; Notes.

Excel VBA — migration readiness auditNo production changes
Option Explicit

Private Const REGISTER_SHEET As String = "MigrationRegister"
Private Const AUDIT_PREFIX As String = "MigrationAudit_"
Private Const ASSET_ROOT As String = "\MigrationAssets"
Private Const SOURCE_FOLDER As String = "\Sources"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const FIRST_DATA_ROW As Long = 2

Private Const SHAPE_FACTORY As String = "FACTORY"
Private Const SHAPE_CONTRACTOR As String = "CONTRACTOR"
Private Const VALUE_YES As String = "YES"
Private Const VALUE_NO As String = "NO"

Private Const STAGE_ASSESSMENT As String = "ASSESSMENT"
Private Const STAGE_PILOT As String = "PILOT"
Private Const STAGE_PARALLEL As String = "PARALLEL"
Private Const STAGE_CUTOVER As String = "CUTOVER"
Private Const STAGE_HOLD As String = "HOLD"

Private Const COL_PROCESS_ID As Long = 1
Private Const COL_PROCESS_NAME As Long = 2
Private Const COL_OWNER As Long = 3
Private Const COL_WORKFLOW_SHAPE As Long = 4
Private Const COL_SOURCE_FILE As Long = 5
Private Const COL_TEMPLATE_FILES As Long = 6
Private Const COL_DATA_CONTRACT As Long = 7
Private Const COL_TEMPLATE_CONTRACT As Long = 8
Private Const COL_PILOT_PASSED As Long = 9
Private Const COL_PARALLEL_PASSED As Long = 10
Private Const COL_ROLLBACK_READY As Long = 11
Private Const COL_STAGE As Long = 12
Private Const COL_NOTES As Long = 13
Private Const FILE_ATTRIBUTES As Long = 39

Public Sub AuditMigrationReadiness()
    Dim wb As Workbook
    Dim registerWs As Worksheet
    Dim auditWs As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim auditRow As Long
    Dim processId As String
    Dim processName As String
    Dim ownerName As String
    Dim workflowShape As String
    Dim sourceFile As String
    Dim templateFiles As String
    Dim dataContractReady As String
    Dim templateContractReady As String
    Dim pilotPassed As String
    Dim parallelPassed As String
    Dim rollbackReady As String
    Dim migrationStage As String
    Dim notesText As String
    Dim processOwners As Object
    Dim errorCount As Long
    Dim warningCount As Long
    Dim cutoverCount As Long
    Dim holdCount As Long
    Dim auditName As String
    Dim errorMessage As String

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    If Len(wb.Path) = 0 Then
        Err.Raise vbObjectError + 3400, , _
            "Save this audit workbook as .xlsm before running the audit."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 3401, , _
            "Open a writable copy of the audit workbook."
    End If
    If wb.ProtectStructure Then
        Err.Raise vbObjectError + 3402, , _
            "Unprotect the workbook structure before adding an audit sheet."
    End If

    Set registerWs = RequireWorksheet(wb, REGISTER_SHEET)
    ValidateHeaders registerWs
    Set lastCell = registerWs.Range("A:M").Find(What:="*", _
        After:=registerWs.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False)
    If lastCell Is Nothing Then
        Err.Raise vbObjectError + 3403, , _
            "The MigrationRegister sheet is empty."
    End If
    If lastCell.Row < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 3403, , _
            "The MigrationRegister sheet has no process rows."
    End If
    lastRow = lastCell.Row

    Set processOwners = CreateObject("Scripting.Dictionary")
    processOwners.CompareMode = vbTextCompare

    auditName = UniqueAuditSheetName(wb)
    Set auditWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    auditWs.Name = auditName
    WriteAuditHeaders auditWs
    auditRow = 2
    Application.ScreenUpdating = False

    For rowNumber = FIRST_DATA_ROW To lastRow
        If Application.CountA(registerWs.Range( _
            registerWs.Cells(rowNumber, COL_PROCESS_ID), _
            registerWs.Cells(rowNumber, COL_NOTES))) = 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "ROW", "ERROR", _
                "Blank row inside the migration register.", _
                errorCount, warningCount
            GoTo NextRow
        End If

        processId = ReadExactText(registerWs.Cells(rowNumber, COL_PROCESS_ID), _
            "ProcessID", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        processName = ReadExactText(registerWs.Cells(rowNumber, COL_PROCESS_NAME), _
            "ProcessName", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        ownerName = ReadExactText(registerWs.Cells(rowNumber, COL_OWNER), _
            "Owner", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        workflowShape = ReadExactText(registerWs.Cells(rowNumber, COL_WORKFLOW_SHAPE), _
            "WorkflowShape", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        sourceFile = ReadExactText(registerWs.Cells(rowNumber, COL_SOURCE_FILE), _
            "SourceFile", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        templateFiles = ReadExactText(registerWs.Cells(rowNumber, COL_TEMPLATE_FILES), _
            "TemplateFiles", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        dataContractReady = ReadExactText(registerWs.Cells(rowNumber, COL_DATA_CONTRACT), _
            "DataContractReady", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        templateContractReady = ReadExactText(registerWs.Cells(rowNumber, COL_TEMPLATE_CONTRACT), _
            "TemplateContractReady", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        pilotPassed = ReadExactText(registerWs.Cells(rowNumber, COL_PILOT_PASSED), _
            "PilotPassed", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        parallelPassed = ReadExactText(registerWs.Cells(rowNumber, COL_PARALLEL_PASSED), _
            "ParallelRunPassed", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        rollbackReady = ReadExactText(registerWs.Cells(rowNumber, COL_ROLLBACK_READY), _
            "RollbackReady", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        migrationStage = ReadExactText(registerWs.Cells(rowNumber, COL_STAGE), _
            "MigrationStage", rowNumber, True, auditWs, auditRow, errorCount, warningCount)
        notesText = ReadExactText(registerWs.Cells(rowNumber, COL_NOTES), _
            "Notes", rowNumber, False, auditWs, auditRow, errorCount, warningCount)

        If Len(processId) > 0 Then
            RequireTextStorage registerWs.Cells(rowNumber, COL_PROCESS_ID), _
                "ProcessID", rowNumber, auditWs, auditRow, _
                errorCount, warningCount
            ValidateIdentifier processId, rowNumber, auditWs, auditRow, _
                errorCount, warningCount
            If processOwners.Exists(processId) Then
                WriteAuditRow auditWs, auditRow, rowNumber, "ProcessID", "ERROR", _
                    "Duplicate or case-variant ProcessID; first used in row " & _
                    processOwners(processId) & ".", errorCount, warningCount
            Else
                processOwners.Add processId, rowNumber
            End If
        End If

        ValidateWorkflowShape workflowShape, rowNumber, auditWs, auditRow, _
            errorCount, warningCount
        ValidateYesNo dataContractReady, "DataContractReady", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateYesNo templateContractReady, "TemplateContractReady", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateYesNo pilotPassed, "PilotPassed", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateYesNo parallelPassed, "ParallelRunPassed", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateYesNo rollbackReady, "RollbackReady", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateMigrationStage migrationStage, rowNumber, auditWs, auditRow, _
            errorCount, warningCount

        If Len(sourceFile) > 0 Then
            ValidateSingleAsset wb.Path & ASSET_ROOT & SOURCE_FOLDER, _
                sourceFile, "SourceFile", rowNumber, auditWs, auditRow, _
                errorCount, warningCount
        End If
        If Len(templateFiles) > 0 Then
            ValidateTemplateList wb.Path & ASSET_ROOT & TEMPLATE_FOLDER, _
                templateFiles, workflowShape, rowNumber, auditWs, auditRow, _
                errorCount, warningCount
        End If

        ValidateStageDependencies migrationStage, dataContractReady, _
            templateContractReady, pilotPassed, parallelPassed, _
            rollbackReady, notesText, rowNumber, auditWs, auditRow, _
            errorCount, warningCount

        If migrationStage = STAGE_CUTOVER Then cutoverCount = cutoverCount + 1
        If migrationStage = STAGE_HOLD Then holdCount = holdCount + 1
NextRow:
    Next rowNumber

    WriteSummary auditWs, lastRow - 1, cutoverCount, holdCount, _
        errorCount, warningCount
    auditWs.Columns("A:F").AutoFit
    auditWs.Rows(1).Font.Bold = True
    wb.Save
    Application.ScreenUpdating = True

    MsgBox "Migration audit created: " & auditName & vbCrLf & _
           "Errors: " & errorCount & vbCrLf & _
           "Warnings: " & warningCount, _
           IIf(errorCount = 0, vbInformation, vbExclamation), _
           "Migration readiness audit"
    Exit Sub

CleanFail:
    errorMessage = Err.Description
    Application.ScreenUpdating = True
    MsgBox "Audit stopped." & vbCrLf & errorMessage, _
        vbCritical, "Migration readiness audit"
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 + 3410, , _
            "Required sheet is missing: " & sheetName
    End If
End Function

Private Sub ValidateHeaders(ByVal ws As Worksheet)
    Dim headers As Variant
    Dim columnNumber As Long
    Dim rawHeader As Variant
    headers = Array("ProcessID", "ProcessName", "Owner", "WorkflowShape", _
        "SourceFile", "TemplateFiles", "DataContractReady", _
        "TemplateContractReady", "PilotPassed", "ParallelRunPassed", _
        "RollbackReady", "MigrationStage", "Notes")
    For columnNumber = 1 To 13
        rawHeader = ws.Cells(1, columnNumber).Value2
        If IsError(rawHeader) Then
            Err.Raise vbObjectError + 3411, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " contains an Excel error."
        End If
        If StrComp(CStr(rawHeader), CStr(headers(columnNumber - 1)), _
                   vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 3411, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " must match exactly: " & headers(columnNumber - 1) & "."
        End If
    Next columnNumber
End Sub

Private Function ReadExactText(ByVal cell As Range, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long, _
                               ByVal required As Boolean, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount As Long) As String
    Dim rawValue As Variant
    rawValue = cell.Value2
    If IsError(rawValue) Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Cell contains an Excel error.", errorCount, warningCount
        Exit Function
    End If
    ReadExactText = CStr(rawValue)
    If required And Len(ReadExactText) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Required value is blank.", errorCount, warningCount
        Exit Function
    End If
    If Len(ReadExactText) > 0 Then
        If StrComp(ReadExactText, Trim$(ReadExactText), vbBinaryCompare) <> 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
                "Leading or trailing spaces are not allowed.", _
                errorCount, warningCount
        End If
        ValidateTextControls ReadExactText, fieldName, rowNumber, _
            auditWs, auditRow, errorCount, warningCount
    End If
End Function

Private Sub ValidateTextControls(ByVal valueText As String, _
                                 ByVal fieldName As String, _
                                 ByVal rowNumber As Long, _
                                 ByVal auditWs As Worksheet, _
                                 ByRef auditRow As Long, _
                                 ByRef errorCount As Long, _
                                 ByRef warningCount As Long)
    Dim position As Long
    Dim characterCode As Long
    For position = 1 To Len(valueText)
        characterCode = AscW(Mid$(valueText, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
                "Tabs, line breaks, and control characters are not allowed.", _
                errorCount, warningCount
            Exit Sub
        End If
    Next position
End Sub

Private Sub RequireTextStorage(ByVal cell As Range, _
                               ByVal fieldName As String, _
                               ByVal rowNumber As Long, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount As Long)
    If IsError(cell.Value2) Then Exit Sub
    If VarType(cell.Value2) <> vbString Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Store the identifier as text to preserve exact identity.", _
            errorCount, warningCount
    End If
End Sub

Private Sub ValidateIdentifier(ByVal valueText As String, _
                               ByVal rowNumber As Long, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount As Long)
    Dim position As Long
    Dim character As String
    For position = 1 To Len(valueText)
        character = Mid$(valueText, position, 1)
        If Not (character Like "[A-Za-z0-9_-]") Then
            WriteAuditRow auditWs, auditRow, rowNumber, "ProcessID", "ERROR", _
                "Use only letters, digits, underscore, and hyphen.", _
                errorCount, warningCount
            Exit Sub
        End If
    Next position
End Sub

Private Sub ValidateWorkflowShape(ByVal valueText As String, _
                                  ByVal rowNumber As Long, _
                                  ByVal auditWs As Worksheet, _
                                  ByRef auditRow As Long, _
                                  ByRef errorCount As Long, _
                                  ByRef warningCount As Long)
    If valueText <> SHAPE_FACTORY And valueText <> SHAPE_CONTRACTOR Then
        WriteAuditRow auditWs, auditRow, rowNumber, "WorkflowShape", _
            "ERROR", "Use FACTORY or CONTRACTOR exactly.", _
            errorCount, warningCount
    End If
End Sub

Private Sub ValidateYesNo(ByVal valueText As String, _
                          ByVal fieldName As String, _
                          ByVal rowNumber As Long, _
                          ByVal auditWs As Worksheet, _
                          ByRef auditRow As Long, _
                          ByRef errorCount As Long, _
                          ByRef warningCount As Long)
    If valueText <> VALUE_YES And valueText <> VALUE_NO Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, _
            "ERROR", "Use YES or NO exactly.", errorCount, warningCount
    End If
End Sub

Private Sub ValidateMigrationStage(ByVal valueText As String, _
                                   ByVal rowNumber As Long, _
                                   ByVal auditWs As Worksheet, _
                                   ByRef auditRow As Long, _
                                   ByRef errorCount As Long, _
                                   ByRef warningCount As Long)
    Select Case valueText
        Case STAGE_ASSESSMENT, STAGE_PILOT, STAGE_PARALLEL, _
             STAGE_CUTOVER, STAGE_HOLD
        Case Else
            WriteAuditRow auditWs, auditRow, rowNumber, "MigrationStage", _
                "ERROR", _
                "Use ASSESSMENT, PILOT, PARALLEL, CUTOVER, or HOLD exactly.", _
                errorCount, warningCount
    End Select
End Sub

Private Sub ValidateStageDependencies(ByVal stageValue As String, _
                                      ByVal dataReady As String, _
                                      ByVal templateReady As String, _
                                      ByVal pilotPassed As String, _
                                      ByVal parallelPassed As String, _
                                      ByVal rollbackReady As String, _
                                      ByVal notesText As String, _
                                      ByVal rowNumber As Long, _
                                      ByVal auditWs As Worksheet, _
                                      ByRef auditRow As Long, _
                                      ByRef errorCount As Long, _
                                      ByRef warningCount As Long)
    If stageValue = STAGE_PILOT Or stageValue = STAGE_PARALLEL Or _
       stageValue = STAGE_CUTOVER Then
        If dataReady <> VALUE_YES Or templateReady <> VALUE_YES Then
            WriteAuditRow auditWs, auditRow, rowNumber, "STAGE GATE", _
                "ERROR", "PILOT or later requires both contracts to be ready.", _
                errorCount, warningCount
        End If
    End If
    If stageValue = STAGE_PARALLEL Or stageValue = STAGE_CUTOVER Then
        If pilotPassed <> VALUE_YES Then
            WriteAuditRow auditWs, auditRow, rowNumber, "STAGE GATE", _
                "ERROR", "PARALLEL or CUTOVER requires PilotPassed = YES.", _
                errorCount, warningCount
        End If
        If rollbackReady <> VALUE_YES Then
            WriteAuditRow auditWs, auditRow, rowNumber, "STAGE GATE", _
                "ERROR", "PARALLEL or CUTOVER requires RollbackReady = YES.", _
                errorCount, warningCount
        End If
    End If
    If stageValue = STAGE_CUTOVER And parallelPassed <> VALUE_YES Then
        WriteAuditRow auditWs, auditRow, rowNumber, "STAGE GATE", _
            "ERROR", "CUTOVER requires ParallelRunPassed = YES.", _
            errorCount, warningCount
    End If
    If stageValue = STAGE_HOLD Then
        If Len(notesText) = 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, "Notes", "ERROR", _
                "HOLD requires a documented reason in Notes.", _
                errorCount, warningCount
        Else
            WriteAuditRow auditWs, auditRow, rowNumber, "STAGE GATE", _
                "WARNING", "Process remains on HOLD.", _
                errorCount, warningCount
        End If
    End If
End Sub

Private Sub ValidateTemplateList(ByVal templateFolder As String, _
                                 ByVal listText As String, _
                                 ByVal workflowShape As String, _
                                 ByVal rowNumber As Long, _
                                 ByVal auditWs As Worksheet, _
                                 ByRef auditRow As Long, _
                                 ByRef errorCount As Long, _
                                 ByRef warningCount As Long)
    Dim items As Variant
    Dim item As Variant
    Dim templateName As String
    Dim seen As Object
    Dim nameIsValid As Boolean
    Dim extensionIsValid As Boolean
    items = Split(listText, "|")
    If workflowShape = SHAPE_FACTORY And UBound(items) <> 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
            "ERROR", "FACTORY requires exactly one Word template.", _
            errorCount, warningCount
    End If
    If workflowShape = SHAPE_CONTRACTOR And UBound(items) < 1 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
            "ERROR", "CONTRACTOR requires at least two different templates.", _
            errorCount, warningCount
    End If
    Set seen = CreateObject("Scripting.Dictionary")
    seen.CompareMode = vbTextCompare
    For Each item In items
        templateName = CStr(item)
        nameIsValid = ValidateAssetName(templateName, "TemplateFiles", rowNumber, _
            auditWs, auditRow, errorCount, warningCount)
        extensionIsValid = ValidateFileExtension(templateName, Array("docx"), _
            "TemplateFiles", rowNumber, auditWs, auditRow, _
            errorCount, warningCount)
        If seen.Exists(templateName) Then
            WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
                "ERROR", "Duplicate template filename in the list: " & _
                templateName, errorCount, warningCount
        Else
            seen.Add templateName, True
        End If
        If nameIsValid And extensionIsValid Then
            CheckFileEntry templateFolder & "\" & templateName, _
                "TemplateFiles", rowNumber, auditWs, auditRow, _
                errorCount, warningCount
        End If
    Next item
End Sub

Private Sub ValidateSingleAsset(ByVal folderPath As String, _
                                ByVal fileName As String, _
                                ByVal fieldName As String, _
                                ByVal rowNumber As Long, _
                                ByVal auditWs As Worksheet, _
                                ByRef auditRow As Long, _
                                ByRef errorCount As Long, _
                                ByRef warningCount As Long)
    Dim nameIsValid As Boolean
    Dim extensionIsValid As Boolean
    nameIsValid = ValidateAssetName(fileName, fieldName, rowNumber, _
        auditWs, auditRow, errorCount, warningCount)
    extensionIsValid = ValidateFileExtension(fileName, _
        Array("xlsx", "xlsm", "csv"), _
        fieldName, rowNumber, auditWs, auditRow, errorCount, warningCount)
    If nameIsValid And extensionIsValid Then
        CheckFileEntry folderPath & "\" & fileName, fieldName, rowNumber, _
            auditWs, auditRow, errorCount, warningCount
    End If
End Sub

Private Function ValidateAssetName(ByVal fileName As String, _
                                   ByVal fieldName As String, _
                                   ByVal rowNumber As Long, _
                                   ByVal auditWs As Worksheet, _
                                   ByRef auditRow As Long, _
                                   ByRef errorCount As Long, _
                                   ByRef warningCount As Long) As Boolean
    Dim forbidden As Variant
    Dim item As Variant
    Dim position As Long
    Dim characterCode As Long
    Dim baseName As String
    ValidateAssetName = True
    forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    If Len(fileName) = 0 Or _
       StrComp(fileName, Trim$(fileName), vbBinaryCompare) <> 0 Or _
       Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Filename is blank or has unsafe outer characters.", _
            errorCount, warningCount
        ValidateAssetName = False
    End If
    For Each item In forbidden
        If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
                "Filename contains a forbidden Windows character.", _
                errorCount, warningCount
            ValidateAssetName = False
        End If
    Next item
    For position = 1 To Len(fileName)
        characterCode = AscW(Mid$(fileName, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
                "Filename contains a Windows control character.", _
                errorCount, warningCount
            ValidateAssetName = False
            Exit For
        End If
    Next position
    baseName = fileName
    If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
        baseName = Left$(baseName, InStr(1, baseName, ".", vbBinaryCompare) - 1)
    End If
    If IsReservedWindowsName(UCase$(baseName)) Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Filename starts with a reserved Windows device name.", _
            errorCount, warningCount
        ValidateAssetName = False
    End If
End Function

Private Function ValidateFileExtension(ByVal fileName As String, _
                                       ByVal allowedExtensions As Variant, _
                                       ByVal fieldName As String, _
                                       ByVal rowNumber As Long, _
                                       ByVal auditWs As Worksheet, _
                                       ByRef auditRow As Long, _
                                       ByRef errorCount As Long, _
                                       ByRef warningCount As Long) As Boolean
    Dim dotPosition As Long
    Dim extension As String
    Dim item As Variant
    Dim isAllowed As Boolean
    dotPosition = InStrRev(fileName, ".")
    If dotPosition > 1 And dotPosition < Len(fileName) Then
        extension = LCase$(Mid$(fileName, dotPosition + 1))
    End If
    For Each item In allowedExtensions
        If extension = CStr(item) Then isAllowed = True
    Next item
    ValidateFileExtension = isAllowed
    If Not isAllowed Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "File extension is not allowed for this migration asset: " & _
            fileName, errorCount, warningCount
    End If
End Function

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

Private Sub CheckFileEntry(ByVal filePath As String, _
                           ByVal fieldName As String, _
                           ByVal rowNumber As Long, _
                           ByVal auditWs As Worksheet, _
                           ByRef auditRow As Long, _
                           ByRef errorCount As Long, _
                           ByRef warningCount As Long)
    Dim foundName As String
    Dim checkErrorNumber As Long
    Dim checkErrorDescription As String
    On Error GoTo CheckFailed
    foundName = Dir$(filePath, FILE_ATTRIBUTES)
    If Len(foundName) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Required file is missing: " & filePath, _
            errorCount, warningCount
    ElseIf (GetAttr(filePath) And vbDirectory) = vbDirectory Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Expected a file but found a folder: " & filePath, _
            errorCount, warningCount
    Else
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "PASS", _
            "File entry was found: " & filePath, errorCount, warningCount
    End If
    Exit Sub
CheckFailed:
    checkErrorNumber = Err.Number
    checkErrorDescription = Err.Description
    Err.Clear
    WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
        "Could not check path: " & filePath & ". File-system error " & _
        checkErrorNumber & ": " & checkErrorDescription, _
        errorCount, warningCount
End Sub

Private Function UniqueAuditSheetName(ByVal wb As Workbook) As String
    Dim baseName As String
    Dim candidate As String
    Dim counter As Long
    baseName = AUDIT_PREFIX & Format$(Now, "yyyymmdd_hhnnss")
    candidate = Left$(baseName, 31)
    counter = 1
    Do While WorksheetExists(wb, candidate)
        counter = counter + 1
        candidate = Left$(baseName, 27) & "_" & counter
    Loop
    UniqueAuditSheetName = candidate
End Function

Private Function WorksheetExists(ByVal wb As Workbook, _
                                 ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    WorksheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

Private Sub WriteAuditHeaders(ByVal ws As Worksheet)
    ws.Cells(1, 1).Value = "SourceRow"
    ws.Cells(1, 2).Value = "ProcessID"
    ws.Cells(1, 3).Value = "Check"
    ws.Cells(1, 4).Value = "Result"
    ws.Cells(1, 5).Value = "Details"
    ws.Cells(1, 6).Value = "CheckedAt"
End Sub

Private Sub WriteAuditRow(ByVal auditWs As Worksheet, _
                          ByRef auditRow As Long, _
                          ByVal sourceRow As Long, _
                          ByVal checkName As String, _
                          ByVal resultText As String, _
                          ByVal details As String, _
                          ByRef errorCount As Long, _
                          ByRef warningCount As Long)
    auditWs.Cells(auditRow, 1).Value = sourceRow
    If sourceRow >= FIRST_DATA_ROW Then
        auditWs.Cells(auditRow, 2).Value = _
            ThisWorkbook.Worksheets(REGISTER_SHEET).Cells( _
                sourceRow, COL_PROCESS_ID).Value2
    End If
    auditWs.Cells(auditRow, 3).Value = checkName
    auditWs.Cells(auditRow, 4).Value = resultText
    auditWs.Cells(auditRow, 5).Value = details
    auditWs.Cells(auditRow, 6).Value = Now
    If resultText = "ERROR" Then errorCount = errorCount + 1
    If resultText = "WARNING" Then warningCount = warningCount + 1
    auditRow = auditRow + 1
End Sub

Private Sub WriteSummary(ByVal ws As Worksheet, _
                         ByVal processCount As Long, _
                         ByVal cutoverCount As Long, _
                         ByVal holdCount As Long, _
                         ByVal errorCount As Long, _
                         ByVal warningCount As Long)
    ws.Range("H1").Value = "SUMMARY"
    ws.Range("H2").Value = "Process rows"
    ws.Range("I2").Value = processCount
    ws.Range("H3").Value = "CUTOVER rows"
    ws.Range("I3").Value = cutoverCount
    ws.Range("H4").Value = "HOLD rows"
    ws.Range("I4").Value = holdCount
    ws.Range("H5").Value = "Errors"
    ws.Range("I5").Value = errorCount
    ws.Range("H6").Value = "Warnings"
    ws.Range("I6").Value = warningCount
    ws.Range("H8").Value = "Scope"
    ws.Range("I8").Value = _
        "Readiness audit only; does not migrate, generate, or delete files."
    ws.Columns("H:I").AutoFit
End Sub

Excel VBA references: Range.Find and Workbook.ReadOnly.

Run a controlled parallel comparison

Freeze the test inputs

Use the same approved records, source version, and expected result for the manual and structured routes.

Compare identity

Verify record IDs, filenames, template versions, page counts, and output formats.

Compare content

Check every mapped field, allowed blank, repeated section, image, table, and fixed notice.

Compare layout

Inspect Word and PDF pagination, fonts, spacing, headers, footers, and print behavior.

Record exceptions

Classify real defects separately from approved differences and undocumented manual habits.

Approve evidence

Do not mark ParallelRunPassed = YES until the process owner accepts the documented comparison.

Cut over without losing the recovery path

Freeze releases

Record the approved workbook, template versions, configuration, and adoption date.

Train named owners

Train source preparers, operators, reviewers, and support contacts on the exact new process.

Separate old and new output

Prevent operators from mixing results created by different workflows during transition.

Monitor agreed signals

Track blocking defects, rework, output time, exception volume, and unprocessed records.

Define rollback triggers

Name the conditions and decision owner that can pause the new process and restore the earlier controlled route.

Retire deliberately

Archive obsolete templates and instructions only after the monitoring period and retention decision are complete.

Map the approved target into DocxForge Pro

DocxForge Pro supports the two target workflow shapes after the data and template contracts are defined.

Contractor

One case, shared case data, and several different documents from different Word templates.

Factory

Many independent Excel rows, one standard Word template, and a separate output for each selected row.

DocxForge Pro Contractor case data during structured workflow migration
Contractor: review the shared data for one case.
DocxForge Pro Contractor documents using different Word templates
Contractor: confirm the different documents and templates.
DocxForge Pro Factory Excel rows during migration from copy paste
Factory: review independent rows and structured values.
DocxForge Pro Factory document settings with one standard template
Factory: confirm one template and the output settings.
Move from manual assembly to a defined local document workflowTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Contractor + FactoryStructured contractsLocal processingDOCX + PDF
Start Free 7-Day Trial

Frequently asked questions

Should we automate every manual document at once?

No. Start with one stable, repeated document process whose owner, source, template, exceptions, and review criteria can be defined. Use the first migration to establish the method before expanding.

How long should the manual and structured processes run in parallel?

There is no universal duration. Run enough representative cases to cover normal records, approved exceptions, peak conditions, and the agreed acceptance criteria without creating two uncontrolled sources of truth.

What does rollback mean in document generation?

Rollback means preserving the last approved source, templates, configuration, operating instructions, and ownership needed to resume the earlier controlled process if the new workflow fails its cutover criteria.

Can the VBA helper migrate or generate documents?

No. It audits a MigrationRegister, checks referenced source and template files, validates Factory or Contractor shape, and enforces stage dependencies. It does not alter production files.

When should we choose Factory or Contractor?

Choose Factory for many independent rows and one standard template. Choose Contractor when one case creates several different documents from different Word templates.

Does a clean migration audit mean the workflow is ready for production?

No. It confirms the declared register passed these static checks. The process owner must still approve real pilot output, parallel-run evidence, access controls, operating instructions, and rollback readiness.

Replace copy-paste after the structured workflow has earned the cutoverRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Defined contractsParallel evidenceRollback readyControlled output
Start Free 7-Day Trial