Document Automation

How to Standardize Repeating Business Documents Across Teams

Standardize recurring documents with one visible operating contract: what data enters, which templates are approved, how files are named, who reviews releases, and how changes reach every team.

Shared contracts
Named owners
Versioned templates
Controlled releases
Standardize the contract, not every business exception. A shared template without ownership and release control will drift again.

Quick answer: standardize the operating contract before the files

A repeatable document becomes a team standard only when the same source fields, approved template release, naming rule, output format, reviewer, and change path are used consistently.

Practical sequence

Group truly equivalent outputs into document families. Define one contract per family. Register every team workflow against that contract. Pilot representative records. Release only after ownership and review are explicit. Audit the register regularly for drift.

Define six layers of the shared standard

1. Document family

State which outputs are genuinely equivalent. Similar appearance does not prove that teams share the same purpose, fields, review, or retention needs.

2. Data contract

Define exact field names, required and optional values, IDs, display-ready dates and amounts, image references, and allowed statuses.

3. Template contract

Name the approved DOCX files, destinations, styles, blank behavior, maximum tested content, and template version.

4. Output contract

Define file naming, DOCX or PDF output, folder ownership, overwrite rules, completion status, and exception handling.

5. Review contract

Name the process owner and review owner, blocking defects, evidence to retain, and the authority that approves a release.

6. Change contract

Define how a field, template, naming rule, or module change is proposed, tested, released, communicated, and retired.

Standardize what is shared and preserve real differences

Good candidates for one family

The teams use the same business purpose, data fields, template layout, output rule, reviewer role, and release criteria. Differences are limited to record values.

Keep separate families

The outputs have different legal purpose, mandatory wording, review authority, template set, retention rule, or downstream consumer. Combining them would hide a real control difference.

Ownership must be explicit

A shared drive location does not identify who approves content, investigates failures, coordinates changes, or retires an obsolete release.

Avoid the universal template

One template filled with team-specific conditions can become harder to test than several explicit families.

Avoid local silent copies

A centrally approved template is ineffective if operators continue using renamed desktop copies.

Build a standards register that teams can actually maintain

One row per team workflow

Repeated rows for the same DocumentFamily must carry the same workflow shape, template list, template version, data-contract version, and output-name pattern.

A–F: identity, ownership, and shape

WorkflowID; DocumentFamily; TeamName; ProcessOwner; WorkflowShape; TemplateFiles.

G–L: version and control state

TemplateVersion; DataContractVersion; OutputNamePattern; ReviewOwner; ReleaseStatus; Notes.

Exact values

Use FACTORY or CONTRACTOR for WorkflowShape. Use DRAFT, PILOT, APPROVED, or RETIRED for ReleaseStatus. Separate Contractor template filenames with a literal vertical bar. Store WorkflowID as a true text value.

Move changes through one visible release process

DRAFT

Define the proposed contract and owner. Do not present it as a production standard.

PILOT

Test normal, long, blank, image, formatting, and failure cases with representative source records.

APPROVED

Record the reviewer, publish the approved files, communicate the adoption date, and preserve release evidence.

RETIRED

Record the replacement or retention decision. Prevent the retired release from remaining the easiest copy to find.

Free VBA helper: audit the cross-team standards register

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create StandardsRegister with the exact A–L headers above. Put approved DOCX files in StandardsAssets\Templates beside the workbook.

Exact contract values

Rejects altered headers, normalized statuses, leading or trailing spaces, control characters, missing required values, and duplicate workflow IDs.

Family drift

Compares shape, template list, template version, data-contract version, and output pattern across every row in the same document family.

Safe template presence check

Rejects unsafe filenames and wrong extensions before checking the configured template folder. It does not open or authenticate DOCX content.

Approval ownership

Requires a ReviewOwner for APPROVED rows and warns when the process and review owners are the same.

Excel VBA — cross-team standards auditRegister audit only
Option Explicit

Private Const REGISTER_SHEET As String = "StandardsRegister"
Private Const AUDIT_PREFIX As String = "StandardsAudit_"
Private Const TEMPLATE_ROOT As String = "\StandardsAssets\Templates"
Private Const FIRST_DATA_ROW As Long = 2
Private Const FILE_ATTRIBUTES As Long = 39

Private Const SHAPE_FACTORY As String = "FACTORY"
Private Const SHAPE_CONTRACTOR As String = "CONTRACTOR"
Private Const STATUS_DRAFT As String = "DRAFT"
Private Const STATUS_PILOT As String = "PILOT"
Private Const STATUS_APPROVED As String = "APPROVED"
Private Const STATUS_RETIRED As String = "RETIRED"

Private Const COL_WORKFLOW_ID As Long = 1
Private Const COL_DOCUMENT_FAMILY As Long = 2
Private Const COL_TEAM_NAME As Long = 3
Private Const COL_PROCESS_OWNER As Long = 4
Private Const COL_WORKFLOW_SHAPE As Long = 5
Private Const COL_TEMPLATE_FILES As Long = 6
Private Const COL_TEMPLATE_VERSION As Long = 7
Private Const COL_DATA_VERSION As Long = 8
Private Const COL_OUTPUT_PATTERN As Long = 9
Private Const COL_REVIEW_OWNER As Long = 10
Private Const COL_RELEASE_STATUS As Long = 11
Private Const COL_NOTES As Long = 12

Public Sub AuditCrossTeamDocumentStandards()
    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 workflowId As String
    Dim documentFamily As String
    Dim teamName As String
    Dim processOwner As String
    Dim workflowShape As String
    Dim templateFiles As String
    Dim templateVersion As String
    Dim dataVersion As String
    Dim outputPattern As String
    Dim reviewOwner As String
    Dim releaseStatus As String
    Dim notesText As String
    Dim workflowRows As Object
    Dim familyContracts As Object
    Dim familyFirstRows As Object
    Dim contractText As String
    Dim errorCount As Long
    Dim warningCount As Long
    Dim approvedCount 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 + 3500, , _
            "Save this audit workbook as .xlsm before running the audit."
    End If
    If wb.ReadOnly Then
        Err.Raise vbObjectError + 3501, , _
            "Open a writable copy of the audit workbook."
    End If
    If wb.ProtectStructure Then
        Err.Raise vbObjectError + 3502, , _
            "Unprotect the workbook structure before adding an audit sheet."
    End If

    Set registerWs = RequireWorksheet(wb, REGISTER_SHEET)
    ValidateHeaders registerWs

    Set lastCell = registerWs.Range("A:L").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 + 3503, , _
            "The StandardsRegister sheet is empty."
    End If
    If lastCell.Row < FIRST_DATA_ROW Then
        Err.Raise vbObjectError + 3503, , _
            "The StandardsRegister sheet has no workflow rows."
    End If
    lastRow = lastCell.Row

    Set workflowRows = CreateObject("Scripting.Dictionary")
    workflowRows.CompareMode = vbTextCompare
    Set familyContracts = CreateObject("Scripting.Dictionary")
    familyContracts.CompareMode = vbTextCompare
    Set familyFirstRows = CreateObject("Scripting.Dictionary")
    familyFirstRows.CompareMode = vbTextCompare

    auditName = NextAuditSheetName(wb)
    Set auditWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    auditWs.Name = auditName
    PrepareAuditSheet auditWs
    auditRow = 2

    For rowNumber = FIRST_DATA_ROW To lastRow
        If WorksheetFunction.CountA(registerWs.Range( _
            registerWs.Cells(rowNumber, 1), _
            registerWs.Cells(rowNumber, 12))) = 0 Then
            GoTo NextRow
        End If

        workflowId = ReadRequiredText(registerWs.Cells(rowNumber, COL_WORKFLOW_ID), _
            "WorkflowID", rowNumber, auditWs, auditRow, errorCount, warningCount)
        documentFamily = ReadRequiredText(registerWs.Cells(rowNumber, COL_DOCUMENT_FAMILY), _
            "DocumentFamily", rowNumber, auditWs, auditRow, errorCount, warningCount)
        teamName = ReadRequiredText(registerWs.Cells(rowNumber, COL_TEAM_NAME), _
            "TeamName", rowNumber, auditWs, auditRow, errorCount, warningCount)
        processOwner = ReadRequiredText(registerWs.Cells(rowNumber, COL_PROCESS_OWNER), _
            "ProcessOwner", rowNumber, auditWs, auditRow, errorCount, warningCount)
        workflowShape = ReadRequiredText(registerWs.Cells(rowNumber, COL_WORKFLOW_SHAPE), _
            "WorkflowShape", rowNumber, auditWs, auditRow, errorCount, warningCount)
        templateFiles = ReadRequiredText(registerWs.Cells(rowNumber, COL_TEMPLATE_FILES), _
            "TemplateFiles", rowNumber, auditWs, auditRow, errorCount, warningCount)
        templateVersion = ReadRequiredText(registerWs.Cells(rowNumber, COL_TEMPLATE_VERSION), _
            "TemplateVersion", rowNumber, auditWs, auditRow, errorCount, warningCount)
        dataVersion = ReadRequiredText(registerWs.Cells(rowNumber, COL_DATA_VERSION), _
            "DataContractVersion", rowNumber, auditWs, auditRow, errorCount, warningCount)
        outputPattern = ReadRequiredText(registerWs.Cells(rowNumber, COL_OUTPUT_PATTERN), _
            "OutputNamePattern", rowNumber, auditWs, auditRow, errorCount, warningCount)
        reviewOwner = ReadOptionalText(registerWs.Cells(rowNumber, COL_REVIEW_OWNER), _
            "ReviewOwner", rowNumber, auditWs, auditRow, errorCount, warningCount)
        releaseStatus = ReadRequiredText(registerWs.Cells(rowNumber, COL_RELEASE_STATUS), _
            "ReleaseStatus", rowNumber, auditWs, auditRow, errorCount, warningCount)
        notesText = ReadOptionalText(registerWs.Cells(rowNumber, COL_NOTES), _
            "Notes", rowNumber, auditWs, auditRow, errorCount, warningCount)

        RequireTextValue registerWs.Cells(rowNumber, COL_WORKFLOW_ID), _
            "WorkflowID", rowNumber, auditWs, auditRow, errorCount, warningCount
        ValidateWorkflowId workflowId, rowNumber, workflowRows, auditWs, auditRow, _
            errorCount, warningCount
        ValidateExactValue workflowShape, Array(SHAPE_FACTORY, SHAPE_CONTRACTOR), _
            "WorkflowShape", "Use FACTORY or CONTRACTOR exactly.", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateExactValue releaseStatus, _
            Array(STATUS_DRAFT, STATUS_PILOT, STATUS_APPROVED, STATUS_RETIRED), _
            "ReleaseStatus", _
            "Use DRAFT, PILOT, APPROVED, or RETIRED exactly.", rowNumber, _
            auditWs, auditRow, errorCount, warningCount
        ValidateTemplateList wb.Path & TEMPLATE_ROOT, templateFiles, workflowShape, _
            rowNumber, auditWs, auditRow, errorCount, warningCount
        ValidateOutputPattern outputPattern, rowNumber, auditWs, auditRow, _
            errorCount, warningCount
        ValidateApproval releaseStatus, processOwner, reviewOwner, notesText, _
            rowNumber, auditWs, auditRow, errorCount, warningCount

        If releaseStatus = STATUS_APPROVED Then approvedCount = approvedCount + 1

        If Len(documentFamily) > 0 And Len(workflowShape) > 0 And _
           Len(templateFiles) > 0 And Len(templateVersion) > 0 And _
           Len(dataVersion) > 0 And Len(outputPattern) > 0 Then
            contractText = BuildContract(workflowShape, templateFiles, _
                templateVersion, dataVersion, outputPattern)
            ValidateFamilyContract documentFamily, contractText, rowNumber, _
                familyContracts, familyFirstRows, auditWs, auditRow, _
                errorCount, warningCount
        End If

NextRow:
    Next rowNumber

    FinishAuditSheet auditWs, errorCount, warningCount, approvedCount
    wb.Save
    MsgBox "Standards audit complete." & vbCrLf & _
        "Errors: " & errorCount & vbCrLf & _
        "Warnings: " & warningCount & vbCrLf & _
        "Approved rows: " & approvedCount & vbCrLf & _
        "Report: " & auditName, _
        IIf(errorCount = 0, vbInformation, vbExclamation), _
        "Cross-team document standards"
    Exit Sub

CleanFail:
    errorMessage = Err.Description
    MsgBox "The standards audit stopped: " & errorMessage, _
        vbCritical, "Cross-team document standards"
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 + 3510, , _
            "Required worksheet not found: " & sheetName
    End If
End Function

Private Sub ValidateHeaders(ByVal ws As Worksheet)
    Dim expected As Variant
    Dim columnNumber As Long
    Dim actualText As String
    expected = Array("WorkflowID", "DocumentFamily", "TeamName", _
        "ProcessOwner", "WorkflowShape", "TemplateFiles", _
        "TemplateVersion", "DataContractVersion", "OutputNamePattern", _
        "ReviewOwner", "ReleaseStatus", "Notes")
    For columnNumber = 1 To 12
        actualText = CStr(ws.Cells(1, columnNumber).Value2)
        If StrComp(actualText, CStr(expected(columnNumber - 1)), _
                   vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 3511, , _
                "Header " & ws.Cells(1, columnNumber).Address(False, False) & _
                " must match exactly: " & expected(columnNumber - 1)
        End If
    Next columnNumber
End Sub

Private Function ReadRequiredText(ByVal sourceCell 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) As String
    ReadRequiredText = ReadCellText(sourceCell, fieldName, rowNumber, _
        auditWs, auditRow, errorCount, warningCount)
    If Len(ReadRequiredText) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Required value is blank.", errorCount, warningCount
    End If
End Function

Private Function ReadOptionalText(ByVal sourceCell 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) As String
    ReadOptionalText = ReadCellText(sourceCell, fieldName, rowNumber, _
        auditWs, auditRow, errorCount, warningCount)
End Function

Private Function ReadCellText(ByVal sourceCell 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) As String
    Dim rawValue As Variant
    rawValue = sourceCell.Value2
    If IsError(rawValue) Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Cell contains an Excel error.", errorCount, warningCount
        Exit Function
    End If
    ReadCellText = CStr(rawValue)
    If StrComp(ReadCellText, Trim$(ReadCellText), vbBinaryCompare) <> 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Value has leading or trailing spaces.", errorCount, warningCount
    End If
    If ContainsControlCharacter(ReadCellText) Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Value contains a control character.", errorCount, warningCount
    End If
End Function

Private Sub RequireTextValue(ByVal sourceCell 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)
    Dim rawValue As Variant
    rawValue = sourceCell.Value2
    If IsError(rawValue) Or IsEmpty(rawValue) Then Exit Sub
    If VarType(rawValue) <> vbString Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "WorkflowID must be stored as a text value, not only displayed with a text format.", _
            errorCount, warningCount
    ElseIf sourceCell.NumberFormat <> "@" Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "WARNING", _
            "The value is text, but format the WorkflowID column as Text before future imports.", _
            errorCount, warningCount
    End If
End Sub

Private Sub ValidateWorkflowId(ByVal workflowId As String, _
                               ByVal rowNumber As Long, _
                               ByVal workflowRows As Object, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount As Long)
    If Len(workflowId) = 0 Then Exit Sub
    If workflowRows.Exists(workflowId) Then
        WriteAuditRow auditWs, auditRow, rowNumber, "WorkflowID", "ERROR", _
            "Duplicate WorkflowID; first used on row " & _
            workflowRows(workflowId) & ".", errorCount, warningCount
    Else
        workflowRows.Add workflowId, rowNumber
    End If
End Sub

Private Sub ValidateExactValue(ByVal actualValue As String, _
                               ByVal allowedValues As Variant, _
                               ByVal fieldName As String, _
                               ByVal errorText As String, _
                               ByVal rowNumber As Long, _
                               ByVal auditWs As Worksheet, _
                               ByRef auditRow As Long, _
                               ByRef errorCount As Long, _
                               ByRef warningCount As Long)
    Dim item As Variant
    Dim isAllowed As Boolean
    For Each item In allowedValues
        If StrComp(actualValue, CStr(item), vbBinaryCompare) = 0 Then
            isAllowed = True
            Exit For
        End If
    Next item
    If Not isAllowed Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            errorText, errorCount, warningCount
    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
    If Len(listText) = 0 Then Exit Sub
    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, "docx", _
            "TemplateFiles", rowNumber, auditWs, auditRow, _
            errorCount, warningCount)
        If seen.Exists(templateName) Then
            WriteAuditRow auditWs, auditRow, rowNumber, "TemplateFiles", _
                "ERROR", "Duplicate template filename: " & 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 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 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
    If ContainsControlCharacter(fileName) Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Filename contains a Windows control character.", _
            errorCount, warningCount
        ValidateAssetName = False
    End If
    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 allowedExtension 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 dotPosition As Long
    Dim extension As String
    dotPosition = InStrRev(fileName, ".")
    If dotPosition > 1 And dotPosition < Len(fileName) Then
        extension = LCase$(Mid$(fileName, dotPosition + 1))
    End If
    ValidateFileExtension = _
        (StrComp(extension, allowedExtension, vbBinaryCompare) = 0)
    If Not ValidateFileExtension Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Template must use the ." & allowedExtension & _
            " extension: " & fileName, errorCount, warningCount
    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 attributes As Long
    On Error GoTo CheckFailed
    foundName = Dir$(filePath, FILE_ATTRIBUTES)
    If Len(foundName) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Template file was not found: " & filePath, _
            errorCount, warningCount
        Exit Sub
    End If
    attributes = GetAttr(filePath)
    If (attributes And vbDirectory) = vbDirectory Then
        WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
            "Expected a file but found a folder: " & filePath, _
            errorCount, warningCount
        Exit Sub
    End If
    WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "PASS", _
        "Template file entry was found: " & filePath, _
        errorCount, warningCount
    Exit Sub
CheckFailed:
    WriteAuditRow auditWs, auditRow, rowNumber, fieldName, "ERROR", _
        "Could not check path: " & filePath & ". " & Err.Description, _
        errorCount, warningCount
End Sub

Private Sub ValidateOutputPattern(ByVal patternText As String, _
                                  ByVal rowNumber As Long, _
                                  ByVal auditWs As Worksheet, _
                                  ByRef auditRow As Long, _
                                  ByRef errorCount As Long, _
                                  ByRef warningCount As Long)
    Dim forbidden As Variant
    Dim item As Variant
    If Len(patternText) = 0 Then Exit Sub
    forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    For Each item In forbidden
        If InStr(1, patternText, CStr(item), vbBinaryCompare) > 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, _
                "OutputNamePattern", "ERROR", _
                "Output pattern contains a forbidden filename character.", _
                errorCount, warningCount
            Exit For
        End If
    Next item
    If ContainsControlCharacter(patternText) Then
        WriteAuditRow auditWs, auditRow, rowNumber, _
            "OutputNamePattern", "ERROR", _
            "Output pattern contains a control character.", _
            errorCount, warningCount
    End If
End Sub

Private Sub ValidateApproval(ByVal releaseStatus As String, _
                             ByVal processOwner As String, _
                             ByVal reviewOwner 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 releaseStatus = STATUS_APPROVED And Len(reviewOwner) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "ReviewOwner", _
            "ERROR", "APPROVED requires a named ReviewOwner.", _
            errorCount, warningCount
    End If
    If releaseStatus = STATUS_APPROVED And _
       StrComp(processOwner, reviewOwner, vbTextCompare) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "ReviewOwner", _
            "WARNING", _
            "ProcessOwner and ReviewOwner are the same; confirm segregation of review.", _
            errorCount, warningCount
    End If
    If releaseStatus = STATUS_RETIRED And Len(notesText) = 0 Then
        WriteAuditRow auditWs, auditRow, rowNumber, "Notes", "WARNING", _
            "RETIRED should record the replacement or retention decision.", _
            errorCount, warningCount
    End If
End Sub

Private Function BuildContract(ByVal workflowShape As String, _
                               ByVal templateFiles As String, _
                               ByVal templateVersion As String, _
                               ByVal dataVersion As String, _
                               ByVal outputPattern As String) As String
    BuildContract = workflowShape & ChrW(30) & templateFiles & _
        ChrW(30) & templateVersion & ChrW(30) & dataVersion & _
        ChrW(30) & outputPattern
End Function

Private Sub ValidateFamilyContract(ByVal documentFamily As String, _
                                   ByVal contractText As String, _
                                   ByVal rowNumber As Long, _
                                   ByVal familyContracts As Object, _
                                   ByVal familyFirstRows As Object, _
                                   ByVal auditWs As Worksheet, _
                                   ByRef auditRow As Long, _
                                   ByRef errorCount As Long, _
                                   ByRef warningCount As Long)
    If familyContracts.Exists(documentFamily) Then
        If StrComp(CStr(familyContracts(documentFamily)), contractText, _
                   vbBinaryCompare) <> 0 Then
            WriteAuditRow auditWs, auditRow, rowNumber, _
                "DocumentFamily", "ERROR", _
                "Contract differs from the first row for this family (row " & _
                familyFirstRows(documentFamily) & ").", _
                errorCount, warningCount
        End If
    Else
        familyContracts.Add documentFamily, contractText
        familyFirstRows.Add documentFamily, rowNumber
    End If
End Sub

Private Function ContainsControlCharacter(ByVal textValue As String) As Boolean
    Dim position As Long
    Dim characterCode As Long
    For position = 1 To Len(textValue)
        characterCode = AscW(Mid$(textValue, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            ContainsControlCharacter = True
            Exit Function
        End If
    Next position
End Function

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

Private Function NextAuditSheetName(ByVal wb As Workbook) As String
    Dim candidate As String
    Dim suffix As Long
    candidate = AUDIT_PREFIX & Format$(Now, "yyyymmdd_hhnnss")
    If Not WorksheetExists(wb, candidate) Then
        NextAuditSheetName = candidate
        Exit Function
    End If
    For suffix = 1 To 99
        candidate = Left$(AUDIT_PREFIX & Format$(Now, "yyyymmdd_hhnnss"), _
            28) & "_" & Format$(suffix, "00")
        If Not WorksheetExists(wb, candidate) Then
            NextAuditSheetName = candidate
            Exit Function
        End If
    Next suffix
    Err.Raise vbObjectError + 3512, , _
        "Could not create a unique audit sheet name."
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)
    On Error GoTo 0
    WorksheetExists = Not ws Is Nothing
End Function

Private Sub PrepareAuditSheet(ByVal auditWs As Worksheet)
    auditWs.Cells(1, 1).Value = "SourceRow"
    auditWs.Cells(1, 2).Value = "Field"
    auditWs.Cells(1, 3).Value = "Result"
    auditWs.Cells(1, 4).Value = "Message"
    auditWs.Cells(1, 5).Value = "CheckedAt"
    auditWs.Cells(1, 6).Value = "AuditScope"
    auditWs.Rows(1).Font.Bold = True
    auditWs.Range("A1:F1").Interior.Color = RGB(31, 78, 121)
    auditWs.Range("A1:F1").Font.Color = RGB(255, 255, 255)
    auditWs.Columns("A:F").ColumnWidth = 24
    auditWs.Columns("D").ColumnWidth = 72
    auditWs.Columns("F").ColumnWidth = 48
End Sub

Private Sub WriteAuditRow(ByVal auditWs As Worksheet, _
                          ByRef auditRow As Long, _
                          ByVal sourceRow As Long, _
                          ByVal fieldName As String, _
                          ByVal resultText As String, _
                          ByVal messageText As String, _
                          ByRef errorCount As Long, _
                          ByRef warningCount As Long)
    auditWs.Cells(auditRow, 1).Value = sourceRow
    auditWs.Cells(auditRow, 2).Value = fieldName
    auditWs.Cells(auditRow, 3).Value = resultText
    auditWs.Cells(auditRow, 4).Value = messageText
    auditWs.Cells(auditRow, 5).Value = Now
    auditWs.Cells(auditRow, 6).Value = _
        "Read-only template presence and register consistency audit"
    If resultText = "ERROR" Then errorCount = errorCount + 1
    If resultText = "WARNING" Then warningCount = warningCount + 1
    auditRow = auditRow + 1
End Sub

Private Sub FinishAuditSheet(ByVal auditWs As Worksheet, _
                             ByVal errorCount As Long, _
                             ByVal warningCount As Long, _
                             ByVal approvedCount As Long)
    auditWs.Range("H1:I4").ClearContents
    auditWs.Range("H1").Value = "Summary"
    auditWs.Range("H2").Value = "Errors"
    auditWs.Range("I2").Value = errorCount
    auditWs.Range("H3").Value = "Warnings"
    auditWs.Range("I3").Value = warningCount
    auditWs.Range("H4").Value = "Rows marked APPROVED"
    auditWs.Range("I4").Value = approvedCount
    auditWs.Columns("A:I").VerticalAlignment = xlTop
    auditWs.Columns("D").WrapText = True
    auditWs.Range("A1:F1").AutoFilter
    auditWs.Activate
    auditWs.Range("A2").Select
    ActiveWindow.FreezePanes = True
End Sub

Official VBA references: Workbook.ReadOnly and Dictionary CompareMode.

Apply the standard in DocxForge Pro

DocxForge Pro supports two different workflow shapes. Standardize each family around the shape that matches its real output.

Contractor

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

Factory

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

DocxForge Pro Contractor shared case data for a standardized document workflow
Contractor: maintain the shared case data.
DocxForge Pro Contractor document list using different approved Word templates
Contractor: control the different documents and templates.
DocxForge Pro Factory Excel rows following a shared data contract
Factory: review independent rows under one data contract.
DocxForge Pro Factory settings using one approved standard Word template
Factory: confirm one approved template and output settings.
Run approved document workflows locally across teamsTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Contractor + FactoryShared templatesLocal processingDOCX + PDF
Start Free 7-Day Trial

Keep the standard alive after launch

Quarterly ownership review

Confirm that owners, reviewers, active teams, and downstream consumers are still correct.

Release inventory

Keep active and retired template versions distinguishable. Remove ambiguous shortcuts and uncontrolled duplicates.

Representative regression set

Retain approved test records that exercise blanks, long values, tables, images, names, and PDF pagination.

Exception register

Record valid team exceptions with an owner and review date instead of hiding them in local instructions.

Adoption evidence

Compare registered workflows with actual operators and output folders to find teams still using earlier routes.

Retirement decision

Archive old releases according to retention rules without leaving them available for accidental production use.

Frequently asked questions

What should be standardized across teams?

Standardize the data contract, approved Word templates, template and data-contract versions, output naming, review ownership, release status, and change process. Do not force unrelated document families into one template merely to reduce the template count.

Does standardization mean every team uses one Word template?

No. A document family should use one approved contract, but different families may need different templates. Factory uses one standard template for many independent rows. Contractor uses several different templates for the documents in one case.

What does the VBA audit change?

It adds a timestamped audit worksheet to the saved .xlsm workbook and saves that workbook. It checks register values and whether referenced template file entries exist. It does not open template contents, generate documents, or alter production files.

Can the audit prove that a template is approved?

No. A filename and version value are registry evidence, not authentication of the DOCX content. Keep the approved release in a controlled location and use your normal access, review, and integrity controls.

How should teams roll out a new template version?

Publish the new contract and template together, test representative records, record the reviewer, update the register, and retire the previous release only after the agreed transition and retention steps are complete.

Where do DocxForge Pro Factory and Contractor fit?

Use Factory when many independent Excel rows use one standard Word template. Use Contractor when one case reuses shared data across several different documents created from different Word templates.

Turn the approved standard into a repeatable local workflowRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Named contractsVersioned templatesReview ownershipControlled output
Start Free 7-Day Trial