How to Create Employee Document Packs from Excel and Word
Reuse one reviewed employee record across an offer, agreement, onboarding checklist, and other approved Word templates, then create separate DOCX and PDF files.
Quick answer: separate the employee record from the document register
Keep one reviewed employee record in EmployeeCase. Define every offer, agreement, checklist, acknowledgment, or other output in DocumentRegister. Each approved row names a different Word template, output suffix, exact bookmark contract, and optional signature image.
Use Contractor when one employee needs several different documents. If many employees each need the same document from one template, use Factory instead.
Define the one-row EmployeeCase contract
A — PackID
Unique Windows-safe identifier for this employee document pack.
B — EmployeeID
Stable employee identifier stored as text when leading zeros matter.
C — EmployeeName
Reviewed name exactly as the approved documents should display it.
D — JobTitle
Approved job title or role name.
E — StartDateDisplay
Document-ready date text rather than an Excel date serial.
F — CompensationDisplay
Optional reviewed compensation text; required only by templates that list it.
G — DepartmentName
Approved department, business unit, or employing entity label.
H — HRSignatoryName
Printed HR representative or authorized signatory name.
I — PackStatus
Use exactly READY, HOLD, or DONE.
Use DocumentRegister for the different employee documents
DocumentKey
Unique purpose such as OFFER, AGREEMENT, ONBOARDING_CHECKLIST, or POLICY_ACK.
TemplateFile
Unique DOCX filename stored in the Templates folder.
OutputSuffix
Unique Windows-safe suffix combined with PackID.
RequiredBookmarks
Exact semicolon-separated contract that must include PackID and EmployeeName.
SignatureFile
PNG, JPG, or JPEG filename only when SignatureImage is part of the template contract.
DocumentStatus
Use exactly APPROVED or RETIRED.
PackID;EmployeeID;EmployeeName;JobTitle;StartDateDisplay;DepartmentName;HRSignatoryName;SignatureImage
Make every Word template declare its exact data contract
Exact bookmark names
Use PackID, EmployeeID, EmployeeName, JobTitle, StartDateDisplay, CompensationDisplay, DepartmentName, HRSignatoryName, and SignatureImage.
Only required fields
List only the bookmarks the specific document uses. The macro rejects undeclared supported bookmarks in that template.
Consistent placeholder style
Keep one style inside each text bookmark and exclude nested fields, content controls, images, and paragraph markers.
Controlled signature
List SignatureImage and an approved signature filename together, or leave both absent.
Clean review state
Turn off Track Changes, resolve revisions in every story, and remove comments.
Separate templates
Do not hide several unrelated documents inside one master template. Give each approved purpose its own release-controlled DOCX.
Limit the employee data and signature assets used by the pack
Minimum data
Include only fields required by the approved documents. Do not add sensitive personal data merely because it exists elsewhere.
Document-ready compensation
Store only the approved display text required by the template. Do not rely on Excel number formatting to create contract wording.
Access control
Restrict the workbook, templates, signatures, run snapshots, and completed output to authorized HR staff.
Signature boundary
An embedded image is not a certificate-backed digital signature or proof that the named person approved the document.
Delivery
Use an approved secure channel and verify the recipient before sending employee documents.
Retention
Apply the organization’s retention, correction, hold, and deletion rules outside the generation macro.
Move the employee pack through six gates
1. Review EmployeeCase
Confirm identity, role, date, compensation display, department, signatory name, and READY status.
2. Approve required documents
Mark only the templates required for this employee as APPROVED in DocumentRegister.
3. Freeze the inputs
Copy every approved template and unique signature asset into the run-specific InputSnapshot.
4. Validate each template
Check its exact bookmark contract, comments, and tracked revisions before filling it.
5. Stage and move pairs
Create nonempty DOCX/PDF pairs and move them into Completed without overwriting an existing target.
6. Reconcile and release
Confirm the case and full register stayed unchanged, then review and approve every output separately.
Free Excel VBA example: generate one guarded employee document pack
Save the workbook as .xlsm. Create EmployeeCase and DocumentRegister. Put approved templates in Templates and any separately authorized signature images in Assets\Signatures. Run GenerateEmployeeDocumentPack.
Option Explicit
Private Const PACK_SHEET As String = "EmployeeCase"
Private Const REGISTER_SHEET As String = "DocumentRegister"
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const DOC_APPROVED As String = "APPROVED"
Private Const DOC_RETIRED As String = "RETIRED"
Private Const WD_FORMAT_DOCX As Long = 12
Private Const WD_EXPORT_PDF As Long = 17
Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_COLLAPSE_START As Long = 1
Private Const OFFICE_TRUE As Long = -1
Private Const POINTS_PER_CM As Double = 28.3464567
Public Sub GenerateEmployeeDocumentPack()
Dim wb As Workbook
Dim packWs As Worksheet
Dim registerWs As Worksheet
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
Dim packData As Variant
Dim registerData As Variant
Dim templateSnapshots As Object
Dim signatureSnapshots As Object
Dim documentKeys As Object
Dim templateFiles As Object
Dim outputSuffixes As Object
Dim requiredBookmarks As Object
Dim packId As String
Dim employeeId As String
Dim employeeName As String
Dim jobTitle As String
Dim startDateDisplay As String
Dim compensationDisplay As String
Dim departmentName As String
Dim hrSignatoryName As String
Dim packStatus As String
Dim documentKey As String
Dim templateFile As String
Dim outputSuffix As String
Dim bookmarkContract As String
Dim signatureFile As String
Dim documentStatus As String
Dim templateFolder As String
Dim signatureFolder As String
Dim outputRoot As String
Dim runFolder As String
Dim inputFolder As String
Dim templateSnapshotFolder As String
Dim signatureSnapshotFolder As String
Dim stagingFolder As String
Dim completedFolder As String
Dim sourcePath As String
Dim snapshotPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim outputStem As String
Dim lastRegisterRow As Long
Dim dataRow As Long
Dim excelRow As Long
Dim approvedCount As Long
Dim generatedCount As Long
Dim previousScreenUpdating As Boolean
Dim previousEnableEvents As Boolean
Dim stateCaptured As Boolean
Dim currentStep As String
Dim errorNumber As Long
Dim errorDescription As String
Dim finalStatusPending As Boolean
Dim statusRecoveryMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
previousScreenUpdating = Application.ScreenUpdating
previousEnableEvents = Application.EnableEvents
stateCaptured = True
Application.ScreenUpdating = False
Application.EnableEvents = False
currentStep = "checking the macro-enabled workbook"
RequireWritableMacroWorkbook wb
Set packWs = RequireWorksheet(wb, PACK_SHEET)
Set registerWs = RequireWorksheet(wb, REGISTER_SHEET)
If packWs.ProtectContents Or registerWs.ProtectContents Then
Err.Raise vbObjectError + 5100, , _
"EmployeeCase and DocumentRegister must allow status updates."
End If
ValidatePackHeaders packWs
ValidateRegisterHeaders registerWs
RequireOnePackRow packWs
packData = packWs.Range("A2:I2").Value2
packId = ReadRequiredText(packData(1, 1), "PackID", 2)
employeeId = ReadRequiredText(packData(1, 2), "EmployeeID", 2)
employeeName = ReadRequiredText(packData(1, 3), "EmployeeName", 2)
jobTitle = ReadRequiredText(packData(1, 4), "JobTitle", 2)
startDateDisplay = ReadRequiredText( _
packData(1, 5), "StartDateDisplay", 2)
compensationDisplay = ReadOptionalText(packData(1, 6), "CompensationDisplay", 2)
departmentName = ReadRequiredText(packData(1, 7), "DepartmentName", 2)
hrSignatoryName = ReadRequiredText(packData(1, 8), "HRSignatoryName", 2)
packStatus = ReadRequiredText(packData(1, 9), "PackStatus", 2)
ValidatePackStatus packStatus
If packStatus <> STATUS_READY Then
Err.Raise vbObjectError + 5101, , _
"EmployeeCase row 2 must be exactly READY for generation."
End If
ValidateFileComponent packId, "PackID", 2
templateFolder = JoinPath(wb.Path, "Templates")
signatureFolder = JoinPath(JoinPath(wb.Path, "Assets"), "Signatures")
outputRoot = JoinPath(wb.Path, "GeneratedEmployeePacks")
RequireFolder templateFolder, "template folder"
lastRegisterRow = FindLastDataRow(registerWs, 6)
If lastRegisterRow < 2 Then
Err.Raise vbObjectError + 5102, , _
"DocumentRegister has no document rows."
End If
registerData = registerWs.Range("A2:F" & lastRegisterRow).Value2
Set documentKeys = NewTextDictionary(vbTextCompare)
Set templateFiles = NewTextDictionary(vbTextCompare)
Set outputSuffixes = NewTextDictionary(vbTextCompare)
currentStep = "preflighting the document register"
For dataRow = 1 To UBound(registerData, 1)
excelRow = dataRow + 1
If Not DataRowIsBlank(registerData, dataRow, 6) Then
documentStatus = ReadRequiredText( _
registerData(dataRow, 6), "DocumentStatus", excelRow)
ValidateDocumentStatus documentStatus, excelRow
If documentStatus = DOC_APPROVED Then
documentKey = ReadRequiredText( _
registerData(dataRow, 1), "DocumentKey", excelRow)
templateFile = ReadRequiredText( _
registerData(dataRow, 2), "TemplateFile", excelRow)
outputSuffix = ReadRequiredText( _
registerData(dataRow, 3), "OutputSuffix", excelRow)
bookmarkContract = ReadRequiredText( _
registerData(dataRow, 4), "RequiredBookmarks", excelRow)
signatureFile = ReadOptionalText( _
registerData(dataRow, 5), "SignatureFile", excelRow)
ValidateKey documentKey, "DocumentKey", excelRow
ValidateDocxFileName templateFile, excelRow
ValidateFileComponent outputSuffix, "OutputSuffix", excelRow
ValidateFileComponent packId & "_" & outputSuffix, _
"combined output name", excelRow
AddUnique documentKeys, documentKey, "DocumentKey", excelRow
AddUnique templateFiles, templateFile, "TemplateFile", excelRow
AddUnique outputSuffixes, outputSuffix, "OutputSuffix", excelRow
Set requiredBookmarks = ParseBookmarkContract( _
bookmarkContract, excelRow)
If requiredBookmarks.Exists("CompensationDisplay") And _
Len(compensationDisplay) = 0 Then
Err.Raise vbObjectError + 5159, , _
"CompensationDisplay is required by the template contract " & _
"on DocumentRegister row " & excelRow & "."
End If
ValidateSignatureContract requiredBookmarks, signatureFile, excelRow
sourcePath = JoinPath(templateFolder, templateFile)
RequireNonemptyFile sourcePath, _
"template on DocumentRegister row " & excelRow
If Len(signatureFile) > 0 Then
ValidateImageFileName signatureFile, excelRow
sourcePath = JoinPath(signatureFolder, signatureFile)
RequireNonemptyFile sourcePath, _
"signature on DocumentRegister row " & excelRow
End If
approvedCount = approvedCount + 1
End If
End If
Next dataRow
If approvedCount = 0 Then
Err.Raise vbObjectError + 5103, , _
"DocumentRegister has no rows marked exactly APPROVED."
End If
currentStep = "saving the preflighted workbook"
wb.Save
Set fso = CreateObject("Scripting.FileSystemObject")
EnsureFolder outputRoot
runFolder = CreateRunFolder(fso, outputRoot, packId)
inputFolder = JoinPath(runFolder, "InputSnapshot")
templateSnapshotFolder = JoinPath(inputFolder, "Templates")
signatureSnapshotFolder = JoinPath(inputFolder, "Signatures")
stagingFolder = JoinPath(runFolder, "Staging")
completedFolder = JoinPath(runFolder, "Completed")
fso.CreateFolder inputFolder
fso.CreateFolder templateSnapshotFolder
fso.CreateFolder signatureSnapshotFolder
fso.CreateFolder stagingFolder
fso.CreateFolder completedFolder
Set templateSnapshots = NewTextDictionary(vbTextCompare)
Set signatureSnapshots = NewTextDictionary(vbTextCompare)
currentStep = "creating the controlled input snapshot"
For dataRow = 1 To UBound(registerData, 1)
If CStr(registerData(dataRow, 6)) = DOC_APPROVED Then
templateFile = CStr(registerData(dataRow, 2))
sourcePath = JoinPath(templateFolder, templateFile)
snapshotPath = JoinPath(templateSnapshotFolder, templateFile)
CopyFileToSnapshot fso, sourcePath, snapshotPath, _
"template " & templateFile
templateSnapshots.Add templateFile, snapshotPath
signatureFile = CStr(registerData(dataRow, 5))
If Len(signatureFile) > 0 And _
Not signatureSnapshots.Exists(signatureFile) Then
sourcePath = JoinPath(signatureFolder, signatureFile)
snapshotPath = JoinPath(signatureSnapshotFolder, signatureFile)
CopyFileToSnapshot fso, sourcePath, snapshotPath, _
"signature " & signatureFile
signatureSnapshots.Add signatureFile, snapshotPath
End If
End If
Next dataRow
currentStep = "starting Microsoft Word Desktop"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = WD_ALERTS_NONE
wdApp.ScreenUpdating = False
For dataRow = 1 To UBound(registerData, 1)
excelRow = dataRow + 1
If CStr(registerData(dataRow, 6)) = DOC_APPROVED Then
currentStep = "rechecking the employee document pack and register row " & excelRow
RequireRangeUnchanged packWs.Range("A2:I2"), packData, 1, 9, _
"EmployeeCase row 2"
RequireRangeUnchanged registerWs.Range("A" & excelRow & _
":F" & excelRow), registerData, dataRow, 6, _
"DocumentRegister row " & excelRow
documentKey = CStr(registerData(dataRow, 1))
templateFile = CStr(registerData(dataRow, 2))
outputSuffix = CStr(registerData(dataRow, 3))
bookmarkContract = CStr(registerData(dataRow, 4))
signatureFile = CStr(registerData(dataRow, 5))
Set requiredBookmarks = ParseBookmarkContract( _
bookmarkContract, excelRow)
outputStem = packId & "_" & outputSuffix
ValidateFileComponent outputStem, "combined output name", excelRow
tempDocxPath = JoinPath(stagingFolder, outputStem & ".docx")
tempPdfPath = JoinPath(stagingFolder, outputStem & ".pdf")
finalDocxPath = JoinPath(completedFolder, outputStem & ".docx")
finalPdfPath = JoinPath(completedFolder, outputStem & ".pdf")
RequireTargetAbsent tempDocxPath
RequireTargetAbsent tempPdfPath
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
currentStep = "generating " & documentKey
Set doc = wdApp.Documents.Open( _
templateSnapshots(templateFile), False, True, False)
ValidateTemplate doc, requiredBookmarks, templateFile
PopulateRequiredBookmarks doc, requiredBookmarks, _
packId, employeeId, employeeName, jobTitle, _
startDateDisplay, compensationDisplay, departmentName, _
hrSignatoryName, signatureFile, signatureSnapshots
ValidateResultBeforeSave doc, requiredBookmarks, documentKey
doc.SaveAs2 tempDocxPath, WD_FORMAT_DOCX
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_PDF, False, 0, _
0, 1, 1, 0, True, True, 0, True, True, False
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
RequireNonemptyFile tempDocxPath, "staged DOCX " & documentKey
RequireNonemptyFile tempPdfPath, "staged PDF " & documentKey
Name tempDocxPath As finalDocxPath
Name tempPdfPath As finalPdfPath
generatedCount = generatedCount + 1
End If
Next dataRow
currentStep = "confirming the complete pack and document register"
If FindLastDataRow(packWs, 9) <> 2 Then
Err.Raise vbObjectError + 5160, , _
"EmployeeCase row count changed after preflight."
End If
If FindLastDataRow(registerWs, 6) <> lastRegisterRow Then
Err.Raise vbObjectError + 5161, , _
"DocumentRegister row count changed after preflight."
End If
RequireTableUnchanged packWs.Range("A2:I2"), packData, 1, 9, _
"EmployeeCase"
RequireTableUnchanged registerWs.Range("A2:F" & lastRegisterRow), _
registerData, UBound(registerData, 1), 6, "DocumentRegister"
If generatedCount <> approvedCount Then
Err.Raise vbObjectError + 5162, , _
"Generated document count does not match the approved count."
End If
currentStep = "saving the generated status to EmployeeCase"
finalStatusPending = True
packWs.Cells(2, 9).Value2 = STATUS_DONE
wb.Save
finalStatusPending = False
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
RestoreExcelState previousScreenUpdating, previousEnableEvents
stateCaptured = False
MsgBox "Employee document pack completed." & vbCrLf & _
"Documents generated: " & generatedCount & vbCrLf & _
"Completed folder: " & completedFolder & vbCrLf & vbCrLf & _
"Review employee identity, employment data, signatures, DOCX, and PDF before release.", _
vbInformation
CleanExit:
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If stateCaptured Then _
RestoreExcelState previousScreenUpdating, previousEnableEvents
On Error GoTo 0
Exit Sub
CleanFail:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If finalStatusPending And Not packWs Is Nothing Then
packWs.Cells(2, 9).Value2 = packStatus
statusRecoveryMessage = vbCrLf & _
"DONE was restored to " & packStatus & _
" in memory because the final workbook save was not confirmed."
End If
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If stateCaptured Then _
RestoreExcelState previousScreenUpdating, previousEnableEvents
On Error GoTo 0
MsgBox "Employee document pack generation stopped while " & currentStep & "." & _
vbCrLf & "Error " & errorNumber & ": " & errorDescription & _
vbCrLf & IIf(Len(runFolder) > 0, _
"Inspect the retained run folder: " & runFolder, _
"No run folder was created.") & vbCrLf & _
"Existing completed files were not deleted." & _
statusRecoveryMessage, vbCritical
End Sub
Private Sub PopulateRequiredBookmarks(ByVal doc As Object, _
ByVal required As Object, _
ByVal packId As String, _
ByVal employeeId As String, _
ByVal employeeName As String, _
ByVal jobTitle As String, _
ByVal startDate As String, _
ByVal compensationDisplay As String, _
ByVal departmentName As String, _
ByVal hrSignatoryName As String, _
ByVal signatureFile As String, _
ByVal signatures As Object)
Dim token As Variant
For Each token In required.Keys
Select Case CStr(token)
Case "PackID": WriteTextBookmark doc, CStr(token), packId
Case "EmployeeID": WriteTextBookmark doc, CStr(token), employeeId
Case "EmployeeName": WriteTextBookmark doc, CStr(token), employeeName
Case "JobTitle": WriteTextBookmark doc, CStr(token), jobTitle
Case "StartDateDisplay"
WriteTextBookmark doc, CStr(token), startDate
Case "CompensationDisplay": WriteTextBookmark doc, CStr(token), compensationDisplay
Case "DepartmentName": WriteTextBookmark doc, CStr(token), departmentName
Case "HRSignatoryName"
WriteTextBookmark doc, CStr(token), hrSignatoryName
Case "SignatureImage"
InsertSignatureBookmark doc, CStr(token), _
signatures(signatureFile)
End Select
Next token
End Sub
Private Sub ValidateTemplate(ByVal doc As Object, _
ByVal required As Object, _
ByVal templateFile As String)
RejectTrackedChanges doc, "template " & templateFile
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5104, , _
"Remove comments from template " & templateFile & "."
End If
ValidateBookmarkContract doc, required, templateFile
End Sub
Private Sub ValidateResultBeforeSave(ByVal doc As Object, _
ByVal required As Object, _
ByVal documentKey As String)
RejectTrackedChanges doc, "generated document " & documentKey
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5105, , _
"Generated document contains comments: " & documentKey
End If
ValidateBookmarkContract doc, required, documentKey
End Sub
Private Sub ValidateBookmarkContract(ByVal doc As Object, _
ByVal required As Object, _
ByVal documentLabel As String)
Dim token As Variant
Dim allowed As Object
Set allowed = AllowedBookmarkNames()
For Each token In required.Keys
If Not doc.Bookmarks.Exists(CStr(token)) Then
Err.Raise vbObjectError + 5106, , _
"Missing bookmark " & token & " in " & documentLabel & "."
End If
Next token
For Each token In allowed.Keys
If doc.Bookmarks.Exists(CStr(token)) And _
Not required.Exists(CStr(token)) Then
Err.Raise vbObjectError + 5107, , _
"Bookmark " & token & " exists but is not listed in " & _
"RequiredBookmarks for " & documentLabel & "."
End If
Next token
End Sub
Private Function ParseBookmarkContract(ByVal contractText As String, _
ByVal excelRow As Long) As Object
Dim result As Object
Dim allowed As Object
Dim tokens As Variant
Dim token As Variant
Dim tokenText As String
Set result = NewTextDictionary(vbBinaryCompare)
Set allowed = AllowedBookmarkNames()
tokens = Split(contractText, ";")
For Each token In tokens
tokenText = CStr(token)
If Len(tokenText) = 0 Or tokenText <> Trim$(tokenText) Then
Err.Raise vbObjectError + 5108, , _
"RequiredBookmarks has an empty or padded token on row " & _
excelRow & "."
End If
If Not allowed.Exists(tokenText) Then
Err.Raise vbObjectError + 5109, , _
"Unknown or incorrectly cased bookmark on row " & excelRow & _
": " & tokenText
End If
If result.Exists(tokenText) Then
Err.Raise vbObjectError + 5110, , _
"Duplicate bookmark token on row " & excelRow & ": " & tokenText
End If
result.Add tokenText, True
Next token
If Not result.Exists("PackID") Or Not result.Exists("EmployeeName") Then
Err.Raise vbObjectError + 5111, , _
"Every approved template must require PackID and EmployeeName."
End If
Set ParseBookmarkContract = result
End Function
Private Function AllowedBookmarkNames() As Object
Dim result As Object
Dim item As Variant
Set result = NewTextDictionary(vbBinaryCompare)
For Each item In Array("PackID", "EmployeeID", "EmployeeName", _
"JobTitle", "StartDateDisplay", "CompensationDisplay", _
"DepartmentName", "HRSignatoryName", "SignatureImage")
result.Add CStr(item), True
Next item
Set AllowedBookmarkNames = result
End Function
Private Sub ValidateSignatureContract(ByVal required As Object, _
ByVal signatureFile As String, _
ByVal excelRow As Long)
If required.Exists("SignatureImage") And Len(signatureFile) = 0 Then
Err.Raise vbObjectError + 5112, , _
"SignatureFile is required when SignatureImage is listed on row " & _
excelRow & "."
End If
If Not required.Exists("SignatureImage") And Len(signatureFile) > 0 Then
Err.Raise vbObjectError + 5113, , _
"SignatureFile must be blank when SignatureImage is not listed " & _
"on row " & excelRow & "."
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim styleValue As Variant
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 + 5114, , _
"Text bookmark contains a nested object: " & bookmarkName
End If
RejectRangeMarkers targetRange, bookmarkName
styleValue = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = styleValue
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 5115, , _
"Bookmark was not restored: " & bookmarkName
End If
If StrComp(doc.Bookmarks(bookmarkName).Range.Text, replacementText, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5116, , _
"Bookmark value differs after replacement: " & bookmarkName
End If
End Sub
Private Sub InsertSignatureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal imagePath As String)
Dim targetRange As Object
Dim picture As Object
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 + 5117, , _
"Signature bookmark contains a nested object."
End If
RejectRangeMarkers targetRange, bookmarkName
targetRange.Text = ""
targetRange.Collapse WD_COLLAPSE_START
Set picture = doc.InlineShapes.AddPicture( _
imagePath, False, True, targetRange)
If picture.Width <= 0 Or picture.Height <= 0 Then
Err.Raise vbObjectError + 5118, , _
"Word inserted an invalid signature image."
End If
picture.LockAspectRatio = OFFICE_TRUE
If picture.Width > 4.5 * POINTS_PER_CM Then _
picture.Width = 4.5 * POINTS_PER_CM
If picture.Height > 1.8 * POINTS_PER_CM Then _
picture.Height = 1.8 * POINTS_PER_CM
doc.Bookmarks.Add bookmarkName, picture.Range
If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 1 Then
Err.Raise vbObjectError + 5119, , _
"Signature bookmark does not contain one embedded image."
End If
End Sub
Private Sub RejectRangeMarkers(ByVal targetRange As Object, _
ByVal bookmarkName As String)
If InStr(1, targetRange.Text, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, targetRange.Text, Chr$(7), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5120, , _
"Bookmark includes a paragraph or cell marker: " & bookmarkName
End If
End Sub
Private Sub RejectTrackedChanges(ByVal doc As Object, _
ByVal documentLabel As String)
If doc.TrackRevisions Then
Err.Raise vbObjectError + 5121, , _
"Turn off Track Changes in " & documentLabel & "."
End If
If CountRevisionsInAllStories(doc) > 0 Then
Err.Raise vbObjectError + 5122, , _
"Resolve tracked revisions in every story of " & _
documentLabel & "."
End If
End Sub
Private Function CountRevisionsInAllStories(ByVal doc As Object) As Long
Dim root As Object
Dim story As Object
Dim total As Long
For Each root In doc.StoryRanges
Set story = root
Do While Not story Is Nothing
total = total + story.Revisions.Count
Set story = story.NextStoryRange
Loop
Next root
CountRevisionsInAllStories = total
End Function
Private Sub ValidatePackHeaders(ByVal ws As Worksheet)
ValidateHeaders ws, Array("PackID", "EmployeeID", "EmployeeName", _
"JobTitle", "StartDateDisplay", "CompensationDisplay", _
"DepartmentName", "HRSignatoryName", "PackStatus")
End Sub
Private Sub ValidateRegisterHeaders(ByVal ws As Worksheet)
ValidateHeaders ws, Array("DocumentKey", "TemplateFile", _
"OutputSuffix", "RequiredBookmarks", "SignatureFile", _
"DocumentStatus")
End Sub
Private Sub ValidateHeaders(ByVal ws As Worksheet, ByVal expected As Variant)
Dim columnIndex As Long
Dim valueText As Variant
For columnIndex = 1 To UBound(expected) + 1
valueText = ws.Cells(1, columnIndex).Value2
If IsError(valueText) Or VarType(valueText) <> vbString Then
Err.Raise vbObjectError + 5123, , _
"Header " & ws.Cells(1, columnIndex).Address(False, False) & _
" on " & ws.Name & " must contain exact text."
End If
If StrComp(CStr(valueText), CStr(expected(columnIndex - 1)), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5124, , _
"Expected header " & expected(columnIndex - 1) & _
" in column " & columnIndex & " on " & ws.Name & "."
End If
Next columnIndex
End Sub
Private Sub RequireOnePackRow(ByVal ws As Worksheet)
Dim lastRow As Long
lastRow = FindLastDataRow(ws, 9)
If lastRow < 2 Then
Err.Raise vbObjectError + 5125, , _
"EmployeeCase row 2 is missing."
End If
If lastRow > 2 Then
Err.Raise vbObjectError + 5126, , _
"Contractor logic accepts one reviewed EmployeeCase row per run."
End If
End Sub
Private Function FindLastDataRow(ByVal ws As Worksheet, _
ByVal lastColumn As Long) As Long
Dim lastCell As Range
Set lastCell = ws.Range(ws.Cells(1, 1), _
ws.Cells(ws.Rows.Count, lastColumn)).Find( _
What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If lastCell Is Nothing Then
FindLastDataRow = 0
Else
FindLastDataRow = lastCell.Row
End If
End Function
Private Function DataRowIsBlank(ByRef data As Variant, _
ByVal dataRow As Long, _
ByVal columnCount As Long) As Boolean
Dim columnIndex As Long
DataRowIsBlank = True
For columnIndex = 1 To columnCount
If IsError(data(dataRow, columnIndex)) Then
DataRowIsBlank = False
Exit Function
End If
If Len(CStr(data(dataRow, columnIndex))) > 0 Then
DataRowIsBlank = False
Exit Function
End If
Next columnIndex
End Function
Private Function ReadRequiredText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadRequiredText = ReadOptionalText(rawValue, fieldName, excelRow)
If Len(ReadRequiredText) = 0 Then
Err.Raise vbObjectError + 5127, , _
fieldName & " is required on row " & excelRow & "."
End If
End Function
Private Function ReadOptionalText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
If IsEmpty(rawValue) Then
ReadOptionalText = ""
Exit Function
End If
If IsError(rawValue) Or VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 5128, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
ReadOptionalText = CStr(rawValue)
If ReadOptionalText <> Trim$(ReadOptionalText) Then
Err.Raise vbObjectError + 5129, , _
fieldName & " has outer whitespace on row " & excelRow & "."
End If
If ContainsControlCharacter(ReadOptionalText) Then
Err.Raise vbObjectError + 5130, , _
fieldName & " contains a control character on row " & excelRow & "."
End If
End Function
Private Sub ValidatePackStatus(ByVal statusText As String)
If statusText <> STATUS_READY And statusText <> STATUS_HOLD And _
statusText <> STATUS_DONE Then
Err.Raise vbObjectError + 5131, , _
"PackStatus must be exactly READY, HOLD, or DONE."
End If
End Sub
Private Sub ValidateDocumentStatus(ByVal statusText As String, _
ByVal excelRow As Long)
If statusText <> DOC_APPROVED And statusText <> DOC_RETIRED Then
Err.Raise vbObjectError + 5132, , _
"DocumentStatus must be exactly APPROVED or RETIRED on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateKey(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
If Len(valueText) > 80 Then
Err.Raise vbObjectError + 5133, , _
fieldName & " exceeds 80 characters on row " & excelRow & "."
End If
End Sub
Private Sub ValidateFileComponent(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
Dim item As Variant
Dim baseName As String
Dim lowerName As String
If Len(valueText) > 100 Then
Err.Raise vbObjectError + 5134, , _
fieldName & " exceeds 100 characters on row " & excelRow & "."
End If
For Each item In Array("<", ">", ":", Chr$(34), "/", "\", _
"|", "?", "*")
If InStr(1, valueText, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5135, , _
fieldName & " contains a forbidden Windows character on row " & _
excelRow & "."
End If
Next item
If Right$(valueText, 1) = "." Or Right$(valueText, 1) = " " Then
Err.Raise vbObjectError + 5136, , _
fieldName & " ends with a period or space on row " & excelRow & "."
End If
lowerName = LCase$(valueText)
If Right$(lowerName, 5) = ".docx" Or _
Right$(lowerName, 4) = ".pdf" Then
Err.Raise vbObjectError + 5137, , _
fieldName & " must not include .docx or .pdf on row " & excelRow & "."
End If
baseName = valueText
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(baseName) Then
Err.Raise vbObjectError + 5138, , _
fieldName & " uses a reserved Windows name on row " & excelRow & "."
End If
End Sub
Private Sub ValidateDocxFileName(ByVal fileName As String, _
ByVal excelRow As Long)
ValidateAssetFileName fileName, excelRow
If LCase$(Right$(fileName, 5)) <> ".docx" Then
Err.Raise vbObjectError + 5139, , _
"TemplateFile must end with .docx on row " & excelRow & "."
End If
End Sub
Private Sub ValidateImageFileName(ByVal fileName As String, _
ByVal excelRow As Long)
Dim lowerName As String
ValidateAssetFileName fileName, excelRow
lowerName = LCase$(fileName)
If Right$(lowerName, 4) <> ".png" And _
Right$(lowerName, 4) <> ".jpg" And _
Right$(lowerName, 5) <> ".jpeg" Then
Err.Raise vbObjectError + 5140, , _
"SignatureFile must be PNG, JPG, or JPEG on row " & excelRow & "."
End If
End Sub
Private Sub ValidateAssetFileName(ByVal fileName As String, _
ByVal excelRow As Long)
Dim item As Variant
For Each item In Array("<", ">", ":", Chr$(34), "/", "\", _
"|", "?", "*")
If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5141, , _
"Asset filename contains a path or wildcard on row " & _
excelRow & "."
End If
Next item
If Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
Err.Raise vbObjectError + 5142, , _
"Asset filename ends with a period or space on row " & excelRow & "."
End If
End Sub
Private Sub AddUnique(ByVal values As Object, ByVal valueText As String, _
ByVal fieldName As String, ByVal excelRow As Long)
If values.Exists(valueText) Then
Err.Raise vbObjectError + 5143, , _
"Duplicate " & fieldName & " on rows " & values(valueText) & _
" and " & excelRow & ": " & valueText
End If
values.Add valueText, excelRow
End Sub
Private Function NewTextDictionary(ByVal compareMode As Long) As Object
Dim result As Object
Set result = CreateObject("Scripting.Dictionary")
result.CompareMode = compareMode
Set NewTextDictionary = result
End Function
Private Sub RequireRangeUnchanged(ByVal liveRange As Range, _
ByRef snapshot As Variant, _
ByVal snapshotRow As Long, _
ByVal columnCount As Long, _
ByVal rangeLabel As String)
Dim liveData As Variant
Dim columnIndex As Long
liveData = liveRange.Value2
For columnIndex = 1 To columnCount
If Not ValuesExactlyEqual(snapshot(snapshotRow, columnIndex), _
liveData(1, columnIndex)) Then
Err.Raise vbObjectError + 5144, , _
rangeLabel & " changed after preflight. Start a new run."
End If
Next columnIndex
End Sub
Private Sub RequireTableUnchanged(ByVal liveRange As Range, _
ByRef snapshot As Variant, _
ByVal expectedRows As Long, _
ByVal expectedColumns As Long, _
ByVal tableLabel As String)
Dim liveData As Variant
Dim rowIndex As Long
Dim columnIndex As Long
If liveRange.Rows.Count <> expectedRows Or _
liveRange.Columns.Count <> expectedColumns Then
Err.Raise vbObjectError + 5163, , _
tableLabel & " dimensions changed after preflight."
End If
liveData = liveRange.Value2
For rowIndex = 1 To expectedRows
For columnIndex = 1 To expectedColumns
If Not ValuesExactlyEqual(snapshot(rowIndex, columnIndex), _
liveData(rowIndex, columnIndex)) Then
Err.Raise vbObjectError + 5164, , _
tableLabel & " changed after preflight at row " & _
(rowIndex + 1) & ", column " & columnIndex & "."
End If
Next columnIndex
Next rowIndex
End Sub
Private Function ValuesExactlyEqual(ByVal firstValue As Variant, _
ByVal secondValue As Variant) As Boolean
If IsError(firstValue) Or IsError(secondValue) Then Exit Function
If VarType(firstValue) <> VarType(secondValue) Then Exit Function
ValuesExactlyEqual = (StrComp(CStr(firstValue), CStr(secondValue), _
vbBinaryCompare) = 0)
End Function
Private Sub CopyFileToSnapshot(ByVal fso As Object, _
ByVal sourcePath As String, _
ByVal snapshotPath As String, _
ByVal fileLabel As String)
Dim sourceSize As Variant
Dim sourceModified As Date
Dim failureText As String
RequireNonemptyFile sourcePath, fileLabel
RequireTargetAbsent snapshotPath
On Error GoTo CopyFailed
sourceSize = fso.GetFile(sourcePath).Size
sourceModified = fso.GetFile(sourcePath).DateLastModified
fso.CopyFile sourcePath, snapshotPath, False
RequireNonemptyFile snapshotPath, fileLabel & " snapshot"
If fso.GetFile(snapshotPath).Size <> sourceSize Then
Err.Raise vbObjectError + 5145, , _
"Snapshot size differs for " & fileLabel & "."
End If
If fso.GetFile(sourcePath).Size <> sourceSize Or _
fso.GetFile(sourcePath).DateLastModified <> sourceModified Then
Err.Raise vbObjectError + 5146, , _
"Source changed while snapshotting " & fileLabel & "."
End If
Exit Sub
CopyFailed:
failureText = Err.Description
Err.Raise vbObjectError + 5147, , _
"Could not snapshot " & fileLabel & ": " & failureText
End Sub
Private Function CreateRunFolder(ByVal fso As Object, _
ByVal outputRoot As String, _
ByVal packId As String) As String
Dim baseName As String
Dim candidate As String
Dim suffix As Long
baseName = "Run_" & packId & "_" & Format$(Now, "yyyymmdd_hhnnss")
candidate = JoinPath(outputRoot, baseName)
Do While fso.FolderExists(candidate)
suffix = suffix + 1
candidate = JoinPath(outputRoot, baseName & "_" & _
Format$(suffix, "000"))
Loop
fso.CreateFolder candidate
CreateRunFolder = candidate
End Function
Private Sub RequireWritableMacroWorkbook(ByVal wb As Workbook)
If Len(wb.Path) = 0 Or LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 5148, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 5149, , _
"The workbook is read-only; DONE cannot be saved."
End If
End Sub
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error GoTo MissingSheet
Set RequireWorksheet = wb.Worksheets(sheetName)
Exit Function
MissingSheet:
Err.Raise vbObjectError + 5150, , _
"Missing worksheet: " & sheetName
End Function
Private Sub EnsureFolder(ByVal folderPath As String)
If FolderExistsStrict(folderPath) Then Exit Sub
On Error GoTo CreateFailed
MkDir folderPath
Exit Sub
CreateFailed:
Err.Raise vbObjectError + 5151, , _
"Could not create folder: " & folderPath & ". " & Err.Description
End Sub
Private Sub RequireFolder(ByVal folderPath As String, _
ByVal folderLabel As String)
If Not FolderExistsStrict(folderPath) Then
Err.Raise vbObjectError + 5152, , _
"Missing " & folderLabel & ": " & folderPath
End If
End Sub
Private Sub RequireTargetAbsent(ByVal filePath As String)
If FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 5153, , _
"Refusing to overwrite existing file: " & filePath
End If
End Sub
Private Sub RequireNonemptyFile(ByVal filePath As String, _
ByVal fileLabel As String)
If Not FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 5154, , _
"Missing " & fileLabel & ": " & filePath
End If
On Error GoTo InspectFailed
If FileLen(filePath) <= 0 Then
Err.Raise vbObjectError + 5155, , _
"Empty " & fileLabel & ": " & filePath
End If
Exit Sub
InspectFailed:
Err.Raise vbObjectError + 5156, , _
"Could not inspect " & fileLabel & ": " & filePath & _
". " & Err.Description
End Sub
Private Function FileExistsStrict(ByVal filePath As String) As Boolean
On Error GoTo CheckFailed
FileExistsStrict = (Len(Dir$(filePath, _
vbNormal Or vbHidden Or vbSystem Or vbReadOnly)) > 0)
Exit Function
CheckFailed:
Err.Raise vbObjectError + 5157, , _
"Could not check file path: " & filePath & ". " & Err.Description
End Function
Private Function FolderExistsStrict(ByVal folderPath As String) As Boolean
Dim resultText As String
On Error GoTo CheckFailed
resultText = Dir$(folderPath, vbDirectory)
If Len(resultText) > 0 Then
FolderExistsStrict = _
((GetAttr(folderPath) And vbDirectory) = vbDirectory)
End If
Exit Function
CheckFailed:
Err.Raise vbObjectError + 5158, , _
"Could not check folder: " & folderPath & ". " & Err.Description
End Function
Private Function JoinPath(ByVal parentPath As String, _
ByVal childName As String) As String
If Right$(parentPath, 1) = "\" Then
JoinPath = parentPath & childName
Else
JoinPath = parentPath & "\" & childName
End If
End Function
Private Function ContainsControlCharacter(ByVal valueText As String) As Boolean
Dim index As Long
Dim code As Long
For index = 1 To Len(valueText)
code = AscW(Mid$(valueText, index, 1))
If code >= 0 And code <= 31 Then
ContainsControlCharacter = True
Exit Function
End If
Next index
End Function
Private Function IsReservedWindowsName(ByVal baseName As String) As Boolean
Dim upperName As String
Dim suffixText As String
Dim suffixCode As Long
upperName = UCase$(baseName)
Select Case upperName
Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
IsReservedWindowsName = True
Exit Function
End Select
If Len(upperName) >= 4 Then
If Left$(upperName, 3) = "COM" Or Left$(upperName, 3) = "LPT" Then
suffixText = Mid$(upperName, 4)
If Len(suffixText) = 1 Then
suffixCode = AscW(suffixText)
If (suffixCode >= AscW("1") And suffixCode <= AscW("9")) Or _
suffixText = ChrW$(&HB9) Or suffixText = ChrW$(&HB2) Or _
suffixText = ChrW$(&HB3) Then
IsReservedWindowsName = True
End If
End If
End If
End If
End Function
Private Sub RestoreExcelState(ByVal screenUpdatingValue As Boolean, _
ByVal enableEventsValue As Boolean)
Application.EnableEvents = enableEventsValue
Application.ScreenUpdating = screenUpdatingValue
End SubThe example uses Word’s documented bookmark checks, embedded image insertion, DOCX saving, and PDF export.
Review the generated pack before HR release
Employee identity
Match PackID, EmployeeID, name, job title, start date, department, and every output filename.
Employment terms
Verify compensation and all contractual wording against the approved source and current template release.
Document completeness
Confirm the completed folder contains exactly the approved register documents and both file formats.
Signature use
Confirm the named signatory, image authorization, placement, and any separate digital-signature requirement.
Visual output
Inspect branding, wrapping, tables, checkboxes, page breaks, headers, footers, DOCX, and every PDF page.
Release record
Record the approver, recipient, delivery channel, time, correction history, and retention decision outside PackStatus.
Know what the free example does not guarantee
No HR decision
The macro cannot approve a hire, employment terms, compensation, policy content, or release.
No digital signature
A signature image does not authenticate the signer or protect document integrity.
No atomic pack commit
Each file pair moves separately and the final workbook save is a separate operation.
No automatic distribution
Email, portals, e-signatures, identity checks, and delivery receipts remain separate workflows.
No roster batch
One EmployeeCase row is accepted per run. Many employees using one template belong in Factory.
No records program
Access reviews, retention, legal holds, corrections, and deletion remain organizational responsibilities.
Use Contractor for one multi-document employee case
DocxForge Pro Contractor reuses one reviewed case across several different documents created from different Word templates. That is the employee document pack described here.
Use it for an offer, agreement, onboarding checklist, policy acknowledgment, and other distinct outputs for one employee. Factory remains the separate many-row, one-template workflow.




Frequently asked questions
Is this a Contractor workflow?
Yes. One reviewed EmployeeCase supplies shared data to several different approved Word templates. Each template produces its own DOCX/PDF pair.
What can an employee document pack contain?
It can contain an offer letter, employment agreement, onboarding checklist, policy acknowledgment, role summary, or another approved document defined by its own template and bookmark contract.
Can CompensationDisplay be blank?
Yes, unless an approved template lists CompensationDisplay in RequiredBookmarks. A document that requires compensation cannot be generated from a blank value.
Does an embedded HR signature prove authorization?
No. A signature image is not a digital signature and does not prove identity, consent, authority, or document integrity. Approve its use separately before releasing HR documents.
Does DONE mean HR approved the documents?
No. DONE records successful generation and workbook saving. HR must still review every document, confirm authorization, apply any required signing process, and approve release.
When should Factory be used instead?
Use Factory when many independent employee rows each use one standard template, such as one policy acknowledgment per employee. Use Contractor for several different documents about one employee.