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




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.