Document Types & Use Cases

How to Generate Training Attendance Sheets and Completion Letters

Build one session-level attendance sheet from a participant roster, then create separate completion letters only for eligible attendees. Keep the two output levels explicit from Excel through Word and PDF.

One session record
Many participant rows
Eligibility gate
DOCX + PDF
Attendance proves a recorded session result; a completion letter communicates an approved outcome. Do not treat either file as proof of identity, consent, or qualification beyond your underlying controls.

Quick answer: model the session and participant outputs separately

Use a Sessions table for course-level facts and a Participants table for person-level facts. Join them with SessionID. Generate one attendance sheet for the selected session and one completion letter for each READY participant who is PRESENT and eligible.

Do not flatten the workflow

Repeating the session title, trainer, location, and date across participant rows may be convenient for a simple letter batch, but it does not replace a controlled session record or a session-level attendance output.

Define the two-table Excel contract

Setup

Put the target SessionID in Setup!B2. Store identifiers and display-ready dates as true text values.

Sessions A–H

SessionID; CourseTitle; SessionDateDisplay; TrainerName; Location; AttendanceOutputName; SessionStatus; Notes.

Participants A–I

ParticipantID; SessionID; ParticipantName; AttendanceStatus; CompletionEligible; LetterOutputName; CompletionDateDisplay; RecordStatus; Notes.

Exact status values

Use READY or HOLD for session and participant processing. Use PRESENT or ABSENT for attendance. Use YES or NO for completion eligibility.

Build two Word templates with different responsibilities

AttendanceSheetTemplate.docx

Required bookmarks: SessionID, CourseTitle, SessionDateDisplay, TrainerName, Location, and ParticipantTable. Keep ParticipantTable as plain placeholder text where the roster table belongs.

CompletionLetterTemplate.docx

Required bookmarks: ParticipantName, CourseTitle, CompletionDateDisplay, TrainerName, and SessionID. The example restores text bookmarks after replacement.

Folder contract

Save both templates in Templates beside the .xlsm workbook. Output is staged under GeneratedTrainingDocuments\_run_..., then moved to Attendance and Letters.

Make the completion rule explicit before generation

PRESENT + YES + READY

Include the participant on the attendance sheet and generate the completion letter.

PRESENT + NO + READY

Include the participant on the attendance sheet, but require blank letter filename and completion date.

ABSENT + NO + READY

Include the participant with ABSENT status. Do not generate a completion letter.

HOLD

Exclude the row from this run until the underlying data or review issue is resolved.

Blocked combination

The VBA rejects CompletionEligible = YES when AttendanceStatus is not PRESENT. Adapt this rule only after the training owner approves the actual policy.

Free VBA example: generate one controlled training-session set

The macro validates the selected session, participant rows, exact statuses, Word bookmarks, filenames, duplicate IDs, and existing outputs before starting Word.

Session-level roster

Creates one attendance document and inserts a fixed-width Word table for all READY participants in the selected session.

Participant-level letters

Creates separate DOCX and PDF completion letters only for rows that pass the explicit eligibility gate.

Staged output

Creates all files in a unique temporary run folder and rechecks final destinations before moving them.

Honest recovery limit

The final multi-file move is sequential, not atomic. If commit is interrupted, inspect the final folders before rerunning.

Excel VBA — attendance sheet and completion lettersOne selected session per run
Option Explicit

Private Const SETUP_SHEET As String = "Setup"
Private Const SESSIONS_SHEET As String = "Sessions"
Private Const PARTICIPANTS_SHEET As String = "Participants"
Private Const TARGET_SESSION_CELL As String = "B2"
Private Const TEMPLATE_FOLDER As String = "\Templates"
Private Const ATTENDANCE_TEMPLATE As String = "AttendanceSheetTemplate.docx"
Private Const LETTER_TEMPLATE As String = "CompletionLetterTemplate.docx"
Private Const OUTPUT_ROOT As String = "\GeneratedTrainingDocuments"
Private Const ATTENDANCE_FOLDER As String = "\Attendance"
Private Const LETTER_FOLDER As String = "\Letters"
Private Const FIRST_DATA_ROW As Long = 2
Private Const FILE_ATTRIBUTES As Long = 39

Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const ATTENDANCE_PRESENT As String = "PRESENT"
Private Const ATTENDANCE_ABSENT As String = "ABSENT"
Private Const VALUE_YES As String = "YES"
Private Const VALUE_NO As String = "NO"

Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_FORMAT_DOCX As Long = 16
Private Const WD_EXPORT_PDF As Long = 17
Private Const WD_COLLAPSE_START As Long = 1

Public Sub GenerateTrainingSessionDocuments()
    Dim wb As Workbook
    Dim setupWs As Worksheet
    Dim sessionsWs As Worksheet
    Dim participantsWs As Worksheet
    Dim targetSessionId As String
    Dim sessionRow As Long
    Dim participantRows As Collection
    Dim stagedPaths As Collection
    Dim finalPaths As Collection
    Dim outputNames As Object
    Dim participantIds As Object
    Dim attendanceTemplatePath As String
    Dim letterTemplatePath As String
    Dim outputRootPath As String
    Dim attendanceOutputPath As String
    Dim letterOutputPath As String
    Dim runFolder As String
    Dim attendanceOutputName As String
    Dim courseTitle As String
    Dim sessionDateText As String
    Dim trainerName As String
    Dim locationText As String
    Dim wdApp As Object
    Dim doc As Object
    Dim rowNumber As Variant
    Dim errorMessage As String
    Dim letterCount As Long

    On Error GoTo CleanFail

    Set wb = ThisWorkbook
    If Len(wb.Path) = 0 Then
        Err.Raise vbObjectError + 3600, , _
            "Save the workbook as .xlsm before generating documents."
    End If

    Set setupWs = RequireWorksheet(wb, SETUP_SHEET)
    Set sessionsWs = RequireWorksheet(wb, SESSIONS_SHEET)
    Set participantsWs = RequireWorksheet(wb, PARTICIPANTS_SHEET)
    ValidateSessionHeaders sessionsWs
    ValidateParticipantHeaders participantsWs

    targetSessionId = ReadRequiredText(setupWs.Range(TARGET_SESSION_CELL), _
        "Setup!" & TARGET_SESSION_CELL)
    RequireStringValue setupWs.Range(TARGET_SESSION_CELL), _
        "Setup!" & TARGET_SESSION_CELL
    sessionRow = FindUniqueSessionRow(sessionsWs, targetSessionId)

    courseTitle = ReadRequiredText(sessionsWs.Cells(sessionRow, 2), _
        "Sessions!CourseTitle")
    sessionDateText = ReadRequiredText(sessionsWs.Cells(sessionRow, 3), _
        "Sessions!SessionDateDisplay")
    trainerName = ReadRequiredText(sessionsWs.Cells(sessionRow, 4), _
        "Sessions!TrainerName")
    locationText = ReadRequiredText(sessionsWs.Cells(sessionRow, 5), _
        "Sessions!Location")
    attendanceOutputName = ReadRequiredText(sessionsWs.Cells(sessionRow, 6), _
        "Sessions!AttendanceOutputName")
    ValidateExactValue ReadRequiredText(sessionsWs.Cells(sessionRow, 7), _
        "Sessions!SessionStatus"), Array(STATUS_READY, STATUS_HOLD), _
        "Sessions!SessionStatus"
    If CStr(sessionsWs.Cells(sessionRow, 7).Value2) <> STATUS_READY Then
        Err.Raise vbObjectError + 3601, , _
            "The selected session must have SessionStatus = READY."
    End If
    RequireStringValue sessionsWs.Cells(sessionRow, 1), "Sessions!SessionID"
    RequireStringValue sessionsWs.Cells(sessionRow, 3), _
        "Sessions!SessionDateDisplay"
    ValidateFileStem attendanceOutputName, "AttendanceOutputName"

    attendanceTemplatePath = wb.Path & TEMPLATE_FOLDER & _
        "\" & ATTENDANCE_TEMPLATE
    letterTemplatePath = wb.Path & TEMPLATE_FOLDER & _
        "\" & LETTER_TEMPLATE
    RequireFileEntry attendanceTemplatePath, "Attendance template"
    RequireFileEntry letterTemplatePath, "Completion-letter template"

    outputRootPath = wb.Path & OUTPUT_ROOT
    attendanceOutputPath = outputRootPath & ATTENDANCE_FOLDER
    letterOutputPath = outputRootPath & LETTER_FOLDER

    Set participantRows = New Collection
    Set outputNames = CreateObject("Scripting.Dictionary")
    outputNames.CompareMode = vbTextCompare
    Set participantIds = CreateObject("Scripting.Dictionary")
    participantIds.CompareMode = vbTextCompare
    PreflightParticipants participantsWs, targetSessionId, _
        attendanceOutputName, attendanceOutputPath, letterOutputPath, _
        participantRows, outputNames, participantIds, letterCount

    If participantRows.Count = 0 Then
        Err.Raise vbObjectError + 3602, , _
            "No READY participant rows were found for session " & _
            targetSessionId & "."
    End If

    EnsureFolder outputRootPath
    EnsureFolder attendanceOutputPath
    EnsureFolder letterOutputPath
    runFolder = CreateRunFolder(outputRootPath)
    Set stagedPaths = New Collection
    Set finalPaths = New Collection

    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False
    wdApp.ScreenUpdating = False
    wdApp.DisplayAlerts = WD_ALERTS_NONE

    Set doc = wdApp.Documents.Add(attendanceTemplatePath, False, 0, False)
    ValidateBookmarks doc, Array("SessionID", "CourseTitle", _
        "SessionDateDisplay", "TrainerName", "Location", _
        "ParticipantTable"), "attendance template"
    WriteRequiredBookmark doc, "SessionID", targetSessionId
    WriteRequiredBookmark doc, "CourseTitle", courseTitle
    WriteRequiredBookmark doc, "SessionDateDisplay", sessionDateText
    WriteRequiredBookmark doc, "TrainerName", trainerName
    WriteRequiredBookmark doc, "Location", locationText
    BuildAttendanceTable doc, "ParticipantTable", participantRows, _
        participantsWs
    StageDocument doc, runFolder, "attendance", _
        attendanceOutputName, attendanceOutputPath, stagedPaths, finalPaths
    doc.Close WD_DO_NOT_SAVE_CHANGES
    Set doc = Nothing

    For Each rowNumber In participantRows
        If ShouldGenerateLetter(participantsWs, CLng(rowNumber)) Then
            Set doc = wdApp.Documents.Add(letterTemplatePath, False, 0, False)
            ValidateBookmarks doc, Array("ParticipantName", "CourseTitle", _
                "CompletionDateDisplay", "TrainerName", "SessionID"), _
                "completion-letter template"
            WriteRequiredBookmark doc, "ParticipantName", _
                CStr(participantsWs.Cells(CLng(rowNumber), 3).Value2)
            WriteRequiredBookmark doc, "CourseTitle", courseTitle
            WriteRequiredBookmark doc, "CompletionDateDisplay", _
                CStr(participantsWs.Cells(CLng(rowNumber), 7).Value2)
            WriteRequiredBookmark doc, "TrainerName", trainerName
            WriteRequiredBookmark doc, "SessionID", targetSessionId
            StageDocument doc, runFolder, _
                "letter_" & Format$(CLng(rowNumber), "000000"), _
                CStr(participantsWs.Cells(CLng(rowNumber), 6).Value2), _
                letterOutputPath, stagedPaths, finalPaths
            doc.Close WD_DO_NOT_SAVE_CHANGES
            Set doc = Nothing
        End If
    Next rowNumber

    wdApp.NormalTemplate.Saved = True
    wdApp.Quit WD_DO_NOT_SAVE_CHANGES
    Set wdApp = Nothing

    CommitStagedFiles stagedPaths, finalPaths
    RemoveRunFolder runFolder

    MsgBox "Training documents generated." & vbCrLf & _
        "Attendance sheets: 1" & vbCrLf & _
        "Completion letters: " & letterCount, _
        vbInformation, "Training session documents"
    Exit Sub

CleanFail:
    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.NormalTemplate.Saved = True
        wdApp.Quit WD_DO_NOT_SAVE_CHANGES
    End If
    If Len(runFolder) > 0 Then RemoveRunFolder runFolder
    On Error GoTo 0
    MsgBox "Generation stopped: " & errorMessage & vbCrLf & _
        "Review the final folders for any files already moved during commit.", _
        vbCritical, "Training session documents"
End Sub

Private Function RequireWorksheet(ByVal wb As Workbook, _
                                  ByVal sheetName As String) As Worksheet
    On Error Resume Next
    Set RequireWorksheet = wb.Worksheets(sheetName)
    On Error GoTo 0
    If RequireWorksheet Is Nothing Then
        Err.Raise vbObjectError + 3610, , _
            "Required worksheet not found: " & sheetName
    End If
End Function

Private Sub ValidateSessionHeaders(ByVal ws As Worksheet)
    ValidateHeaders ws, Array("SessionID", "CourseTitle", _
        "SessionDateDisplay", "TrainerName", "Location", _
        "AttendanceOutputName", "SessionStatus", "Notes"), "Sessions"
End Sub

Private Sub ValidateParticipantHeaders(ByVal ws As Worksheet)
    ValidateHeaders ws, Array("ParticipantID", "SessionID", _
        "ParticipantName", "AttendanceStatus", "CompletionEligible", _
        "LetterOutputName", "CompletionDateDisplay", "RecordStatus", _
        "Notes"), "Participants"
End Sub

Private Sub ValidateHeaders(ByVal ws As Worksheet, _
                            ByVal expected As Variant, _
                            ByVal sheetLabel As String)
    Dim columnNumber As Long
    Dim actualText As String
    For columnNumber = LBound(expected) To UBound(expected)
        actualText = CStr(ws.Cells(1, columnNumber + 1).Value2)
        If StrComp(actualText, CStr(expected(columnNumber)), _
                   vbBinaryCompare) <> 0 Then
            Err.Raise vbObjectError + 3611, , _
                sheetLabel & " header " & _
                ws.Cells(1, columnNumber + 1).Address(False, False) & _
                " must match exactly: " & expected(columnNumber)
        End If
    Next columnNumber
End Sub

Private Function ReadRequiredText(ByVal sourceCell As Range, _
                                  ByVal fieldLabel As String) As String
    Dim rawValue As Variant
    rawValue = sourceCell.Value2
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 3612, , _
            fieldLabel & " contains an Excel error."
    End If
    ReadRequiredText = CStr(rawValue)
    If Len(ReadRequiredText) = 0 Then
        Err.Raise vbObjectError + 3613, , _
            fieldLabel & " is required."
    End If
    If StrComp(ReadRequiredText, Trim$(ReadRequiredText), _
               vbBinaryCompare) <> 0 Then
        Err.Raise vbObjectError + 3614, , _
            fieldLabel & " has leading or trailing spaces."
    End If
    If ContainsControlCharacter(ReadRequiredText) Then
        Err.Raise vbObjectError + 3615, , _
            fieldLabel & " contains a control character."
    End If
End Function

Private Function ReadOptionalText(ByVal sourceCell As Range, _
                                  ByVal fieldLabel As String) As String
    Dim rawValue As Variant
    rawValue = sourceCell.Value2
    If IsError(rawValue) Then
        Err.Raise vbObjectError + 3612, , _
            fieldLabel & " contains an Excel error."
    End If
    ReadOptionalText = CStr(rawValue)
    If StrComp(ReadOptionalText, Trim$(ReadOptionalText), _
               vbBinaryCompare) <> 0 Then
        Err.Raise vbObjectError + 3614, , _
            fieldLabel & " has leading or trailing spaces."
    End If
    If ContainsControlCharacter(ReadOptionalText) Then
        Err.Raise vbObjectError + 3615, , _
            fieldLabel & " contains a control character."
    End If
End Function

Private Sub RequireStringValue(ByVal sourceCell As Range, _
                               ByVal fieldLabel As String)
    Dim rawValue As Variant
    rawValue = sourceCell.Value2
    If IsError(rawValue) Then Exit Sub
    If VarType(rawValue) <> vbString Then
        Err.Raise vbObjectError + 3616, , _
            fieldLabel & " must be stored as text."
    End If
End Sub

Private Sub ValidateExactValue(ByVal actualValue As String, _
                               ByVal allowedValues As Variant, _
                               ByVal fieldLabel As String)
    Dim item As Variant
    For Each item In allowedValues
        If StrComp(actualValue, CStr(item), vbBinaryCompare) = 0 Then Exit Sub
    Next item
    Err.Raise vbObjectError + 3617, , _
        fieldLabel & " has an unsupported value: " & actualValue
End Sub

Private Function FindUniqueSessionRow(ByVal ws As Worksheet, _
                                      ByVal targetSessionId As String) As Long
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim candidate As String
    lastRow = LastUsedRow(ws, 8)
    For rowNumber = FIRST_DATA_ROW To lastRow
        If WorksheetFunction.CountA(ws.Range(ws.Cells(rowNumber, 1), _
            ws.Cells(rowNumber, 8))) > 0 Then
            candidate = ReadRequiredText(ws.Cells(rowNumber, 1), _
                "Sessions!SessionID row " & rowNumber)
            RequireStringValue ws.Cells(rowNumber, 1), _
                "Sessions!SessionID row " & rowNumber
            If StrComp(candidate, targetSessionId, vbBinaryCompare) = 0 Then
                If FindUniqueSessionRow > 0 Then
                    Err.Raise vbObjectError + 3618, , _
                        "Duplicate selected SessionID on Sessions rows " & _
                        FindUniqueSessionRow & " and " & rowNumber & "."
                End If
                FindUniqueSessionRow = rowNumber
            End If
        End If
    Next rowNumber
    If FindUniqueSessionRow = 0 Then
        Err.Raise vbObjectError + 3619, , _
            "Selected SessionID was not found: " & targetSessionId
    End If
End Function

Private Function LastUsedRow(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
        Err.Raise vbObjectError + 3620, , _
            "Worksheet is empty: " & ws.Name
    End If
    LastUsedRow = lastCell.Row
End Function

Private Sub PreflightParticipants(ByVal ws As Worksheet, _
                                  ByVal targetSessionId As String, _
                                  ByVal attendanceOutputName As String, _
                                  ByVal attendanceFolder As String, _
                                  ByVal letterFolder As String, _
                                  ByVal participantRows As Collection, _
                                  ByVal outputNames As Object, _
                                  ByVal participantIds As Object, _
                                  ByRef letterCount As Long)
    Dim lastRow As Long
    Dim rowNumber As Long
    Dim participantId As String
    Dim rowSessionId As String
    Dim participantName As String
    Dim attendanceStatus As String
    Dim completionEligible As String
    Dim letterOutputName As String
    Dim completionDateText As String
    Dim recordStatus As String
    Dim outputKey As String

    ValidateOutputPair attendanceFolder, attendanceOutputName, _
        "attendance sheet"
    outputNames.Add "A:" & attendanceOutputName, "attendance sheet"
    lastRow = LastUsedRow(ws, 9)

    For rowNumber = FIRST_DATA_ROW To lastRow
        If WorksheetFunction.CountA(ws.Range(ws.Cells(rowNumber, 1), _
            ws.Cells(rowNumber, 9))) = 0 Then GoTo NextRow

        rowSessionId = ReadRequiredText(ws.Cells(rowNumber, 2), _
            "Participants!SessionID row " & rowNumber)
        RequireStringValue ws.Cells(rowNumber, 2), _
            "Participants!SessionID row " & rowNumber
        If StrComp(rowSessionId, targetSessionId, vbBinaryCompare) <> 0 Then _
            GoTo NextRow

        recordStatus = ReadRequiredText(ws.Cells(rowNumber, 8), _
            "Participants!RecordStatus row " & rowNumber)
        ValidateExactValue recordStatus, Array(STATUS_READY, STATUS_HOLD), _
            "Participants!RecordStatus row " & rowNumber
        If recordStatus = STATUS_HOLD Then GoTo NextRow

        participantId = ReadRequiredText(ws.Cells(rowNumber, 1), _
            "Participants!ParticipantID row " & rowNumber)
        RequireStringValue ws.Cells(rowNumber, 1), _
            "Participants!ParticipantID row " & rowNumber
        If participantIds.Exists(participantId) Then
            Err.Raise vbObjectError + 3621, , _
                "Duplicate ParticipantID for the selected session on rows " & _
                participantIds(participantId) & " and " & rowNumber & "."
        End If
        participantIds.Add participantId, rowNumber

        participantName = ReadRequiredText(ws.Cells(rowNumber, 3), _
            "Participants!ParticipantName row " & rowNumber)
        attendanceStatus = ReadRequiredText(ws.Cells(rowNumber, 4), _
            "Participants!AttendanceStatus row " & rowNumber)
        ValidateExactValue attendanceStatus, _
            Array(ATTENDANCE_PRESENT, ATTENDANCE_ABSENT), _
            "Participants!AttendanceStatus row " & rowNumber
        completionEligible = ReadRequiredText(ws.Cells(rowNumber, 5), _
            "Participants!CompletionEligible row " & rowNumber)
        ValidateExactValue completionEligible, Array(VALUE_YES, VALUE_NO), _
            "Participants!CompletionEligible row " & rowNumber

        participantRows.Add rowNumber

        If completionEligible = VALUE_YES And _
           attendanceStatus <> ATTENDANCE_PRESENT Then
            Err.Raise vbObjectError + 3622, , _
                "CompletionEligible = YES requires AttendanceStatus = PRESENT " & _
                "on Participants row " & rowNumber & "."
        End If

        If completionEligible = VALUE_YES Then
            letterOutputName = ReadRequiredText(ws.Cells(rowNumber, 6), _
                "Participants!LetterOutputName row " & rowNumber)
            completionDateText = ReadRequiredText(ws.Cells(rowNumber, 7), _
                "Participants!CompletionDateDisplay row " & rowNumber)
            RequireStringValue ws.Cells(rowNumber, 7), _
                "Participants!CompletionDateDisplay row " & rowNumber
            ValidateFileStem letterOutputName, _
                "LetterOutputName row " & rowNumber
            outputKey = "L:" & letterOutputName
            If outputNames.Exists(outputKey) Then
                Err.Raise vbObjectError + 3623, , _
                    "Duplicate completion-letter output name on Participants row " & _
                    rowNumber & ": " & letterOutputName
            End If
            outputNames.Add outputKey, rowNumber
            ValidateOutputPair letterFolder, letterOutputName, _
                "completion letter row " & rowNumber
            letterCount = letterCount + 1
        Else
            If Len(ReadOptionalText(ws.Cells(rowNumber, 6), _
                "Participants!LetterOutputName row " & rowNumber)) > 0 Or _
               Len(ReadOptionalText(ws.Cells(rowNumber, 7), _
                "Participants!CompletionDateDisplay row " & rowNumber)) > 0 Then
                Err.Raise vbObjectError + 3624, , _
                    "CompletionEligible = NO requires blank letter output and " & _
                    "completion date on Participants row " & rowNumber & "."
            End If
        End If
NextRow:
    Next rowNumber
End Sub

Private Function ShouldGenerateLetter(ByVal ws As Worksheet, _
                                      ByVal rowNumber As Long) As Boolean
    ShouldGenerateLetter = _
        (CStr(ws.Cells(rowNumber, 4).Value2) = ATTENDANCE_PRESENT And _
         CStr(ws.Cells(rowNumber, 5).Value2) = VALUE_YES)
End Function

Private Sub ValidateBookmarks(ByVal doc As Object, _
                              ByVal bookmarkNames As Variant, _
                              ByVal templateLabel As String)
    Dim item As Variant
    For Each item In bookmarkNames
        If Not doc.Bookmarks.Exists(CStr(item)) Then
            Err.Raise vbObjectError + 3630, , _
                "Missing bookmark " & item & " in " & templateLabel & "."
        End If
    Next item
End Sub

Private Sub WriteRequiredBookmark(ByVal doc As Object, _
                                  ByVal bookmarkName As String, _
                                  ByVal replacementText As String)
    Dim targetRange As Object
    Dim savedStyle As Variant
    If Len(replacementText) = 0 Then
        Err.Raise vbObjectError + 3631, , _
            "Required bookmark value is blank: " & bookmarkName
    End If
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    If targetRange.Fields.Count > 0 Or _
       targetRange.ContentControls.Count > 0 Or _
       targetRange.InlineShapes.Count > 0 Then
        Err.Raise vbObjectError + 3632, , _
            "Bookmark contains a field, content control, or image: " & _
            bookmarkName
    End If
    savedStyle = targetRange.Style
    targetRange.Text = replacementText
    targetRange.Style = savedStyle
    doc.Bookmarks.Add bookmarkName, targetRange
    If Not doc.Bookmarks.Exists(bookmarkName) Then
        Err.Raise vbObjectError + 3633, , _
            "Bookmark was not restored: " & bookmarkName
    End If
    If Len(Trim$(doc.Bookmarks(bookmarkName).Range.Text)) = 0 Then
        Err.Raise vbObjectError + 3634, , _
            "Bookmark is empty after replacement: " & bookmarkName
    End If
End Sub

Private Sub BuildAttendanceTable(ByVal doc As Object, _
                                 ByVal bookmarkName As String, _
                                 ByVal participantRows As Collection, _
                                 ByVal ws As Worksheet)
    Dim targetRange As Object
    Dim tableItem As Object
    Dim tableRow As Long
    Dim sourceRow As Variant
    Set targetRange = doc.Bookmarks(bookmarkName).Range
    If targetRange.Fields.Count > 0 Or _
       targetRange.ContentControls.Count > 0 Or _
       targetRange.InlineShapes.Count > 0 Or _
       targetRange.Tables.Count > 0 Then
        Err.Raise vbObjectError + 3635, , _
            "ParticipantTable bookmark must contain plain placeholder text."
    End If
    targetRange.Text = vbNullString
    targetRange.Collapse WD_COLLAPSE_START
    Set tableItem = doc.Tables.Add(targetRange, _
        participantRows.Count + 1, 3, 0, 0)
    tableItem.AllowAutoFit = False
    tableItem.Borders.Enable = True
    tableItem.Cell(1, 1).Range.Text = "Participant"
    tableItem.Cell(1, 2).Range.Text = "Participant ID"
    tableItem.Cell(1, 3).Range.Text = "Attendance"
    tableRow = 2
    For Each sourceRow In participantRows
        tableItem.Cell(tableRow, 1).Range.Text = _
            CStr(ws.Cells(CLng(sourceRow), 3).Value2)
        tableItem.Cell(tableRow, 2).Range.Text = _
            CStr(ws.Cells(CLng(sourceRow), 1).Value2)
        tableItem.Cell(tableRow, 3).Range.Text = _
            CStr(ws.Cells(CLng(sourceRow), 4).Value2)
        tableRow = tableRow + 1
    Next sourceRow
End Sub

Private Sub StageDocument(ByVal doc As Object, _
                          ByVal runFolder As String, _
                          ByVal stageKey As String, _
                          ByVal outputName As String, _
                          ByVal finalFolder As String, _
                          ByVal stagedPaths As Collection, _
                          ByVal finalPaths As Collection)
    Dim stagedDocx As String
    Dim stagedPdf As String
    Dim finalDocx As String
    Dim finalPdf As String
    stagedDocx = runFolder & "\" & stageKey & ".docx"
    stagedPdf = runFolder & "\" & stageKey & ".pdf"
    finalDocx = finalFolder & "\" & outputName & ".docx"
    finalPdf = finalFolder & "\" & outputName & ".pdf"
    doc.SaveAs2 stagedDocx, WD_FORMAT_DOCX
    doc.ExportAsFixedFormat stagedPdf, WD_EXPORT_PDF
    If Not FileExistsChecked(stagedDocx) Or _
       Not FileExistsChecked(stagedPdf) Then
        Err.Raise vbObjectError + 3636, , _
            "Word did not create both staged files for " & outputName & "."
    End If
    stagedPaths.Add stagedDocx
    finalPaths.Add finalDocx
    stagedPaths.Add stagedPdf
    finalPaths.Add finalPdf
End Sub

Private Sub CommitStagedFiles(ByVal stagedPaths As Collection, _
                              ByVal finalPaths As Collection)
    Dim itemNumber As Long
    Dim sourcePath As String
    Dim destinationPath As String
    If stagedPaths.Count <> finalPaths.Count Then
        Err.Raise vbObjectError + 3640, , _
            "Internal staged-file mapping is inconsistent."
    End If
    For itemNumber = 1 To finalPaths.Count
        If FileExistsChecked(CStr(finalPaths(itemNumber))) Then
            Err.Raise vbObjectError + 3641, , _
                "Final file appeared during generation; nothing was overwritten: " & _
                finalPaths(itemNumber)
        End If
        If Not FileExistsChecked(CStr(stagedPaths(itemNumber))) Then
            Err.Raise vbObjectError + 3642, , _
                "Staged file is missing: " & stagedPaths(itemNumber)
        End If
    Next itemNumber
    For itemNumber = 1 To finalPaths.Count
        sourcePath = CStr(stagedPaths(itemNumber))
        destinationPath = CStr(finalPaths(itemNumber))
        Name sourcePath As destinationPath
    Next itemNumber
End Sub

Private Sub ValidateOutputPair(ByVal folderPath As String, _
                               ByVal outputName As String, _
                               ByVal outputLabel As String)
    ValidateFileStem outputName, outputLabel
    If FileExistsChecked(folderPath & "\" & outputName & ".docx") Or _
       FileExistsChecked(folderPath & "\" & outputName & ".pdf") Then
        Err.Raise vbObjectError + 3643, , _
            "Output already exists for " & outputLabel & ": " & outputName
    End If
End Sub

Private Sub ValidateFileStem(ByVal fileStem As String, _
                             ByVal fieldLabel As String)
    Dim forbidden As Variant
    Dim item As Variant
    Dim baseName As String
    Dim lowerStem As String
    If Len(fileStem) = 0 Or Len(fileStem) > 120 Then
        Err.Raise vbObjectError + 3650, , _
            fieldLabel & " must contain 1 to 120 characters."
    End If
    If StrComp(fileStem, Trim$(fileStem), vbBinaryCompare) <> 0 Or _
       Right$(fileStem, 1) = "." Or Right$(fileStem, 1) = " " Then
        Err.Raise vbObjectError + 3651, , _
            fieldLabel & " has unsafe outer characters."
    End If
    forbidden = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    For Each item In forbidden
        If InStr(1, fileStem, CStr(item), vbBinaryCompare) > 0 Then
            Err.Raise vbObjectError + 3652, , _
                fieldLabel & " contains a forbidden Windows character."
        End If
    Next item
    If ContainsControlCharacter(fileStem) Then
        Err.Raise vbObjectError + 3653, , _
            fieldLabel & " contains a Windows control character."
    End If
    lowerStem = LCase$(fileStem)
    If Right$(lowerStem, 5) = ".docx" Or _
       Right$(lowerStem, 4) = ".pdf" Then
        Err.Raise vbObjectError + 3655, , _
            fieldLabel & " must be a filename stem without .docx or .pdf."
    End If
    baseName = fileStem
    If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
        baseName = Left$(baseName, InStr(1, baseName, ".", _
            vbBinaryCompare) - 1)
    End If
    If IsReservedWindowsName(UCase$(baseName)) Then
        Err.Raise vbObjectError + 3654, , _
            fieldLabel & " starts with a reserved Windows device name."
    End If
End Sub

Private Function IsReservedWindowsName(ByVal upperBase As String) As Boolean
    Dim finalCharacter As String
    Select Case upperBase
        Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
            IsReservedWindowsName = True
            Exit Function
    End Select
    If Left$(upperBase, 3) = "COM" Or Left$(upperBase, 3) = "LPT" Then
        If Len(upperBase) = 4 And Mid$(upperBase, 4, 1) Like "[1-9]" Then
            IsReservedWindowsName = True
            Exit Function
        End If
        If Len(upperBase) = 4 Then
            finalCharacter = Mid$(upperBase, 4, 1)
            If finalCharacter = ChrW$(&HB9) Or _
               finalCharacter = ChrW$(&HB2) Or _
               finalCharacter = ChrW$(&HB3) Then
                IsReservedWindowsName = True
            End If
        End If
    End If
End Function

Private Function ContainsControlCharacter(ByVal textValue As String) As Boolean
    Dim position As Long
    Dim characterCode As Long
    For position = 1 To Len(textValue)
        characterCode = AscW(Mid$(textValue, position, 1))
        If characterCode >= 0 And characterCode < 32 Then
            ContainsControlCharacter = True
            Exit Function
        End If
    Next position
End Function

Private Sub RequireFileEntry(ByVal filePath As String, _
                             ByVal fileLabel As String)
    Dim foundName As String
    Dim attributes As Long
    On Error GoTo CheckFailed
    foundName = Dir$(filePath, FILE_ATTRIBUTES)
    If Len(foundName) = 0 Then
        Err.Raise vbObjectError + 3660, , _
            fileLabel & " was not found: " & filePath
    End If
    attributes = GetAttr(filePath)
    If (attributes And vbDirectory) = vbDirectory Then
        Err.Raise vbObjectError + 3661, , _
            fileLabel & " is a folder, not a file: " & filePath
    End If
    Exit Sub
CheckFailed:
    If Err.Number >= vbObjectError + 3660 And _
       Err.Number <= vbObjectError + 3661 Then
        Err.Raise Err.Number, , Err.Description
    End If
    Err.Raise vbObjectError + 3662, , _
        "Could not check " & fileLabel & ": " & filePath & _
        ". " & Err.Description
End Sub

Private Function FileExistsChecked(ByVal filePath As String) As Boolean
    Dim foundName As String
    Dim attributes As Long
    On Error GoTo CheckFailed
    foundName = Dir$(filePath, FILE_ATTRIBUTES)
    If Len(foundName) = 0 Then
        FileExistsChecked = False
        Exit Function
    End If
    attributes = GetAttr(filePath)
    If (attributes And vbDirectory) = vbDirectory Then
        Err.Raise vbObjectError + 3666, , _
            "Expected a file but found a folder: " & filePath
    End If
    FileExistsChecked = True
    Exit Function
CheckFailed:
    Err.Raise vbObjectError + 3663, , _
        "Could not check file path: " & filePath & ". " & Err.Description
End Function

Private Sub EnsureFolder(ByVal folderPath As String)
    Dim attributes As Long
    If Len(Dir$(folderPath, vbDirectory)) = 0 Then
        MkDir folderPath
    Else
        attributes = GetAttr(folderPath)
        If (attributes And vbDirectory) <> vbDirectory Then
            Err.Raise vbObjectError + 3664, , _
                "Expected a folder but found a file: " & folderPath
        End If
    End If
End Sub

Private Function CreateRunFolder(ByVal outputRootPath As String) As String
    Dim attempt As Long
    Dim candidate As String
    For attempt = 0 To 99
        candidate = outputRootPath & "\_run_" & _
            Format$(Now, "yyyymmdd_hhnnss") & "_" & Format$(attempt, "00")
        If Len(Dir$(candidate, vbDirectory)) = 0 Then
            MkDir candidate
            CreateRunFolder = candidate
            Exit Function
        End If
    Next attempt
    Err.Raise vbObjectError + 3665, , _
        "Could not create a unique temporary run folder."
End Function

Private Sub RemoveRunFolder(ByVal runFolder As String)
    Dim itemName As String
    If Len(runFolder) = 0 Then Exit Sub
    itemName = Dir$(runFolder & "\*", FILE_ATTRIBUTES)
    Do While Len(itemName) > 0
        If itemName <> "." And itemName <> ".." Then
            If (GetAttr(runFolder & "\" & itemName) And vbDirectory) = 0 Then
                Kill runFolder & "\" & itemName
            End If
        End If
        itemName = Dir$()
    Loop
    RmDir runFolder
End Sub

Official Word VBA references: Tables.Add, Bookmarks.Add, and SaveAs2.

Review the set before release

Roster identity

Compare SessionID, participant IDs, names, and attendance status with the approved source.

Eligibility evidence

Confirm every generated letter belongs to a participant who met the approved rule.

Template release

Verify the exact attendance and completion-letter templates used for the run.

DOCX and PDF rendering

Inspect long names, page breaks, table overflow, headers, footers, and PDF pagination.

Output inventory

Reconcile one attendance pair plus the expected number of completion-letter pairs.

Delivery and retention

Apply the approved distribution, privacy, and retention process after document generation.

Separate the product workflows by output level

DocxForge Pro supports both shapes, but they represent different production runs.

Contractor: one training-session case

Use shared session data across several different documents created from different Word templates, such as a session cover, attendance form, and trainer summary.

Factory: participant completion letters

Use many independent participant rows with one standard completion-letter template, producing a separate output for each selected row.

Variable roster boundary

The free VBA example builds a variable-length Word table. Do not assume Contractor expands participant rows the same way; test the exact attendance-form design and use a separately prepared roster when the product workflow does not provide the required table behavior.

DocxForge Pro Contractor shared data for one training session
Contractor: maintain the shared training-session data.
DocxForge Pro Contractor documents for a training session case
Contractor: configure different session-level documents.
DocxForge Pro Factory participant rows for completion letters
Factory: review independent participant rows.
DocxForge Pro Factory completion letter template and output settings
Factory: confirm one completion-letter template and output settings.
Run the approved training-document workflows locallyTry DocxForge Pro for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Session case packsLetter batchesLocal processingDOCX + PDF
Start Free 7-Day Trial

Frequently asked questions

Should one Excel row create both an attendance sheet and a completion letter?

No. An attendance sheet belongs to the training session and lists multiple participants. A completion letter belongs to one eligible participant. Keep Sessions and Participants as separate tables joined by SessionID.

Who should receive a completion letter?

The example generates a letter only when AttendanceStatus is PRESENT, CompletionEligible is YES, and RecordStatus is READY. Your organization must define and approve the actual eligibility rule.

What Word templates does the VBA example require?

It uses AttendanceSheetTemplate.docx for the session-level attendance sheet and CompletionLetterTemplate.docx for participant-level letters. The templates use the exact bookmarks listed in the setup section.

Does the macro overwrite existing DOCX or PDF files?

No during the normal single-process workflow. It checks both extensions before Word starts, stages every file in a unique run folder, checks the final paths again, and uses file moves that fail if a destination already exists. A multi-file commit is not atomic, so inspect the final folders if the commit is interrupted.

Can the attendance sheet include signatures?

The example creates a roster table with names, participant IDs, and attendance values. Add signature columns only after defining whether signatures are handwritten after printing, captured through an approved system, or inserted under a separate authorization process.

How do Contractor and Factory fit this training workflow?

Contractor fits a session-level case pack made from different templates. Factory fits many participant rows generating the same type of completion letter from one standard template. Treat them as separate production runs unless your tested process explicitly coordinates both.

Keep session documents and participant letters in their correct workflowRun DocxForge Pro locally. 7 days free, then $38 every 3 months • 14-day refund after purchase.
Two output levelsApproved templatesEligibility gatesControlled output
Start Free 7-Day Trial