HR Document Workflows

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.

Approved employee record
Multiple Word templates
Photos + signatures
DOCX + PDF
Local generation is a technical control, not a complete HR privacy, retention, or compliance program.

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.

Do not multiply the two counts

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.

Required row-1 headers

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.

Excel VBA — three-template employee packWord Desktop on Windows required
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 Sub

Official 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.

Contractor: one employee case, several templates
DocxForge Pro Contractor case data for one employee document pack
Review the approved employee case data once.
DocxForge Pro Contractor documents for several HR templates
Select the different documents required for the employee pack.
Factory: many employees, one standard template
DocxForge Pro Factory Excel data for a roster-wide HR batch
Review the employee rows selected for the standard document.
DocxForge Pro Factory document settings for one HR template
Confirm the standard template and output settings.
Use the HR workflow that matches the document resultTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Contractor packsFactory batchesLocal processingDOCX + PDF
Start Free 7-Day Trial

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.

Turn approved employee data into the right document resultCreate employee packs or roster-wide documents locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
One case or many rowsControlled templatesPhotos + signaturesDOCX + PDF
Start Free 7-Day Trial