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.
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.
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.
ProcessID; ProcessName; Owner; WorkflowShape; SourceFile; TemplateFiles; DataContractReady; TemplateContractReady; PilotPassed; ParallelRunPassed; RollbackReady; MigrationStage; Notes.
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 SubExcel 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.




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.