HR Document Automation with Excel, Word, Images, and PDF
Turn approved employee data into controlled Word and PDF outputs without treating every HR document as the same workflow. Use Contractor for one employee and several different templates. Use Factory for many employee rows and one standard template.
Quick answer: choose the module by the document result
One employee → several different documents
Use a Contractor-shaped workflow for an offer letter, policy acknowledgment, onboarding checklist, or other case pack created from different Word templates.
Many employees → one standard document each
Use a Factory-shaped workflow for a roster-wide run of one document type, such as one standard letter or certificate per approved employee row.
A 200-row roster and a three-template employee pack are different jobs. Approve, test, name, and archive them separately.
Map the HR result before writing code
Offer-letter batch
Many approved candidates, one offer-letter template, and one output per row: Factory logic.
Employee onboarding pack
One employee, several different documents, and a different template for each document: Contractor logic.
Photo directory or ID summary
Many employee rows, one visual template, and one output per row: Factory logic.
Case-specific HR packet
One reviewed employee case with selected agreements, acknowledgments, and checklists: Contractor logic.
Create an employee-data contract before generation
Stable employee identifier
EmployeeID must be unique. Format the column as Text before import when leading zeros carry meaning.
Approved display values
StartDateDisplay contains final document-ready text instead of relying on Excel’s numeric date formatting.
Required employee fields
Name, job title, department, manager, photo file, and signature file must be complete for the selected pack.
Explicit approval gate
The example requires RecordStatus = APPROVED. A nonblank row is not treated as authorization to generate HR documents.
Controlled image names
Excel stores file names only. Folder separators, drive letters, wildcards, and parent-folder references are rejected.
No hidden derivation
Do not infer legal names, dates, compensation, or approvals from unrelated columns during document generation.
EmployeeID | EmployeeName | JobTitle | Department | StartDateDisplay | ManagerName | PhotoFile | SignatureFile | RecordStatus
Define the three-template Contractor pack
OfferLetter.docx
Contains the six common text bookmarks plus EmployeePhoto and HRSignature inline-image bookmarks.
PolicyAcknowledgment.docx
Contains the six common text bookmarks and its own policy content. The example does not insert images into this template.
OnboardingChecklist.docx
Contains the six common text bookmarks and a checklist layout approved for the selected employee case.
Common text bookmarks
EmployeeID, EmployeeName, JobTitle, Department, StartDateDisplay, and ManagerName.
Uniform bookmark ranges
Keep text bookmarks around styled placeholder text without paragraph marks, nested fields, content controls, or inline objects.
Test every template release
Approve maximum text, both images, page breaks, headers, footers, tables, DOCX reopening, and PDF output.
Official references: Bookmarks.Exists and Bookmarks.Add.
Free VBA example: one approved employee → three documents
Save the workbook as .xlsm. Place the three templates in Templates\, employee photos in EmployeePhotos\, and approved HR signature files in HRSignatures\. The macro prompts for one exact EmployeeID, validates all inputs and templates, then creates three DOCX files and three PDFs in a unique run folder.
One unique employee
The requested EmployeeID must match exactly one row and that row must be explicitly APPROVED.
All templates validated first
Every common bookmark and both offer-letter image destinations are checked before the output folder is created.
Images are embedded
Photo and signature are inserted with linking disabled, aspect ratio preserved, and maximum dimensions taken from the placeholders.
Partial results remain visible
If the run stops, completed final files stay in the unique pack folder. Only the macro’s current temporary files are eligible for cleanup.
Option Explicit
Private Const EMPLOYEE_SHEET As String = "Employees"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const PHOTO_FOLDER As String = "\EmployeePhotos"
Private Const SIGNATURE_FOLDER As String = "\HRSignatures"
Private Const OUTPUT_ROOT As String = "\GeneratedHRPacks"
Private Const OFFER_TEMPLATE As String = "OfferLetter.docx"
Private Const POLICY_TEMPLATE As String = "PolicyAcknowledgment.docx"
Private Const CHECKLIST_TEMPLATE As String = "OnboardingChecklist.docx"
Private Const HEADER_EMPLOYEE_ID As String = "EmployeeID"
Private Const HEADER_EMPLOYEE_NAME As String = "EmployeeName"
Private Const HEADER_JOB_TITLE As String = "JobTitle"
Private Const HEADER_DEPARTMENT As String = "Department"
Private Const HEADER_START_DATE As String = "StartDateDisplay"
Private Const HEADER_MANAGER_NAME As String = "ManagerName"
Private Const HEADER_PHOTO_FILE As String = "PhotoFile"
Private Const HEADER_SIGNATURE_FILE As String = "SignatureFile"
Private Const HEADER_RECORD_STATUS As String = "RecordStatus"
Private Const APPROVED_STATUS As String = "APPROVED"
Private Const BOOKMARK_EMPLOYEE_ID As String = "EmployeeID"
Private Const BOOKMARK_EMPLOYEE_NAME As String = "EmployeeName"
Private Const BOOKMARK_JOB_TITLE As String = "JobTitle"
Private Const BOOKMARK_DEPARTMENT As String = "Department"
Private Const BOOKMARK_START_DATE As String = "StartDateDisplay"
Private Const BOOKMARK_MANAGER_NAME As String = "ManagerName"
Private Const BOOKMARK_EMPLOYEE_PHOTO As String = "EmployeePhoto"
Private Const BOOKMARK_HR_SIGNATURE As String = "HRSignature"
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_COLLAPSE_START As Long = 1
Private Const WD_UNDEFINED As Long = 9999999
Private Const MSO_TRUE As Long = -1
Private Const WORD_ALERTS_NONE As Long = 0
Public Sub GenerateEmployeeDocumentPack()
Dim wb As Workbook
Dim ws As Worksheet
Dim employeeIdColumn As Long
Dim employeeNameColumn As Long
Dim jobTitleColumn As Long
Dim departmentColumn As Long
Dim startDateColumn As Long
Dim managerNameColumn As Long
Dim photoFileColumn As Long
Dim signatureFileColumn As Long
Dim recordStatusColumn As Long
Dim requestedEmployeeId As String
Dim employeeId As String
Dim employeeName As String
Dim jobTitle As String
Dim department As String
Dim startDateDisplay As String
Dim managerName As String
Dim photoFile As String
Dim signatureFile As String
Dim recordStatus As String
Dim photoPath As String
Dim signaturePath As String
Dim safeEmployeeId As String
Dim lastRow As Long
Dim rowNumber As Long
Dim employeeRow As Long
Dim matchCount As Long
Dim templateIndex As Long
Dim templatePaths(1 To 3) As String
Dim templateBaseNames(1 To 3) As String
Dim templateNeedsImages(1 To 3) As Boolean
Dim templatePath As String
Dim outputRoot As String
Dim outputFolder As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim runToken As String
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
Dim errorNumber As Long
Dim errorMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 2700, , _
"Save the workbook before generating an employee pack."
End If
If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 2701, , _
"Save the workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
Set ws = RequireWorksheet(wb, EMPLOYEE_SHEET)
employeeIdColumn = RequireHeaderColumn(ws, HEADER_EMPLOYEE_ID)
employeeNameColumn = RequireHeaderColumn(ws, HEADER_EMPLOYEE_NAME)
jobTitleColumn = RequireHeaderColumn(ws, HEADER_JOB_TITLE)
departmentColumn = RequireHeaderColumn(ws, HEADER_DEPARTMENT)
startDateColumn = RequireHeaderColumn(ws, HEADER_START_DATE)
managerNameColumn = RequireHeaderColumn(ws, HEADER_MANAGER_NAME)
photoFileColumn = RequireHeaderColumn(ws, HEADER_PHOTO_FILE)
signatureFileColumn = RequireHeaderColumn(ws, HEADER_SIGNATURE_FILE)
recordStatusColumn = RequireHeaderColumn(ws, HEADER_RECORD_STATUS)
requestedEmployeeId = Trim$(InputBox$( _
"Enter the exact EmployeeID for the approved HR pack.", _
"Generate employee document pack"))
If Len(requestedEmployeeId) = 0 Then Exit Sub
ValidateReplacementText requestedEmployeeId, HEADER_EMPLOYEE_ID
lastRow = ws.Cells(ws.Rows.Count, employeeIdColumn).End(xlUp).Row
For rowNumber = 2 To lastRow
employeeId = ReadCellText(ws, rowNumber, employeeIdColumn, HEADER_EMPLOYEE_ID)
If StrComp(employeeId, requestedEmployeeId, vbTextCompare) = 0 Then
matchCount = matchCount + 1
employeeRow = rowNumber
End If
Next rowNumber
If matchCount = 0 Then
Err.Raise vbObjectError + 2702, , _
"EmployeeID was not found: " & requestedEmployeeId
ElseIf matchCount > 1 Then
Err.Raise vbObjectError + 2703, , _
"EmployeeID is duplicated in the Employees sheet: " & requestedEmployeeId
End If
employeeId = RequireCellText(ws, employeeRow, employeeIdColumn, HEADER_EMPLOYEE_ID)
employeeName = RequireCellText(ws, employeeRow, employeeNameColumn, HEADER_EMPLOYEE_NAME)
jobTitle = RequireCellText(ws, employeeRow, jobTitleColumn, HEADER_JOB_TITLE)
department = RequireCellText(ws, employeeRow, departmentColumn, HEADER_DEPARTMENT)
startDateDisplay = RequireDisplayText(ws, employeeRow, startDateColumn, HEADER_START_DATE)
managerName = RequireCellText(ws, employeeRow, managerNameColumn, HEADER_MANAGER_NAME)
photoFile = RequireCellText(ws, employeeRow, photoFileColumn, HEADER_PHOTO_FILE)
signatureFile = RequireCellText(ws, employeeRow, signatureFileColumn, HEADER_SIGNATURE_FILE)
recordStatus = UCase$(RequireCellText(ws, employeeRow, recordStatusColumn, HEADER_RECORD_STATUS))
If recordStatus <> APPROVED_STATUS Then
RaiseRowError employeeRow, HEADER_RECORD_STATUS, _
"The selected record must have RecordStatus = " & APPROVED_STATUS & "."
End If
ValidateReplacementText employeeId, BOOKMARK_EMPLOYEE_ID
ValidateReplacementText employeeName, BOOKMARK_EMPLOYEE_NAME
ValidateReplacementText jobTitle, BOOKMARK_JOB_TITLE
ValidateReplacementText department, BOOKMARK_DEPARTMENT
ValidateReplacementText startDateDisplay, BOOKMARK_START_DATE
ValidateReplacementText managerName, BOOKMARK_MANAGER_NAME
ValidateImageFileName photoFile, employeeRow, HEADER_PHOTO_FILE
ValidateImageFileName signatureFile, employeeRow, HEADER_SIGNATURE_FILE
safeEmployeeId = CleanFileName(employeeId)
If Len(safeEmployeeId) = 0 Then
RaiseRowError employeeRow, HEADER_EMPLOYEE_ID, _
"The identifier does not contain a usable Windows file name."
End If
templatePaths(1) = wb.Path & TEMPLATE_FOLDER & "\" & OFFER_TEMPLATE
templatePaths(2) = wb.Path & TEMPLATE_FOLDER & "\" & POLICY_TEMPLATE
templatePaths(3) = wb.Path & TEMPLATE_FOLDER & "\" & CHECKLIST_TEMPLATE
templateBaseNames(1) = "Offer_Letter"
templateBaseNames(2) = "Policy_Acknowledgment"
templateBaseNames(3) = "Onboarding_Checklist"
templateNeedsImages(1) = True
templateNeedsImages(2) = False
templateNeedsImages(3) = False
For templateIndex = 1 To 3
RequireFile templatePaths(templateIndex), _
"Word template " & templateBaseNames(templateIndex)
Next templateIndex
photoPath = wb.Path & PHOTO_FOLDER & "\" & photoFile
signaturePath = wb.Path & SIGNATURE_FOLDER & "\" & signatureFile
RequireFolder wb.Path & PHOTO_FOLDER, "employee photo folder"
RequireFolder wb.Path & SIGNATURE_FOLDER, "HR signature folder"
RequireFile photoPath, "employee photo"
RequireFile signaturePath, "HR signature"
Set fso = CreateObject("Scripting.FileSystemObject")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = WORD_ALERTS_NONE
For templateIndex = 1 To 3
Set doc = wdApp.Documents.Open(templatePaths(templateIndex), False, True, False)
ValidateCommonTemplate doc
If templateNeedsImages(templateIndex) Then ValidateOfferImages doc
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
Next templateIndex
outputRoot = wb.Path & OUTPUT_ROOT
If Not FolderExists(outputRoot) Then fso.CreateFolder outputRoot
runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 1000), "00000000")
outputFolder = outputRoot & "\" & safeEmployeeId & "_" & runToken
If FolderExists(outputFolder) Then
Err.Raise vbObjectError + 2704, , _
"The unique output folder already exists: " & outputFolder
End If
If Len(outputFolder & "\" & safeEmployeeId & _
"_Policy_Acknowledgment.docx") > 218 Or _
Len(outputFolder & "\" & safeEmployeeId & _
"_Policy_Acknowledgment.pdf") > 218 Then
Err.Raise vbObjectError + 2708, , _
"The generated HR pack path is too long for this example workflow."
End If
fso.CreateFolder outputFolder
For templateIndex = 1 To 3
templatePath = templatePaths(templateIndex)
finalDocxPath = outputFolder & "\" & safeEmployeeId & "_" & _
templateBaseNames(templateIndex) & ".docx"
finalPdfPath = outputFolder & "\" & safeEmployeeId & "_" & _
templateBaseNames(templateIndex) & ".pdf"
tempDocxPath = outputFolder & "\.__hr_" & runToken & _
"_" & CStr(templateIndex) & ".docx"
tempPdfPath = outputFolder & "\.__hr_" & runToken & _
"_" & CStr(templateIndex) & ".pdf"
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
WriteTextBookmark doc, BOOKMARK_EMPLOYEE_ID, employeeId
WriteTextBookmark doc, BOOKMARK_EMPLOYEE_NAME, employeeName
WriteTextBookmark doc, BOOKMARK_JOB_TITLE, jobTitle
WriteTextBookmark doc, BOOKMARK_DEPARTMENT, department
WriteTextBookmark doc, BOOKMARK_START_DATE, startDateDisplay
WriteTextBookmark doc, BOOKMARK_MANAGER_NAME, managerName
If templateNeedsImages(templateIndex) Then
WritePictureBookmark doc, BOOKMARK_EMPLOYEE_PHOTO, photoPath
WritePictureBookmark doc, BOOKMARK_HR_SIGNATURE, signaturePath
End If
doc.SaveAs2 tempDocxPath, WD_FORMAT_XML_DOCUMENT, False, vbNullString, False
If Not FileExists(tempDocxPath) Then
Err.Raise vbObjectError + 2705, , _
"Word did not create the temporary DOCX for " & _
templateBaseNames(templateIndex) & "."
End If
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_FORMAT_PDF
If Not FileExists(tempPdfPath) Then
Err.Raise vbObjectError + 2706, , _
"Word did not create the temporary PDF for " & _
templateBaseNames(templateIndex) & "."
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If FileExists(finalDocxPath) Or FileExists(finalPdfPath) Then
Err.Raise vbObjectError + 2707, , _
"A final output appeared unexpectedly. No file was replaced."
End If
fso.MoveFile tempDocxPath, finalDocxPath
tempDocxPath = vbNullString
fso.MoveFile tempPdfPath, finalPdfPath
tempPdfPath = vbNullString
Next templateIndex
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
MsgBox "Created three DOCX files and three PDFs for " & employeeId & _
"." & vbCrLf & outputFolder, vbInformation, "HR pack complete"
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.Quit WD_DO_NOT_SAVE_CHANGES
SafeDeleteTempFile tempDocxPath
SafeDeleteTempFile tempPdfPath
On Error GoTo 0
If Len(outputFolder) > 0 Then
errorMessage = errorMessage & vbCrLf & _
"Review partial final files in: " & outputFolder
End If
MsgBox "Employee pack stopped." & vbCrLf & _
"Error " & errorNumber & ": " & errorMessage, _
vbCritical, "HR pack not completed"
End Sub
Private Sub ValidateCommonTemplate(ByVal doc As Object)
ValidateTextBookmark doc, BOOKMARK_EMPLOYEE_ID
ValidateTextBookmark doc, BOOKMARK_EMPLOYEE_NAME
ValidateTextBookmark doc, BOOKMARK_JOB_TITLE
ValidateTextBookmark doc, BOOKMARK_DEPARTMENT
ValidateTextBookmark doc, BOOKMARK_START_DATE
ValidateTextBookmark doc, BOOKMARK_MANAGER_NAME
End Sub
Private Sub ValidateOfferImages(ByVal doc As Object)
ValidatePictureBookmark doc, BOOKMARK_EMPLOYEE_PHOTO
ValidatePictureBookmark doc, BOOKMARK_HR_SIGNATURE
End Sub
Private Sub ValidateTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2710, , _
"Required text bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.Start = targetRange.End Then
Err.Raise vbObjectError + 2711, , _
"Text bookmark must contain styled placeholder text: " & bookmarkName
End If
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2712, , _
"Text bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2713, , _
"Text bookmark contains a Word field or content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 2714, , _
"Text bookmark contains an inline shape: " & bookmarkName
End If
If HasMixedCharacterFormatting(targetRange) Then
Err.Raise vbObjectError + 2715, , _
"Text bookmark uses mixed character formatting: " & bookmarkName
End If
End Sub
Private Sub ValidatePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2716, , _
"Required picture bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2717, , _
"Picture bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2718, , _
"Picture bookmark contains a Word field or content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 1 Then
Err.Raise vbObjectError + 2719, , _
"Picture bookmark may contain one inline placeholder at most: " & bookmarkName
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim savedFont As Object
Dim savedStyle As Variant
ValidateReplacementText replacementText, bookmarkName
ValidateTextBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
Set savedFont = targetRange.Font.Duplicate
savedStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = savedStyle
targetRange.Font = savedFont
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2720, , _
"Text bookmark was not restored: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.Text <> replacementText Then
Err.Raise vbObjectError + 2721, , _
"Text bookmark did not verify: " & bookmarkName
End If
End Sub
Private Sub ValidateReplacementText(ByVal replacementText As String, _
ByVal fieldName As String)
If InStr(1, replacementText, vbTab, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbLf, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(11), vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(12), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 2722, , _
"Single-line replacement contains a tab, line break, or page break: " & _
fieldName
End If
End Sub
Private Sub WritePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal picturePath As String)
Dim targetRange As Object
Dim picture As Object
Dim startPosition As Long
Dim maxWidth As Single
Dim maxHeight As Single
ValidatePictureBookmark doc, bookmarkName
RequireFile picturePath, "picture for " & bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.InlineShapes.Count = 1 Then
maxWidth = targetRange.InlineShapes(1).Width
maxHeight = targetRange.InlineShapes(1).Height
Else
maxWidth = 180!
maxHeight = 120!
End If
startPosition = targetRange.Start
targetRange.Delete
targetRange.SetRange startPosition, startPosition
targetRange.Collapse WD_COLLAPSE_START
Set picture = doc.InlineShapes.AddPicture( _
picturePath, False, True, targetRange)
picture.LockAspectRatio = MSO_TRUE
If picture.Width > maxWidth Then picture.Width = maxWidth
If picture.Height > maxHeight Then picture.Height = maxHeight
doc.Bookmarks.Add bookmarkName, picture.Range
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2723, , _
"Picture bookmark was not restored: " & bookmarkName
End If
End Sub
Private Function HasMixedCharacterFormatting(ByVal targetRange As Object) As Boolean
With targetRange.Font
HasMixedCharacterFormatting = _
(.Bold = WD_UNDEFINED) Or _
(.Italic = WD_UNDEFINED) Or _
(.Underline = WD_UNDEFINED) Or _
(.Size = WD_UNDEFINED) Or _
(.Color = WD_UNDEFINED)
End With
End Function
Private Function RangeContainsParagraphOrCellMark(ByVal targetRange As Object) As Boolean
Dim rangeText As String
rangeText = targetRange.Text
RangeContainsParagraphOrCellMark = _
(InStr(1, rangeText, vbCr, vbBinaryCompare) > 0) Or _
(InStr(1, rangeText, Chr$(7), vbBinaryCompare) > 0)
End Function
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal worksheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(worksheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 2730, , _
"Required worksheet is missing: " & worksheetName
End If
End Function
Private Function RequireHeaderColumn(ByVal ws As Worksheet, _
ByVal headerText As String) As Long
Dim lastColumn As Long
Dim columnNumber As Long
Dim matchCount As Long
lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
For columnNumber = 1 To lastColumn
If StrComp(Trim$(CStr(ws.Cells(1, columnNumber).Value2)), _
headerText, vbTextCompare) = 0 Then
RequireHeaderColumn = columnNumber
matchCount = matchCount + 1
End If
Next columnNumber
If matchCount = 0 Then
Err.Raise vbObjectError + 2731, , _
"Required header is missing from row 1: " & headerText
ElseIf matchCount > 1 Then
Err.Raise vbObjectError + 2732, , _
"Duplicate header in row 1: " & headerText
End If
End Function
Private Function ReadCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
ReadCellText = Trim$(CStr(cellValue))
End Function
Private Function RequireCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
RequireCellText = ReadCellText(ws, rowNumber, columnNumber, headerText)
If Len(RequireCellText) = 0 Then
RaiseRowError rowNumber, headerText, "A value is required."
End If
End Function
Private Function RequireDisplayText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
If VarType(cellValue) <> vbString Then
RaiseRowError rowNumber, headerText, _
"Store the final document-ready date as text."
End If
RequireDisplayText = Trim$(CStr(cellValue))
If Len(RequireDisplayText) = 0 Then
RaiseRowError rowNumber, headerText, "A display value is required."
End If
End Function
Private Sub ValidateImageFileName(ByVal fileNameOnly As String, _
ByVal rowNumber As Long, _
ByVal headerText As String)
Dim dotPosition As Long
Dim extensionText As String
If InStr(fileNameOnly, "\") > 0 Or InStr(fileNameOnly, "/") > 0 Or _
InStr(fileNameOnly, ":") > 0 Or InStr(fileNameOnly, "*") > 0 Or _
InStr(fileNameOnly, "?") > 0 Or InStr(fileNameOnly, "<") > 0 Or _
InStr(fileNameOnly, ">") > 0 Or InStr(fileNameOnly, "|") > 0 Or _
InStr(fileNameOnly, Chr$(34)) > 0 Or _
fileNameOnly = "." Or fileNameOnly = ".." Then
RaiseRowError rowNumber, headerText, _
"Use a file name only, without a folder or drive."
End If
dotPosition = InStrRev(fileNameOnly, ".")
If dotPosition = 0 Then
RaiseRowError rowNumber, headerText, _
"The image needs a supported extension."
End If
extensionText = LCase$(Mid$(fileNameOnly, dotPosition))
Select Case extensionText
Case ".jpg", ".jpeg", ".png", ".bmp", ".gif"
'Supported by this example contract.
Case Else
RaiseRowError rowNumber, headerText, _
"Use a tested JPG, JPEG, PNG, BMP, or GIF image."
End Select
End Sub
Private Function CleanFileName(ByVal rawName As String) As String
Dim result As String
Dim invalidCharacters As Variant
Dim item As Variant
Dim position As Long
Dim characterCode As Long
result = Trim$(rawName)
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", "\", "|", "?", "*")
For Each item In invalidCharacters
result = Replace(result, CStr(item), "_")
Next item
For position = 1 To Len(result)
characterCode = AscW(Mid$(result, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Mid$(result, position, 1) = "_"
End If
Next position
result = Left$(result, 80)
Do While Len(result) > 0 And _
(Right$(result, 1) = "." Or Right$(result, 1) = " ")
result = Left$(result, Len(result) - 1)
Loop
If IsReservedWindowsName(result) Then result = "_" & result
CleanFileName = result
End Function
Private Function IsReservedWindowsName(ByVal fileName As String) As Boolean
Dim baseName As String
Dim numberValue As Long
baseName = UCase$(fileName)
Select Case baseName
Case "CON", "PRN", "AUX", "NUL"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(baseName, 3) = "COM" Or Left$(baseName, 3) = "LPT" Then
If Len(baseName) = 4 And IsNumeric(Right$(baseName, 1)) Then
numberValue = CLng(Right$(baseName, 1))
IsReservedWindowsName = (numberValue >= 1 And numberValue <= 9)
End If
End If
End Function
Private Sub RequireFile(ByVal filePath As String, ByVal labelText As String)
If Not FileExists(filePath) Then
Err.Raise vbObjectError + 2740, , _
"Missing " & labelText & ": " & filePath
End If
If FileLen(filePath) = 0 Then
Err.Raise vbObjectError + 2741, , _
"The " & labelText & " is empty: " & filePath
End If
End Sub
Private Sub RequireFolder(ByVal folderPath As String, ByVal labelText As String)
If Not FolderExists(folderPath) Then
Err.Raise vbObjectError + 2742, , _
"Missing " & labelText & ": " & folderPath
End If
End Sub
Private Function FileExists(ByVal filePath As String) As Boolean
On Error Resume Next
FileExists = (Len(Dir$(filePath, _
vbNormal Or vbReadOnly Or vbHidden Or vbSystem)) > 0)
On Error GoTo 0
End Function
Private Function FolderExists(ByVal folderPath As String) As Boolean
Dim attributes As Long
On Error Resume Next
attributes = GetAttr(folderPath)
FolderExists = (Err.Number = 0) And _
((attributes And vbDirectory) = vbDirectory)
Err.Clear
On Error GoTo 0
End Function
Private Sub SafeDeleteTempFile(ByVal filePath As String)
If Len(filePath) = 0 Then Exit Sub
If InStr(1, filePath, "\.__hr_", vbTextCompare) = 0 Then Exit Sub
If FileExists(filePath) Then Kill filePath
End Sub
Private Sub RaiseRowError(ByVal rowNumber As Long, _
ByVal fieldName As String, _
ByVal messageText As String)
Err.Raise vbObjectError + 2750, , _
"Row " & rowNumber & ", " & fieldName & ": " & messageText
End SubOfficial references: InlineShapes.AddPicture, SaveAs2, ExportAsFixedFormat, and Application.DisplayAlerts.
Use a separate Factory run for roster-wide output
One document type
Choose one approved template, such as a standard employee letter, and keep every row on the same field contract.
Many independent rows
Preflight unique employee IDs, required display values, image names, output names, and approval status across the full batch.
One output per row
Generate a separate DOCX, PDF, or both for each selected employee without carrying state from one row to the next.
Separate from the case pack
Do not hide three-template Contractor logic inside a roster batch. Run and audit the two jobs independently.
Add HR controls outside the document macro
Least-privilege folders
Limit access to the workbook, templates, employee photos, signatures, generated files, and backups.
Approval ownership
Define who may set APPROVED, who may run generation, and who reviews the final employee pack. An embedded signature image is not a digital signature or proof of signer authorization. Approve its use separately before releasing HR documents.
Retention schedule
Document how long source files, temporary materials, editable DOCX files, PDFs, and rejected outputs remain.
Template version control
Record the approved release of each HR template. The example validates bookmarks but does not authenticate template content.
Secure delivery
Generation and delivery are separate controls. Choose an approved channel for sending or storing finished HR documents.
Human review
Verify employee identity, dates, role, images, required clauses, pagination, and signatures before release.
Run six controlled HR tests before release
1. Normal approved employee
Confirm all six DOCX/PDF outputs, filenames, fields, photo, signature, and pack folder.
2. Leading-zero EmployeeID
Verify that Excel, all Word documents, PDFs, and file names preserve the identifier exactly.
3. Duplicate EmployeeID
Confirm that selection stops before Word opens and identifies the duplicate key.
4. Missing or unsupported image
Confirm the macro stops before generating the pack and names the exact missing input.
5. Unapproved record
Confirm that a complete but non-APPROVED employee row cannot produce documents.
6. Failure after one template
Confirm partial finals remain visible, temporary files are scoped, and the operator knows which folder to review.
Choose the matching DocxForge Pro module
DocxForge Pro keeps the two HR output shapes separate instead of presenting every spreadsheet-to-Word job as one generic merge.
Contractor
One employee case → several different HR documents → several different Word templates.
Factory
Many independent employee rows → one standard Word template → one separate output per row.
Shared preparation
Approve the Excel data, template release, image files, output names, and review responsibility before generation.
Shared limitation
Software generation does not replace HR authorization, legal review, access control, retention, or secure delivery.




Frequently asked questions
Should one HR automation workflow use Contractor or Factory?
Choose by output shape. Contractor fits one employee case that creates several different documents from different Word templates. Factory fits many employee rows that each produce one standard document from the same template.
Can Excel dates and employee IDs be used directly?
Store identifiers as text when leading zeros matter. Use a document-ready text column such as StartDateDisplay so Word receives the approved HR format rather than an Excel serial number.
Where should employee photos and HR signatures be stored?
The VBA example accepts file names only and resolves them inside controlled local EmployeePhotos and HRSignatures folders. Restrict access to those folders according to your organization’s HR and security policies.
Does local document generation make an HR process compliant?
No. Local processing can reduce unnecessary transfers, but compliance also depends on access control, retention, approvals, lawful processing, audit requirements, and secure disposal. The article provides a technical workflow, not legal certification.
What happens if the three-document pack stops halfway?
The example keeps completed final files, removes only its current temporary files, and reports the unique output folder. Review that folder before rerunning; it does not silently delete material HR documents.
Can the same pattern create a single document for every employee?
Yes. That is the Factory-shaped version: many independent employee rows, one standard Word template, and one separate DOCX/PDF output per row.