Legal & Compliance Document Generation with Excel, Word, and PDF
Build a controlled Excel-to-Word workflow around approved templates, explicit version and approval fields, preflight checks, reviewed DOCX files, and fixed-layout PDF copies—without treating automation as legal approval.
Quick answer: automate an approved process, not legal judgment
Excel, Word, and PDF can make document production repeatable, but they do not decide whether a clause is correct, a signer has authority, or a retention rule is sufficient. Start with controls owned by qualified people, then automate the mechanical steps around them.
Qualified content owner
Legal or compliance owners approve the wording, required clauses, allowed variations, and review path.
Structured source data
Excel holds one defined value per field, including record ID, display dates, template version, and workflow status.
Controlled Word template
The template owns approved text, styles, headers, tables, page breaks, and signature areas.
Reviewed outputs
DOCX supports authorized review; PDF provides a fixed-layout copy for release or storage after that review.
A status cell that says APPROVED is a workflow gate, not proof of identity, authority, electronic signature, or legal validity. Connect generation to your organization's actual approval and records process.
Define the control model before the template
Write down what must be true before generation, what evidence is recorded, and what must be reviewed before release. This turns “make documents” into a process that can fail visibly.
1. Template register
Record the approved filename, version, owner, approval date, intended use, and replacement status.
2. Data contract
Define required columns, allowed values, display formats, maximum lengths, and which system owns each value.
3. Approval gate
Define who may approve a record and how the automation receives a trustworthy release decision.
4. Generation event
Record the record ID, source version, template version, operator or service, time, outcome, and exception.
5. Release review
Check content, layout, attachments, signatures, metadata, and the PDF against the authorized DOCX.
6. Retention and access
Apply the organization's approved access, storage, backup, retention, legal-hold, and disposal rules.
Choose the workflow by the unit of work
“Compliance documents” can mean a reviewed case pack or a repeatable batch. The correct module and test plan depend on that unit.
Use Contractor when one matter needs several different documents from different Word templates—for example, a notice, acknowledgement, cover letter, and internal record. Case data can be loaded from Excel and reviewed before generation.
Use Factory when each Excel row should create a separate document from the same approved Word template, with controlled naming and optional PDF output.
The Mail Merge versus document automation comparison explains why validation, naming, and repeatable output handling matter beyond basic field insertion.
Use Excel as a defined data contract
Keep calculation fields separate from document-ready text, and never hide legal logic inside an unexplained formula or free-form operator note.
RecordID
Use a unique stable identifier. Format the column as Text before import when leading zeros must survive.
EntityName
Store the reviewed legal or business name required for this document, not an informal label.
EffectiveDateDisplay
Prepare the approved document wording as text after validating the underlying date and business rule.
TemplateVersion
Require the expected version value in each Excel row. Separately verify that the actual Word template is the approved release; this macro does not authenticate the template file.
ApprovalStatus
Use a controlled workflow value such as APPROVED, not inconsistent free text entered during the run.
ApprovedBy
Record the authorized approver reference required by the process, while keeping the authoritative evidence elsewhere.
Use the controlled Word template for approved standard wording. If a clause genuinely varies, model the allowed choice explicitly and require the content owner to approve the decision logic. Do not let operators paste arbitrary clause text into a batch spreadsheet.
Treat the Word template as a controlled release artifact
A polished template is not enough. It needs an owner, version, intended use, test cases, and a clear route from draft to production.
Protect approved wording
Keep standard clauses and mandatory notices in the template or another controlled source, not in the macro.
Use stable placeholders
Give each bookmark or tag one documented meaning and keep it inside a consistent Word style.
Test difficult values
Use the longest names, addresses, dates, identifiers, optional sections, and multilingual values expected in production.
Retire superseded versions
Move old templates out of the production path and preserve them only according to the approved records process.
The reusable Word template guide covers styles, placeholders, variable-length content, and repeatable template testing.
Free VBA fallback: preflight one-template notice batches
This limited Excel VBA example generates one DOCX and one PDF per approved row from one Word template. It checks required values, the expected template-version value stored in Excel, an approval-status field, duplicate output names, existing files, and required bookmarks before or during generation. It does not authenticate the Word template file.
Microsoft Excel Desktop and Microsoft Word Desktop for Windows are required for this VBA example. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create a Records sheet with headers in row 1 and columns A–F for RecordID, EntityName, EffectiveDateDisplay, TemplateVersion, ApprovalStatus, and ApprovedBy. Store document-ready values as text. Format RecordID as Text before entering or importing IDs when leading zeros matter.
Put the approved release of ComplianceNoticeTemplate.docx in a Templates folder beside the workbook. Add the bookmarks RecordID, EntityName, EffectiveDate, TemplateVersion, and ApprovedBy. Change EXPECTED_TEMPLATE_VERSION only after the corresponding template version is approved. The macro compares text values; it does not verify the DOCX contents, signature, hash, or provenance.
Reproduce the preflight with two rows
Enter these values as text. The DRAFT row is deliberate: the complete preflight must stop before Word starts, so the first run creates no files.
| RecordID | EntityName | EffectiveDateDisplay | TemplateVersion | ApprovalStatus | ApprovedBy |
|---|---|---|---|---|---|
LC-0042 | Northwind Legal Services | 30 November 2026 | LC-2026-01 | APPROVED | A. Reviewer |
LC-0043 | Contoso Advisory | 15 December 2026 | LC-2026-01 | DRAFT | B. Reviewer |
First run: the macro reports that row 3 is not approved and creates no output. Change only row 3 to APPROVED and rerun. The output folder should then contain ComplianceNotice_LC-0042_Northwind Legal Services.docx, ComplianceNotice_LC-0042_Northwind Legal Services.pdf, ComplianceNotice_LC-0043_Contoso Advisory.docx, and ComplianceNotice_LC-0043_Contoso Advisory.pdf.
The VBA example uses Word bookmarks created through Insert → Bookmark, with names such as EntityName and no braces. DocxForge Pro templates use visible text tags such as {{EntityName}}. Do not paste a DocxForge tag into the VBA template and expect Bookmarks.Exists to find it.
Option Explicit
Private Const wdFormatXMLDocument As Long = 12
Private Const wdExportFormatPDF As Long = 17
Private Const wdExportOptimizeForPrint As Long = 0
Private Const wdDoNotSaveChanges As Long = 0
Private Const wdAlertsNone As Long = 0
Public Sub GenerateApprovedComplianceNotices()
Const SHEET_NAME As String = "Records"
Const TEMPLATE_SUBFOLDER As String = "Templates"
Const TEMPLATE_FILE As String = "ComplianceNoticeTemplate.docx"
Const OUTPUT_SUBFOLDER As String = "GeneratedNotices"
Const EXPECTED_TEMPLATE_VERSION As String = "LC-2026-01"
Const REQUIRED_APPROVAL_STATUS As String = "APPROVED"
Dim ws As Worksheet
Dim fso As Object
Dim plannedNames As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim templatePath As String
Dim outputFolder As String
Dim recordId As String
Dim entityName As String
Dim effectiveDateText As String
Dim templateVersion As String
Dim approvalStatus As String
Dim approvedBy As String
Dim fileStem As String
Dim docxPath As String
Dim pdfPath As String
Dim lastRow As Long
Dim lastCell As Range
Dim rowNumber As Long
Dim generatedCount As Long
Dim currentStep As String
Dim previousScreenUpdating As Boolean
Dim errorNumber As Long
Dim errorDescription As String
On Error GoTo CleanFail
previousScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
currentStep = "checking the workbook"
If Len(ThisWorkbook.Path) = 0 Then
Err.Raise vbObjectError + 2000, , _
"Save this Excel workbook before running the macro."
End If
Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
Set fso = CreateObject("Scripting.FileSystemObject")
Set plannedNames = CreateObject("Scripting.Dictionary")
templatePath = ThisWorkbook.Path & "\" & TEMPLATE_SUBFOLDER & _
"\" & TEMPLATE_FILE
outputFolder = ThisWorkbook.Path & "\" & OUTPUT_SUBFOLDER
If Not fso.FileExists(templatePath) Then
Err.Raise vbObjectError + 2001, , _
"Approved Word template not found: " & templatePath
End If
EnsureFolder fso, outputFolder
'Find the last occupied row across every required input column.
Set lastCell = ws.Range("A:F").Find( _
What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 2002, , _
"No records were found on the Records sheet."
End If
If lastCell.Row < 2 Then
Err.Raise vbObjectError + 2002, , _
"No records were found on the Records sheet."
End If
lastRow = lastCell.Row
'Pass 1: reject the complete batch before Word starts if any row is invalid.
For rowNumber = 2 To lastRow
currentStep = "validating Excel row " & rowNumber
recordId = RequiredCellText(ws.Cells(rowNumber, "A"), _
"RecordID", rowNumber)
entityName = RequiredCellText(ws.Cells(rowNumber, "B"), _
"EntityName", rowNumber)
effectiveDateText = RequiredCellText(ws.Cells(rowNumber, "C"), _
"EffectiveDateDisplay", rowNumber)
templateVersion = RequiredCellText(ws.Cells(rowNumber, "D"), _
"TemplateVersion", rowNumber)
approvalStatus = RequiredCellText(ws.Cells(rowNumber, "E"), _
"ApprovalStatus", rowNumber)
approvedBy = RequiredCellText(ws.Cells(rowNumber, "F"), _
"ApprovedBy", rowNumber)
If StrComp(templateVersion, EXPECTED_TEMPLATE_VERSION, _
vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2003, , _
"Unexpected template version on Excel row " & rowNumber & _
". Expected " & EXPECTED_TEMPLATE_VERSION & _
", received " & templateVersion & "."
End If
If StrComp(approvalStatus, REQUIRED_APPROVAL_STATUS, _
vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2004, , _
"Excel row " & rowNumber & _
" is not marked APPROVED."
End If
fileStem = SafeWindowsName(recordId & "_" & entityName)
If Len(fileStem) = 0 Then
Err.Raise vbObjectError + 2005, , _
"The output name is invalid on Excel row " & rowNumber & "."
End If
If plannedNames.Exists(UCase$(fileStem)) Then
Err.Raise vbObjectError + 2006, , _
"Duplicate output name after Windows filename cleanup on Excel row " & _
rowNumber & ": " & fileStem
End If
plannedNames(UCase$(fileStem)) = rowNumber
docxPath = outputFolder & "\ComplianceNotice_" & fileStem & ".docx"
pdfPath = outputFolder & "\ComplianceNotice_" & fileStem & ".pdf"
If fso.FileExists(docxPath) Or fso.FileExists(pdfPath) Then
Err.Raise vbObjectError + 2007, , _
"Output already exists for Excel row " & rowNumber & _
": " & fileStem
End If
Next rowNumber
currentStep = "starting Microsoft Word"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone
'Pass 2: generate only after every row passes the preflight.
For rowNumber = 2 To lastRow
recordId = Trim$(CStr(ws.Cells(rowNumber, "A").Value2))
entityName = Trim$(CStr(ws.Cells(rowNumber, "B").Value2))
effectiveDateText = Trim$(CStr(ws.Cells(rowNumber, "C").Value2))
templateVersion = Trim$(CStr(ws.Cells(rowNumber, "D").Value2))
approvedBy = Trim$(CStr(ws.Cells(rowNumber, "F").Value2))
fileStem = SafeWindowsName(recordId & "_" & entityName)
docxPath = outputFolder & "\ComplianceNotice_" & fileStem & ".docx"
pdfPath = outputFolder & "\ComplianceNotice_" & fileStem & ".pdf"
If fso.FileExists(docxPath) Or fso.FileExists(pdfPath) Then
Err.Raise vbObjectError + 2008, , _
"Output appeared after preflight for Excel row " & rowNumber & _
": " & fileStem
End If
currentStep = "opening the approved template for Excel row " & rowNumber
Set wdDoc = wdApp.Documents.Open(templatePath, False, True, False)
WriteRequiredBookmark wdDoc, "RecordID", recordId
WriteRequiredBookmark wdDoc, "EntityName", entityName
WriteRequiredBookmark wdDoc, "EffectiveDate", effectiveDateText
WriteRequiredBookmark wdDoc, "TemplateVersion", templateVersion
WriteRequiredBookmark wdDoc, "ApprovedBy", approvedBy
currentStep = "saving DOCX for Excel row " & rowNumber
wdDoc.SaveAs2 docxPath, wdFormatXMLDocument, False, "", False
currentStep = "exporting PDF for Excel row " & rowNumber
wdDoc.ExportAsFixedFormat pdfPath, wdExportFormatPDF, False, _
wdExportOptimizeForPrint
wdDoc.Close wdDoNotSaveChanges
Set wdDoc = Nothing
generatedCount = generatedCount + 1
Next rowNumber
MsgBox generatedCount & " compliance notice(s) generated in:" & _
vbCrLf & outputFolder, vbInformation
CleanExit:
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
Application.ScreenUpdating = previousScreenUpdating
Exit Sub
CleanFail:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If Not wdDoc Is Nothing Then wdDoc.Close wdDoNotSaveChanges
If Not wdApp Is Nothing Then wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
Application.ScreenUpdating = previousScreenUpdating
On Error GoTo 0
MsgBox "Stopped while " & currentStep & ":" & vbCrLf & _
errorDescription & vbCrLf & "Error " & errorNumber, vbExclamation
End Sub
Private Function RequiredCellText(ByVal sourceCell As Object, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
Dim result As String
If IsError(sourceCell.Value) Then
Err.Raise vbObjectError + 2010, , _
fieldName & " contains an Excel error on row " & rowNumber & "."
End If
result = Trim$(CStr(sourceCell.Value2))
If Len(result) = 0 Then
Err.Raise vbObjectError + 2011, , _
fieldName & " is blank on Excel row " & rowNumber & "."
End If
RequiredCellText = result
End Function
Private Sub WriteRequiredBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim originalStyle As Variant
If Len(Trim$(replacementText)) = 0 Then
Err.Raise vbObjectError + 2012, , _
"Required value is blank for Word bookmark: " & bookmarkName
End If
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2013, , _
"Required Word bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range
originalStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = originalStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2014, , _
"Word bookmark was not restored: " & bookmarkName
End If
If Len(Trim$(doc.Bookmarks(bookmarkName).Range.Text)) = 0 Then
Err.Raise vbObjectError + 2015, , _
"Required bookmark is empty after replacement: " & bookmarkName
End If
End Sub
Private Sub EnsureFolder(ByVal fso As Object, ByVal folderPath As String)
If Not fso.FolderExists(folderPath) Then fso.CreateFolder folderPath
End Sub
Private Function SafeWindowsName(ByVal rawValue As String) As String
Dim result As String
Dim invalidChar As Variant
Dim controlCode As Long
Dim dotPosition As Long
Dim stem As String
result = Trim$(rawValue)
For Each invalidChar In Array("\", "/", ":", "*", "?", Chr$(34), "<", ">", "|")
result = Replace(result, CStr(invalidChar), "_")
Next invalidChar
For controlCode = 0 To 31
result = Replace(result, Chr$(controlCode), "")
Next controlCode
If Len(result) > 90 Then result = Left$(result, 90)
Do While Len(result) > 0
If Right$(result, 1) <> "." And Right$(result, 1) <> " " Then Exit Do
result = Left$(result, Len(result) - 1)
Loop
If Len(result) = 0 Then Exit Function
stem = UCase$(result)
dotPosition = InStr(1, stem, ".", vbBinaryCompare)
If dotPosition > 0 Then stem = Left$(stem, dotPosition - 1)
Select Case stem
Case "CON", "PRN", "AUX", "NUL", _
"COM1", "COM2", "COM3", "COM4", "COM5", _
"COM6", "COM7", "COM8", "COM9", _
"LPT1", "LPT2", "LPT3", "LPT4", "LPT5", _
"LPT6", "LPT7", "LPT8", "LPT9"
result = "_" & result
End Select
SafeWindowsName = result
End FunctionThe code does not authenticate the person named in ApprovedBy, verify signing authority, create a tamper-evident audit log, apply a digital signature, enforce retention, or establish legal or regulatory compliance. It writes plain text and reapplies the bookmark range's Word style; mixed direct formatting, conditional clauses, headers, repeating tables, and several templates require separate tested logic.
It also has no automatic rollback. If the DOCX is saved and PDF export then fails, the partial DOCX remains in GeneratedNotices. Inspect and move or delete that incomplete test output before correcting the row and rerunning; the no-overwrite check intentionally prevents the next run from silently replacing it.
The example uses Word's documented SaveAs2 method, ExportAsFixedFormat method, and Bookmarks.Exists check. The code was reviewed statically; test it in Word Desktop with disposable files before adapting it to production.
Review the document before release
A successful save is not a successful legal or compliance review. Define checks for the source row, editable output, PDF copy, metadata, and distribution package.
Source and version
Confirm the record ID, entity, dates, allowed choices, approval status, approver reference, and template version.
Required content
Check that every mandatory clause, notice, field, attachment reference, and signature area appears as approved.
DOCX layout
Review styles, page breaks, tables, headers, footers, comments, tracked changes, and unresolved placeholders.
PDF comparison
Compare page count, visible text, fonts, links, images, and signature areas with the authorized DOCX.
Metadata inspection
Check hidden data and personal information on a copy before external sharing, using the organization's approved method.
Release record
Record what was released, to whom, when, under which template and source versions, and by whose authorization.
PDF export helps preserve page layout across recipients, but PDF files can still be altered. If authenticity, integrity, signatures, or long-term preservation are required, use the organization's approved signing, records, and verification controls.
Microsoft recommends using Document Inspector on a copy because removed information may not be recoverable. Review its results rather than removing every detected item automatically.
See both DocxForge Pro workflows before testing
DocxForge Pro is a local Windows suite for filling Word templates from reviewed structured data. Microsoft Word Desktop for Windows is required for Word processing. The suite supports document production; your organization remains responsible for legal review, approvals, access, signatures, retention, and release.
Load or enter one case, review its values, and select the different Word templates required for that matter.
Load the Excel rows, use one approved Word template, configure output naming and PDF options, and generate separate files for the selected records.
With VBA, every change to worksheet columns, bookmark names, version gates, filenames, error handling, or output logic requires code maintenance and another test cycle. DocxForge moves recurring tasks such as loading case or Excel data, selecting templates, configuring naming, and choosing DOCX/PDF output into the interface. Templates, input quality, approvals, and release controls still remain your responsibility.
Contractor: reviewed case data and document selection
Factory: Excel data and one-template batch generation
Know what document generation does not replace
A local generator can reduce copy-paste and standardize output. It is not, by itself, a governance, identity, signature, records-management, or compliance system.
Legal and regulatory analysis
Rules, jurisdictions, facts, exceptions, and required wording must be interpreted by qualified owners.
Identity and authority
A name in Excel does not authenticate a person or prove that the person may approve or sign.
Tamper-evident evidence
Ordinary DOCX, PDF, and spreadsheet files do not automatically create a protected event history.
Retention and legal hold
Generation does not determine how long files must be retained, where they may be stored, or when disposal must stop.
For a related local-first output pattern, see the offline proposal pack guide. The controls differ by document type, but the separation between source data, template, review, and fixed-layout output is reusable.
Run a six-record pilot before production
Use difficult records and deliberate failures. A pilot that contains only clean, short values does not test the controls that matter.
1. Freeze the test assets
Copy the approved workbook and Word template to an isolated test folder and record their versions.
2. Prepare six records
Include normal, long, accented, wrong-version, unapproved, and duplicate-output cases.
3. Confirm preflight failure
Verify that the wrong version, missing field, unapproved row, or duplicate name stops the batch before Word starts.
4. Review DOCX output
Check every required value, clause, bookmark, page break, header, footer, and unresolved placeholder.
5. Compare the PDF copy
Confirm visible content, pagination, links, fonts, images, and signature areas against the authorized DOCX.
6. Test correction and rerun
Correct one rejected row and confirm that existing output is not overwritten and the release record stays clear.
For deeper output testing, use the one-row-to-one-document guide to define the expected unit, filenames, and rerun behavior.
Frequently asked questions
Short answers about building controlled legal and compliance document workflows.
Does document automation make a document legally compliant?
No. Automation can apply approved templates, required-field checks, naming, and output steps. Qualified owners still need to determine the applicable rules, approve wording and decisions, and define review, signature, distribution, access, and retention controls.
Should approved clauses be stored in Excel?
Usually the controlled Word template should hold standard approved wording, while Excel supplies structured variable data. If clauses vary, model only the approved choices and have the content owner approve the decision logic.
Is an APPROVED cell enough evidence of authorization?
No. It can stop a simple macro from processing other rows, but it does not authenticate the approver, prove authority, create a signature, or produce a tamper-evident history. Keep authoritative approval evidence in the approved workflow or records system.
Is a generated PDF immutable?
No. PDF is useful as a fixed-layout review or delivery copy, but PDF files can be changed. Use approved digital-signature, integrity, verification, and records controls when those properties are required.
When should I use Contractor instead of Factory?
Use Contractor when one reviewed matter needs several different documents from different Word templates. Use Factory when many Excel rows should each produce a separate document from one Word template.
Can the VBA example create a complete compliance system?
No. It is a narrow one-template batch example. It does not authenticate users, manage signatures, enforce retention, protect an audit log, evaluate legal rules, or handle several templates and complex conditional clauses.