Excel → Word → PDF Workflow for Legal Intake Documents
Turn reviewed intake rows into separate Word and PDF summaries with one controlled template, exact display fields, guarded filenames, and a human release gate.
Quick answer: separate intake capture from matter acceptance
Use one LegalIntake row per prospective matter and one approved Word template for the standardized intake summary. Validate the full batch before Word starts, create separate files, and keep conflict clearance, engagement approval, consent, and release outside IntakeStatus.
This workflow creates an intake document from reviewed source data. It does not create an attorney-client relationship, confirm representation, or replace the organization’s conflict, identity, authorization, and records procedures.
Create the LegalIntake sheet in one exact A–M order
A IntakeID; B ClientName; C MatterType; D Jurisdiction; E ContactDisplay; F IntakeChannel; G IntakeDateDisplay; H DeadlineDisplay; I IntakeSummary; J AssignedProfessional; K OutputFormat; L IntakeStatus; M OutputName.
Identifiers
Store IntakeID as text and keep it unique. Use a separate Windows-safe OutputName without .docx or .pdf.
Client and matter
Review ClientName, MatterType, optional Jurisdiction, ContactDisplay, IntakeChannel, and AssignedProfessional.
Display fields
Store IntakeDateDisplay and DeadlineDisplay as document-ready text rather than relying on Excel formatting.
Narrative
Store IntakeSummary as reviewed text. Ordinary paragraph breaks are allowed; tabs and other control characters are rejected.
Output and status
Use DOCX, PDF, or BOTH and READY, HOLD, or DONE exactly. Only READY rows enter the generation pass.
No silent cleanup
The macro rejects outer spaces, unsafe filenames, duplicate identifiers, duplicate output names, and unexpected types.
Every populated row needs an exact, unique IntakeID and a valid IntakeStatus. HOLD and DONE may leave the remaining fields blank; if they include OutputName, it must still be safe and unique. The complete B–K and M contract applies only to READY.
Limit what enters the intake workbook and generated document
Minimum necessary data
Include only information required by the approved intake purpose. Do not copy entire emails, IDs, or evidence stores into one spreadsheet by default.
Contact display
Use a reviewed contact string intended for the document. Keep raw phone, email, and identity-verification data in their approved systems.
Narrative quality
Separate the client’s reported facts from staff conclusions. Avoid presenting unverified statements as findings.
Deadline handling
Treat DeadlineDisplay as reported intake information until a qualified person verifies the actual deadline and applicable rule.
Access and retention
Restrict the workbook, template, generated files, backups, and correction copies to authorized users and approved retention locations.
Matter acceptance
Use a separate controlled decision for conflict clearance, capacity, scope, terms, engagement, consent, and opening the official matter record.
Build one controlled Word intake template
Ten exact bookmarks
Create IntakeID, ClientName, MatterType, Jurisdiction, ContactDisplay, IntakeChannel, IntakeDateDisplay, DeadlineDisplay, IntakeSummary, and AssignedProfessional.
Stable bookmark ranges
Keep one style inside each text bookmark and exclude nested fields, content controls, images, and paragraph markers.
Approved fixed wording
Keep disclaimers, routing instructions, and standard labels in the template rather than duplicating them in every Excel row.
Clean review state
Turn off Track Changes, accept or reject revisions, and remove comments before the batch.
Release control
Approve the actual DOCX release separately. A matching filename and bookmark list do not authenticate its wording.
Unsigned intake
Do not treat a pasted signature image as consent, identity proof, or an executed engagement document.
Preflight the entire intake batch before Word starts
Workbook gate
Require a writable .xlsm workbook, an unprotected LegalIntake sheet, and exact A–M headers.
Status gate
Validate IntakeStatus first. Apply the full data and output contract only to READY rows.
Uniqueness gate
Reject duplicate IntakeID values and case-insensitive duplicate OutputName values.
Target gate
Stop if a requested DOCX or PDF already exists. SaveAs2 can overwrite, so the explicit check remains necessary.
Template gate
Check the ten bookmarks, Track Changes, revisions, and comments before processing the batch.
Change gate
Recheck the current Excel row plus template size and modification time before generating each document.
Free Excel VBA example: generate guarded legal intake documents
Save the workbook as .xlsm. Create the LegalIntake sheet, put LegalIntakeTemplate.docx in Templates beside the workbook, and run GenerateLegalIntakeBatch. Results go to GeneratedLegalIntake.
Option Explicit
Private Const DATA_SHEET As String = "LegalIntake"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const TEMPLATE_FILE As String = "LegalIntakeTemplate.docx"
Private Const OUTPUT_FOLDER As String = "\GeneratedLegalIntake"
Private Const FIRST_DATA_ROW As Long = 2
Private Const COL_INTAKE_ID As Long = 1
Private Const COL_CLIENT_NAME As Long = 2
Private Const COL_MATTER_TYPE As Long = 3
Private Const COL_JURISDICTION As Long = 4
Private Const COL_CONTACT_DISPLAY As Long = 5
Private Const COL_INTAKE_CHANNEL As Long = 6
Private Const COL_INTAKE_DATE As Long = 7
Private Const COL_DEADLINE As Long = 8
Private Const COL_INTAKE_SUMMARY As Long = 9
Private Const COL_ASSIGNED_PROFESSIONAL As Long = 10
Private Const COL_OUTPUT_FORMAT As Long = 11
Private Const COL_INTAKE_STATUS As Long = 12
Private Const COL_OUTPUT_NAME As Long = 13
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const FORMAT_DOCX As String = "DOCX"
Private Const FORMAT_PDF As String = "PDF"
Private Const FORMAT_BOTH As String = "BOTH"
Private Const WD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_EXPORT_OPTIMIZE_FOR_PRINT As Long = 0
Private Const WD_EXPORT_ALL_DOCUMENT As Long = 0
Private Const WD_EXPORT_DOCUMENT_CONTENT As Long = 0
Private Const WD_EXPORT_CREATE_HEADING_BOOKMARKS As Long = 1
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_ALERTS_NONE As Long = 0
Private Const FILE_ATTRIBUTES As Long = 39
Public Sub GenerateLegalIntakeBatch()
Dim wb As Workbook
Dim ws As Worksheet
Dim dataValues As Variant
Dim lastCell As Range
Dim lastRow As Long
Dim rowNumber As Long
Dim dataRow As Long
Dim readyCount As Long
Dim generatedCount As Long
Dim templatePath As String
Dim outputFolder As String
Dim intakeId As String
Dim clientName As String
Dim matterType As String
Dim jurisdiction As String
Dim contactDisplay As String
Dim intakeChannel As String
Dim intakeDateText As String
Dim deadlineText As String
Dim intakeSummary As String
Dim assignedProfessional As String
Dim outputFormat As String
Dim intakeStatus As String
Dim outputName As String
Dim templateSize As Double
Dim templateModified As Date
Dim docxPath As String
Dim pdfPath As String
Dim workingDocxPath As String
Dim runToken As String
Dim idRows As Object
Dim outputRows As Object
Dim wdApp As Object
Dim doc As Object
Dim errorNumber As Long
Dim errorMessage As String
Dim currentIntakeId As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 3300, , _
"Save this workbook as .xlsm before generating legal intake documents."
End If
If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 3301, , _
"Save the workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 3301, , _
"Open a writable workbook before generating legal intake documents."
End If
If wb.ProtectStructure Then
Err.Raise vbObjectError + 3302, , _
"Unprotect the workbook structure before generation."
End If
Set ws = RequireWorksheet(wb, DATA_SHEET)
If ws.ProtectContents Then
Err.Raise vbObjectError + 3303, , _
"Unprotect the LegalIntake sheet so DONE can be recorded."
End If
ValidateHeaders ws
Set lastCell = ws.Range("A:M").Find(What:="*", _
After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If lastCell Is Nothing Then
Err.Raise vbObjectError + 3304, , "The LegalIntake sheet is empty."
End If
If lastCell.Row < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 3304, , _
"The LegalIntake sheet has no legal intake rows."
End If
lastRow = lastCell.Row
Application.CalculateFull
dataValues = ws.Range(ws.Cells(FIRST_DATA_ROW, 1), _
ws.Cells(lastRow, COL_OUTPUT_NAME)).Value2
templatePath = wb.Path & TEMPLATE_FOLDER & "\" & TEMPLATE_FILE
outputFolder = wb.Path & OUTPUT_FOLDER
runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 100), "00000000")
If Not FileExists(templatePath) Then
Err.Raise vbObjectError + 3305, , _
"Word template is missing: " & templatePath
End If
templateSize = FileLen(templatePath)
templateModified = FileDateTime(templatePath)
Set idRows = CreateObject("Scripting.Dictionary")
idRows.CompareMode = vbTextCompare
Set outputRows = CreateObject("Scripting.Dictionary")
outputRows.CompareMode = vbTextCompare
' Pass 1: validate every row and every target before Word starts.
For dataRow = 1 To UBound(dataValues, 1)
rowNumber = dataRow + 1
If ArrayRowIsBlank(dataValues, dataRow) Then
Err.Raise vbObjectError + 3310, , _
"Blank row inside the data range: row " & rowNumber
End If
intakeStatus = ReadRequiredArrayText( _
dataValues(dataRow, COL_INTAKE_STATUS), "IntakeStatus", rowNumber)
ValidateIntakeStatus intakeStatus, rowNumber
intakeId = ReadRequiredArrayText(dataValues(dataRow, COL_INTAKE_ID), _
"IntakeID", rowNumber)
RequireTextStorage dataValues(dataRow, COL_INTAKE_ID), _
"IntakeID", rowNumber
ValidateIdentifier intakeId, "IntakeID", rowNumber
If idRows.Exists(intakeId) Then
Err.Raise vbObjectError + 3311, , _
"Duplicate IntakeID in rows " & idRows(intakeId) & _
" and " & rowNumber & ": " & intakeId
End If
idRows.Add intakeId, rowNumber
If intakeStatus = STATUS_READY Then
clientName = ReadRequiredArrayText( _
dataValues(dataRow, COL_CLIENT_NAME), _
"ClientName", rowNumber)
matterType = ReadRequiredArrayText( _
dataValues(dataRow, COL_MATTER_TYPE), "MatterType", rowNumber)
jurisdiction = ReadOptionalArrayText( _
dataValues(dataRow, COL_JURISDICTION), _
"Jurisdiction", rowNumber)
contactDisplay = ReadRequiredArrayText( _
dataValues(dataRow, COL_CONTACT_DISPLAY), _
"ContactDisplay", rowNumber)
intakeChannel = ReadRequiredArrayText( _
dataValues(dataRow, COL_INTAKE_CHANNEL), _
"IntakeChannel", rowNumber)
intakeDateText = ReadRequiredDisplayText( _
dataValues(dataRow, COL_INTAKE_DATE), _
"IntakeDateDisplay", rowNumber)
deadlineText = ReadRequiredDisplayText( _
dataValues(dataRow, COL_DEADLINE), _
"DeadlineDisplay", rowNumber)
intakeSummary = ReadRequiredMultilineText( _
dataValues(dataRow, COL_INTAKE_SUMMARY), _
"IntakeSummary", rowNumber)
assignedProfessional = ReadRequiredArrayText( _
dataValues(dataRow, COL_ASSIGNED_PROFESSIONAL), _
"AssignedProfessional", rowNumber)
outputFormat = ReadRequiredArrayText( _
dataValues(dataRow, COL_OUTPUT_FORMAT), _
"OutputFormat", rowNumber)
outputName = ReadRequiredArrayText( _
dataValues(dataRow, COL_OUTPUT_NAME), _
"OutputName", rowNumber)
ValidateOutputFormat outputFormat, rowNumber
Else
outputName = ReadOptionalArrayText( _
dataValues(dataRow, COL_OUTPUT_NAME), _
"OutputName", rowNumber)
End If
If Len(outputName) > 0 Then
If outputRows.Exists(outputName) Then
Err.Raise vbObjectError + 3312, , _
"Duplicate OutputName in rows " & outputRows(outputName) & _
" and " & rowNumber & ": " & outputName
End If
outputRows.Add outputName, rowNumber
ValidateFileStem outputName, rowNumber
End If
If intakeStatus = STATUS_READY Then
readyCount = readyCount + 1
docxPath = outputFolder & "\" & outputName & ".docx"
pdfPath = outputFolder & "\" & outputName & ".pdf"
If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable docxPath, rowNumber
End If
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable pdfPath, rowNumber
End If
End If
Next dataRow
If readyCount = 0 Then
Err.Raise vbObjectError + 3313, , _
"No READY legal intake documents were found."
End If
EnsureFolder outputFolder
wb.Save
' Validate all required Word bookmarks once before processing the batch.
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = WD_ALERTS_NONE
wdApp.ScreenUpdating = False
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
ValidateTemplateBookmarks doc
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
' Pass 2: generate from the cached values captured before preflight.
For dataRow = 1 To UBound(dataValues, 1)
rowNumber = dataRow + 1
intakeStatus = CStr(dataValues(dataRow, COL_INTAKE_STATUS))
If intakeStatus = STATUS_READY Then
RequireRowUnchanged ws, rowNumber, dataValues, dataRow
RequireSourceUnchanged templatePath, templateSize, templateModified
intakeId = CStr(dataValues(dataRow, COL_INTAKE_ID))
currentIntakeId = intakeId
clientName = CStr(dataValues(dataRow, COL_CLIENT_NAME))
matterType = CStr(dataValues(dataRow, COL_MATTER_TYPE))
jurisdiction = CStr(dataValues(dataRow, COL_JURISDICTION))
contactDisplay = CStr(dataValues(dataRow, COL_CONTACT_DISPLAY))
intakeChannel = CStr(dataValues(dataRow, COL_INTAKE_CHANNEL))
intakeDateText = CStr(dataValues(dataRow, COL_INTAKE_DATE))
deadlineText = CStr(dataValues(dataRow, COL_DEADLINE))
intakeSummary = NormalizeMultilineText( _
CStr(dataValues(dataRow, COL_INTAKE_SUMMARY)))
assignedProfessional = CStr(dataValues(dataRow, COL_ASSIGNED_PROFESSIONAL))
outputFormat = CStr(dataValues(dataRow, COL_OUTPUT_FORMAT))
outputName = CStr(dataValues(dataRow, COL_OUTPUT_NAME))
docxPath = outputFolder & "\" & outputName & ".docx"
pdfPath = outputFolder & "\" & outputName & ".pdf"
If outputFormat = FORMAT_PDF Then
workingDocxPath = outputFolder & "\.__pdf_stage_" & _
outputName & "_" & runToken & ".docx"
RequireTargetAvailable workingDocxPath, rowNumber
Else
workingDocxPath = docxPath
RequireTargetAvailable docxPath, rowNumber
End If
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
RequireTargetAvailable pdfPath, rowNumber
End If
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
WriteRequiredBookmark doc, "IntakeID", intakeId
WriteRequiredBookmark doc, "ClientName", clientName
WriteRequiredBookmark doc, "MatterType", matterType
WriteRequiredBookmark doc, "Jurisdiction", jurisdiction
WriteRequiredBookmark doc, "ContactDisplay", contactDisplay
WriteRequiredBookmark doc, "IntakeChannel", intakeChannel
WriteRequiredBookmark doc, "IntakeDateDisplay", intakeDateText
WriteRequiredBookmark doc, "DeadlineDisplay", deadlineText
WriteRequiredBookmark doc, "IntakeSummary", intakeSummary
WriteRequiredBookmark doc, "AssignedProfessional", assignedProfessional
ValidateGeneratedDocument doc
doc.SaveAs2 workingDocxPath, WD_FORMAT_XML_DOCUMENT
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
doc.ExportAsFixedFormat pdfPath, WD_EXPORT_FORMAT_PDF, _
False, WD_EXPORT_OPTIMIZE_FOR_PRINT, _
WD_EXPORT_ALL_DOCUMENT, 1, 1, _
WD_EXPORT_DOCUMENT_CONTENT, False, True, _
WD_EXPORT_CREATE_HEADING_BOOKMARKS, True, True, False
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If outputFormat = FORMAT_PDF Then
If FileExists(workingDocxPath) Then Kill workingDocxPath
End If
workingDocxPath = vbNullString
ValidateGeneratedOutputs outputFormat, docxPath, pdfPath, rowNumber
SaveDoneStatus wb, ws, rowNumber
generatedCount = generatedCount + 1
End If
Next dataRow
wdApp.ScreenUpdating = True
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
currentIntakeId = vbNullString
MsgBox generatedCount & " legal intake document(s) generated.", _
vbInformation, "Legal intake document batch"
Exit Sub
CleanFail:
errorNumber = Err.Number
errorMessage = Err.Description
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.ScreenUpdating = True
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
On Error GoTo 0
MsgBox "Generation stopped." & vbCrLf & _
IIf(Len(currentIntakeId) > 0, _
"Current IntakeID: " & currentIntakeId & vbCrLf, "") & _
"Error " & errorNumber & ": " & errorMessage & vbCrLf & _
"Review GeneratedLegalIntake before retrying. Existing files are not deleted.", _
vbCritical, "Legal intake document batch"
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 + 3320, , _
"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("IntakeID", "ClientName", "MatterType", _
"Jurisdiction", "ContactDisplay", "IntakeChannel", _
"IntakeDateDisplay", "DeadlineDisplay", "IntakeSummary", "AssignedProfessional", _
"OutputFormat", "IntakeStatus", "OutputName")
For columnNumber = 1 To 13
rawHeader = ws.Cells(1, columnNumber).Value2
If IsError(rawHeader) Then
Err.Raise vbObjectError + 3321, , _
"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 + 3321, , _
"Header " & ws.Cells(1, columnNumber).Address(False, False) & _
" must match exactly: " & headers(columnNumber - 1) & "."
End If
Next columnNumber
End Sub
Private Function ArrayRowIsBlank(ByVal values As Variant, _
ByVal dataRow As Long) As Boolean
Dim columnNumber As Long
For columnNumber = 1 To COL_OUTPUT_NAME
If IsError(values(dataRow, columnNumber)) Then Exit Function
If Len(CStr(values(dataRow, columnNumber))) > 0 Then Exit Function
Next columnNumber
ArrayRowIsBlank = True
End Function
Private Function ReadRequiredArrayText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
If IsError(rawValue) Then
Err.Raise vbObjectError + 3322, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 3322, , _
fieldName & " must be stored as text in row " & rowNumber & "."
End If
ReadRequiredArrayText = CStr(rawValue)
If Len(ReadRequiredArrayText) = 0 Then
Err.Raise vbObjectError + 3323, , _
fieldName & " is required in row " & rowNumber & "."
End If
ValidateOuterSpaces ReadRequiredArrayText, fieldName, rowNumber
ValidateSingleLine ReadRequiredArrayText, fieldName, rowNumber
ValidateControlCharacters ReadRequiredArrayText, fieldName, rowNumber
End Function
Private Function ReadOptionalArrayText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
If IsError(rawValue) Then
Err.Raise vbObjectError + 3322, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
If Len(CStr(rawValue)) > 0 And VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 3322, , _
fieldName & " must be stored as text in row " & rowNumber & "."
End If
ReadOptionalArrayText = CStr(rawValue)
If Len(ReadOptionalArrayText) > 0 Then
ValidateOuterSpaces ReadOptionalArrayText, fieldName, rowNumber
ValidateSingleLine ReadOptionalArrayText, fieldName, rowNumber
ValidateControlCharacters ReadOptionalArrayText, fieldName, rowNumber
End If
End Function
Private Function ReadRequiredDisplayText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
RequireTextStorage rawValue, fieldName, rowNumber
ReadRequiredDisplayText = ReadRequiredArrayText( _
rawValue, fieldName, rowNumber)
End Function
Private Function ReadRequiredMultilineText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long) As String
Dim position As Long
Dim characterCode As Long
Dim meaningfulText As String
If IsError(rawValue) Then
Err.Raise vbObjectError + 3322, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 3322, , _
fieldName & " must be stored as text in row " & rowNumber & "."
End If
ReadRequiredMultilineText = CStr(rawValue)
If Len(ReadRequiredMultilineText) = 0 Then
Err.Raise vbObjectError + 3323, , _
fieldName & " is required in row " & rowNumber & "."
End If
meaningfulText = Replace(ReadRequiredMultilineText, vbCr, vbNullString)
meaningfulText = Replace(meaningfulText, vbLf, vbNullString)
If Len(Trim$(meaningfulText)) = 0 Then
Err.Raise vbObjectError + 3323, , _
fieldName & " must contain meaningful text in row " & _
rowNumber & "."
End If
ValidateOuterSpaces ReadRequiredMultilineText, fieldName, rowNumber
For position = 1 To Len(ReadRequiredMultilineText)
characterCode = AscW(Mid$(ReadRequiredMultilineText, position, 1))
If characterCode >= 0 And characterCode < 32 And _
characterCode <> 10 And characterCode <> 13 Then
Err.Raise vbObjectError + 3324, , _
fieldName & " contains an unsupported control character in row " & _
rowNumber & "."
End If
Next position
End Function
Private Function NormalizeMultilineText(ByVal valueText As String) As String
NormalizeMultilineText = Replace(valueText, vbCrLf, vbLf)
NormalizeMultilineText = Replace(NormalizeMultilineText, vbCr, vbLf)
NormalizeMultilineText = Replace(NormalizeMultilineText, vbLf, vbCr)
End Function
Private Sub RequireTextStorage(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
If IsError(rawValue) Then
Err.Raise vbObjectError + 3322, , _
fieldName & " contains an Excel error in row " & rowNumber & "."
End If
If VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 3325, , _
fieldName & " must be stored as text in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateOuterSpaces(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
If StrComp(valueText, Trim$(valueText), vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3326, , _
fieldName & " has leading or trailing spaces in row " & _
rowNumber & "."
End If
End Sub
Private Sub ValidateSingleLine(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber As Long)
If InStr(valueText, vbCr) > 0 Or InStr(valueText, vbLf) > 0 Or _
InStr(valueText, vbTab) > 0 Then
Err.Raise vbObjectError + 3327, , _
fieldName & " must be one line in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateControlCharacters(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber 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
Err.Raise vbObjectError + 3324, , _
fieldName & " contains an unsupported control character in row " & _
rowNumber & "."
End If
Next position
End Sub
Private Sub ValidateIdentifier(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal rowNumber 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
Err.Raise vbObjectError + 3328, , _
fieldName & " may use only letters, digits, underscore, and hyphen " & _
"in row " & rowNumber & "."
End If
Next position
End Sub
Private Sub ValidateOutputFormat(ByVal valueText As String, _
ByVal rowNumber As Long)
If valueText <> FORMAT_DOCX And valueText <> FORMAT_PDF And _
valueText <> FORMAT_BOTH Then
Err.Raise vbObjectError + 3329, , _
"OutputFormat must be DOCX, PDF, or BOTH in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateIntakeStatus(ByVal valueText As String, _
ByVal rowNumber As Long)
If valueText <> STATUS_READY And valueText <> STATUS_HOLD And _
valueText <> STATUS_DONE Then
Err.Raise vbObjectError + 3330, , _
"IntakeStatus must be READY, HOLD, or DONE in row " & rowNumber & "."
End If
End Sub
Private Sub ValidateFileStem(ByVal fileStem As String, _
ByVal rowNumber As Long)
Dim forbidden As Variant
Dim item As Variant
Dim position As Long
Dim characterCode As Long
Dim baseName As String
forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
If Len(fileStem) > 120 Then
Err.Raise vbObjectError + 3331, , _
"OutputName exceeds 120 characters in row " & rowNumber & "."
End If
If Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
Err.Raise vbObjectError + 3331, , _
"OutputName ends with a dot or space in row " & rowNumber & "."
End If
If LCase$(Right$(fileStem, 5)) = ".docx" Or _
LCase$(Right$(fileStem, 4)) = ".pdf" Then
Err.Raise vbObjectError + 3331, , _
"Store OutputName without .docx or .pdf in row " & rowNumber & "."
End If
For Each item In forbidden
If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 3331, , _
"OutputName contains a forbidden character in row " & _
rowNumber & "."
End If
Next item
For position = 1 To Len(fileStem)
characterCode = AscW(Mid$(fileStem, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Err.Raise vbObjectError + 3331, , _
"OutputName contains a Windows control character in row " & _
rowNumber & "."
End If
Next position
baseName = fileStem
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(UCase$(baseName)) Then
Err.Raise vbObjectError + 3331, , _
"OutputName starts with a reserved Windows name in row " & _
rowNumber & "."
End If
End Sub
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 ValidateTemplateBookmarks(ByVal doc As Object)
Dim names As Variant
Dim item As Variant
If doc.TrackRevisions Then
Err.Raise vbObjectError + 3332, , _
"Turn off Track Changes in LegalIntakeTemplate.docx."
End If
If doc.Revisions.Count > 0 Then
Err.Raise vbObjectError + 3332, , _
"Accept or reject all tracked revisions in the legal intake template."
End If
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 3332, , _
"Remove all comments from the legal intake template."
End If
names = Array("IntakeID", "ClientName", "MatterType", _
"Jurisdiction", "ContactDisplay", "IntakeChannel", _
"IntakeDateDisplay", "DeadlineDisplay", _
"IntakeSummary", "AssignedProfessional")
For Each item In names
If Not doc.Bookmarks.Exists(CStr(item)) Then
Err.Raise vbObjectError + 3332, , _
"Required Word bookmark is missing: " & item
End If
Next item
End Sub
Private Sub ValidateGeneratedDocument(ByVal doc As Object)
If doc.TrackRevisions Or doc.Revisions.Count > 0 Or _
doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 3339, , _
"Generated legal intake document contains tracked changes or comments."
End If
End Sub
Private Sub WriteRequiredBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim targetStyle As Variant
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3333, , _
"Required Word bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Or _
targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 3334, , _
"Bookmark contains a field, content control, or image: " & bookmarkName
End If
targetStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = targetStyle
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 3335, , _
"Bookmark was not restored: " & bookmarkName
End If
End Sub
Private Sub ValidateGeneratedOutputs(ByVal outputFormat As String, _
ByVal docxPath As String, _
ByVal pdfPath As String, _
ByVal rowNumber As Long)
If outputFormat = FORMAT_DOCX Or outputFormat = FORMAT_BOTH Then
If Not FileExists(docxPath) Then
Err.Raise vbObjectError + 3343, , _
"DOCX output is missing for row " & rowNumber & "."
End If
If FileLen(docxPath) = 0 Then
Err.Raise vbObjectError + 3344, , _
"DOCX output is empty for row " & rowNumber & "."
End If
End If
If outputFormat = FORMAT_PDF Or outputFormat = FORMAT_BOTH Then
If Not FileExists(pdfPath) Then
Err.Raise vbObjectError + 3345, , _
"PDF output is missing for row " & rowNumber & "."
End If
If FileLen(pdfPath) = 0 Then
Err.Raise vbObjectError + 3346, , _
"PDF output is empty for row " & rowNumber & "."
End If
End If
End Sub
Private Sub RequireTargetAvailable(ByVal filePath As String, _
ByVal rowNumber As Long)
If FileExists(filePath) Then
Err.Raise vbObjectError + 3336, , _
"Output already exists for row " & rowNumber & ": " & filePath
End If
End Sub
Private Function FileExists(ByVal filePath As String) As Boolean
Dim foundName As String
Dim checkErrorNumber As Long
Dim checkErrorDescription As String
On Error GoTo CheckFailed
foundName = Dir$(filePath, FILE_ATTRIBUTES)
FileExists = (Len(foundName) > 0)
Exit Function
CheckFailed:
checkErrorNumber = Err.Number
checkErrorDescription = Err.Description
Err.Clear
Err.Raise vbObjectError + 3338, "FileExists", _
"Could not check path: " & filePath & vbCrLf & _
"File-system error " & checkErrorNumber & ": " & _
checkErrorDescription
End Function
Private Sub EnsureFolder(ByVal folderPath As String)
If Len(Dir$(folderPath, vbDirectory)) = 0 Then MkDir folderPath
If Len(Dir$(folderPath, vbDirectory)) = 0 Then
Err.Raise vbObjectError + 3337, , _
"Could not create output folder: " & folderPath
End If
End Sub
Private Sub RequireRowUnchanged(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal cachedValues As Variant, _
ByVal dataRow As Long)
Dim columnNumber As Long
Dim currentValue As Variant
Dim cachedValue As Variant
For columnNumber = 1 To COL_OUTPUT_NAME
currentValue = ws.Cells(rowNumber, columnNumber).Value2
cachedValue = cachedValues(dataRow, columnNumber)
If IsError(currentValue) Or IsError(cachedValue) Then
Err.Raise vbObjectError + 3340, , _
"Excel row changed or contains an error: row " & rowNumber
End If
If VarType(currentValue) <> VarType(cachedValue) Or _
StrComp(CStr(currentValue), CStr(cachedValue), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 3340, , _
"Excel row changed after preflight: row " & rowNumber
End If
Next columnNumber
End Sub
Private Sub RequireSourceUnchanged(ByVal filePath As String, _
ByVal expectedSize As Double, _
ByVal expectedModified As Date)
If Not FileExists(filePath) Then
Err.Raise vbObjectError + 3341, , _
"Legal intake template is no longer available: " & filePath
End If
If CDbl(FileLen(filePath)) <> expectedSize Or _
FileDateTime(filePath) <> expectedModified Then
Err.Raise vbObjectError + 3341, , _
"Legal intake template changed after preflight. Stop and review it again."
End If
End Sub
Private Sub SaveDoneStatus(ByVal wb As Workbook, _
ByVal ws As Worksheet, _
ByVal rowNumber As Long)
Dim saveErrorNumber As Long
Dim saveErrorDescription As String
ws.Cells(rowNumber, COL_INTAKE_STATUS).Value2 = STATUS_DONE
On Error GoTo SaveFailed
wb.Save
On Error GoTo 0
Exit Sub
SaveFailed:
saveErrorNumber = Err.Number
saveErrorDescription = Err.Description
Err.Clear
ws.Cells(rowNumber, COL_INTAKE_STATUS).Value2 = STATUS_READY
On Error GoTo 0
Err.Raise vbObjectError + 3342, "SaveDoneStatus", _
"The legal intake files were created, but DONE was not saved for row " & _
rowNumber & ". Excel error " & saveErrorNumber & ": " & _
saveErrorDescription
End SubThe example uses Word’s documented bookmark checks, DOCX saving, and PDF export.
Review every intake document before routing or release
Identity and contact
Match IntakeID, client name, intended contact details, intake channel, and recipient.
Matter description
Confirm matter type, jurisdiction, narrative, source attribution, and any uncertainty or missing information.
Dates and deadlines
Verify intake date and independently confirm every stated deadline before relying on it.
Assignment and conflicts
Confirm the assigned professional, conflict result, capacity, scope, and the decision to proceed or decline.
Document output
Open each DOCX and PDF. Inspect pagination, headings, tables, headers, footers, metadata, and all pages.
Records and communication
Record reviewer, decision, approved version, delivery channel, corrections, and retention outside IntakeStatus.
Know what the free example does not guarantee
No conflict clearance
The macro cannot identify every adverse party, relationship, or organizational conflict.
No engagement
A generated intake document does not establish representation, scope, fees, consent, or acceptance.
No legal deadline calculation
DeadlineDisplay is text supplied by the workbook and is not a rules engine or legal determination.
No cryptographic template check
Size and modification time detect ordinary changes but do not prove file identity.
No atomic file-and-status commit
Files and the Excel DONE status are separate operations; reconcile partial results after a failure.
No automatic distribution
Email, portals, identity checks, consent, e-signatures, and official matter creation remain separate workflows.
Use Factory for standardized legal intake summaries
DocxForge Pro Factory maps many reviewed Excel rows to one standard Word template and creates a separate output for each row. That is the legal intake pattern described here.
Use it for many intake records that share one approved summary template. When one matter needs several different documents from different templates, use Contractor instead.


Frequently asked questions
Is this a Factory workflow?
Yes. Each READY row is one intake record, all rows use one approved LegalIntakeTemplate.docx, and every row produces its own DOCX and/or PDF.
Can HOLD and DONE rows be incomplete?
Yes. They still need an exact, unique IntakeID and a valid IntakeStatus. If OutputName is present, it must be safe and unique. The complete field contract applies only to READY rows.
Can IntakeSummary contain several paragraphs?
Yes. Store it as text with ordinary line breaks. The macro normalizes those line breaks for Word and rejects tabs and other unsupported control characters.
Does DONE mean the firm accepted the matter?
No. DONE confirms that requested files exist, are nonempty, and the status was saved. Conflict clearance, engagement approval, legal review, and release remain separate decisions.
Does this workflow collect a legally valid signature?
No. The free example creates an unsigned intake document. Consent, engagement, identity verification, and signatures require a separately approved process.
When should Contractor be used instead?
Use Contractor when one legal matter needs several different documents from different Word templates, such as an intake summary, engagement letter, conflict memo, and checklist. Use Factory for many intake rows using one standard template.