Spreadsheet → Word → PDF Workflow for NGO Grant Documentation
Create one controlled grant pack from a reviewed spreadsheet record and several approved Word templates, then inspect every DOCX/PDF pair before release.
Quick answer: separate the grant record from the document register
Keep the approved case data in one GrantCase row. Define each cover letter, project summary, budget narrative, or evidence document in DocumentRegister. Every approved row names a different Word template, filename suffix, exact bookmark contract, and optional signature asset.
Use this multi-template workflow when one grant case needs several different documents. If many independent spreadsheet rows must use one standard Word template, use Factory logic instead.
Define the one-row GrantCase contract
A — GrantID
Unique Windows-safe identifier for the pack and output filenames.
B — ContactName
Reviewed contact name exactly as it should appear in documents.
C — OrganizationName
Legal or approved display name of the applicant organization.
D — ProjectTitle
Approved project or programme title.
E — GrantPeriodDisplay
Document-ready period text, not an Excel date serial.
F — AwardAmountDisplay
Optional reviewed amount text; mandatory only when a template requires it.
G — FunderName
Approved funder or programme name.
H — AuthorizedSignatoryName
Printed name of the authorized signatory.
I — GrantStatus
Use exactly READY, HOLD, or DONE.
Prepare dates, periods, currencies, and identifiers as approved display text in Excel. The macro transfers those values; it does not validate the underlying grant budget.
Use DocumentRegister for the different grant documents
DocumentKey
Unique purpose such as COVER_LETTER, PROJECT_SUMMARY, or BUDGET_NARRATIVE.
TemplateFile
Unique DOCX filename stored in the Templates folder.
OutputSuffix
Unique Windows-safe suffix combined with GrantID.
RequiredBookmarks
Exact semicolon-separated data contract for that template.
SignatureFile
PNG, JPG, or JPEG filename only when SignatureImage is required.
DocumentStatus
Use exactly APPROVED or RETIRED.
GrantID;ContactName;OrganizationName;ProjectTitle;GrantPeriodDisplay;AwardAmountDisplay;FunderName;AuthorizedSignatoryName;SignatureImage
Make every Word template declare an exact contract
Exact bookmark names
Use only GrantID, ContactName, OrganizationName, ProjectTitle, GrantPeriodDisplay, AwardAmountDisplay, FunderName, AuthorizedSignatoryName, and SignatureImage.
Controlled text ranges
Keep one style inside each text bookmark and exclude nested fields, controls, images, and range markers.
Controlled signature use
Require SignatureImage and a signature filename together, or omit both.
Clean review state
Turn off Track Changes, resolve revisions across all Word stories, and remove comments.
Move the grant pack through six control gates
1. Review the grant record
Confirm organization, contact, project, period, displayed amount, funder, and READY status.
2. Approve the document list
Mark only the templates required for this case as APPROVED.
3. Freeze working inputs
Copy every approved template and unique signature into the run-specific InputSnapshot.
4. Validate each template
Check exact bookmarks, tracked revisions, comments, and signature requirements.
5. Stage and move pairs
Create nonempty DOCX/PDF pairs and move them into Completed without overwriting files.
6. Reconcile before release
Review the complete pack against funder instructions and the submission portal before release.
Free Excel VBA example: generate one guarded grant pack
Save the workbook as .xlsm, create GrantCase and DocumentRegister, place templates in Templates, and keep optional signatures in Assets\Signatures. Run GenerateGrantDocumentationPack. The macro snapshots approved inputs, validates each declared contract, rechecks the spreadsheet, and retains partial output for reconciliation.
Option Explicit
Private Const GRANT_SHEET As String = "GrantCase"
Private Const REGISTER_SHEET As String = "DocumentRegister"
Private Const STATUS_READY As String = "READY"
Private Const STATUS_HOLD As String = "HOLD"
Private Const STATUS_DONE As String = "DONE"
Private Const DOC_APPROVED As String = "APPROVED"
Private Const DOC_RETIRED As String = "RETIRED"
Private Const WD_FORMAT_DOCX As Long = 12
Private Const WD_EXPORT_PDF As Long = 17
Private Const WD_ALERTS_NONE As Long = 0
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_COLLAPSE_START As Long = 1
Private Const OFFICE_TRUE As Long = -1
Private Const POINTS_PER_CM As Double = 28.3464567
Public Sub GenerateGrantDocumentationPack()
Dim wb As Workbook
Dim grantWs As Worksheet
Dim registerWs As Worksheet
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
Dim grantData As Variant
Dim registerData As Variant
Dim templateSnapshots As Object
Dim signatureSnapshots As Object
Dim documentKeys As Object
Dim templateFiles As Object
Dim outputSuffixes As Object
Dim requiredBookmarks As Object
Dim grantId As String
Dim contactName As String
Dim organizationName As String
Dim projectTitle As String
Dim grantPeriodDisplay As String
Dim awardAmountDisplay As String
Dim funderName As String
Dim authorizedSignatoryName As String
Dim grantStatus As String
Dim documentKey As String
Dim templateFile As String
Dim outputSuffix As String
Dim bookmarkContract As String
Dim signatureFile As String
Dim documentStatus As String
Dim templateFolder As String
Dim signatureFolder As String
Dim outputRoot As String
Dim runFolder As String
Dim inputFolder As String
Dim templateSnapshotFolder As String
Dim signatureSnapshotFolder As String
Dim stagingFolder As String
Dim completedFolder As String
Dim sourcePath As String
Dim snapshotPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim outputStem As String
Dim lastRegisterRow As Long
Dim dataRow As Long
Dim excelRow As Long
Dim approvedCount As Long
Dim generatedCount As Long
Dim previousScreenUpdating As Boolean
Dim previousEnableEvents As Boolean
Dim stateCaptured As Boolean
Dim currentStep As String
Dim errorNumber As Long
Dim errorDescription As String
Dim finalStatusPending As Boolean
Dim statusRecoveryMessage As String
On Error GoTo CleanFail
Set wb = ThisWorkbook
previousScreenUpdating = Application.ScreenUpdating
previousEnableEvents = Application.EnableEvents
stateCaptured = True
Application.ScreenUpdating = False
Application.EnableEvents = False
currentStep = "checking the macro-enabled workbook"
RequireWritableMacroWorkbook wb
Set grantWs = RequireWorksheet(wb, GRANT_SHEET)
Set registerWs = RequireWorksheet(wb, REGISTER_SHEET)
If grantWs.ProtectContents Or registerWs.ProtectContents Then
Err.Raise vbObjectError + 5100, , _
"GrantCase and DocumentRegister must allow status updates."
End If
ValidateGrantHeaders grantWs
ValidateRegisterHeaders registerWs
RequireOneGrantRow grantWs
grantData = grantWs.Range("A2:I2").Value2
grantId = ReadRequiredText(grantData(1, 1), "GrantID", 2)
contactName = ReadRequiredText(grantData(1, 2), "ContactName", 2)
organizationName = ReadRequiredText(grantData(1, 3), "OrganizationName", 2)
projectTitle = ReadRequiredText(grantData(1, 4), "ProjectTitle", 2)
grantPeriodDisplay = ReadRequiredText( _
grantData(1, 5), "GrantPeriodDisplay", 2)
awardAmountDisplay = ReadOptionalText(grantData(1, 6), "AwardAmountDisplay", 2)
funderName = ReadRequiredText(grantData(1, 7), "FunderName", 2)
authorizedSignatoryName = ReadRequiredText(grantData(1, 8), "AuthorizedSignatoryName", 2)
grantStatus = ReadRequiredText(grantData(1, 9), "GrantStatus", 2)
ValidateGrantStatus grantStatus
If grantStatus <> STATUS_READY Then
Err.Raise vbObjectError + 5101, , _
"GrantCase row 2 must be exactly READY for generation."
End If
ValidateFileComponent grantId, "GrantID", 2
templateFolder = JoinPath(wb.Path, "Templates")
signatureFolder = JoinPath(JoinPath(wb.Path, "Assets"), "Signatures")
outputRoot = JoinPath(wb.Path, "GeneratedGrantPacks")
RequireFolder templateFolder, "template folder"
lastRegisterRow = FindLastDataRow(registerWs, 6)
If lastRegisterRow < 2 Then
Err.Raise vbObjectError + 5102, , _
"DocumentRegister has no document rows."
End If
registerData = registerWs.Range("A2:F" & lastRegisterRow).Value2
Set documentKeys = NewTextDictionary(vbTextCompare)
Set templateFiles = NewTextDictionary(vbTextCompare)
Set outputSuffixes = NewTextDictionary(vbTextCompare)
currentStep = "preflighting the document register"
For dataRow = 1 To UBound(registerData, 1)
excelRow = dataRow + 1
If Not DataRowIsBlank(registerData, dataRow, 6) Then
documentStatus = ReadRequiredText( _
registerData(dataRow, 6), "DocumentStatus", excelRow)
ValidateDocumentStatus documentStatus, excelRow
If documentStatus = DOC_APPROVED Then
documentKey = ReadRequiredText( _
registerData(dataRow, 1), "DocumentKey", excelRow)
templateFile = ReadRequiredText( _
registerData(dataRow, 2), "TemplateFile", excelRow)
outputSuffix = ReadRequiredText( _
registerData(dataRow, 3), "OutputSuffix", excelRow)
bookmarkContract = ReadRequiredText( _
registerData(dataRow, 4), "RequiredBookmarks", excelRow)
signatureFile = ReadOptionalText( _
registerData(dataRow, 5), "SignatureFile", excelRow)
ValidateKey documentKey, "DocumentKey", excelRow
ValidateDocxFileName templateFile, excelRow
ValidateFileComponent outputSuffix, "OutputSuffix", excelRow
AddUnique documentKeys, documentKey, "DocumentKey", excelRow
AddUnique templateFiles, templateFile, "TemplateFile", excelRow
AddUnique outputSuffixes, outputSuffix, "OutputSuffix", excelRow
Set requiredBookmarks = ParseBookmarkContract( _
bookmarkContract, excelRow)
If requiredBookmarks.Exists("AwardAmountDisplay") And _
Len(awardAmountDisplay) = 0 Then
Err.Raise vbObjectError + 5159, , _
"AwardAmountDisplay is required by the template contract " & _
"on DocumentRegister row " & excelRow & "."
End If
ValidateSignatureContract requiredBookmarks, signatureFile, excelRow
sourcePath = JoinPath(templateFolder, templateFile)
RequireNonemptyFile sourcePath, _
"template on DocumentRegister row " & excelRow
If Len(signatureFile) > 0 Then
ValidateImageFileName signatureFile, excelRow
sourcePath = JoinPath(signatureFolder, signatureFile)
RequireNonemptyFile sourcePath, _
"signature on DocumentRegister row " & excelRow
End If
approvedCount = approvedCount + 1
End If
End If
Next dataRow
If approvedCount = 0 Then
Err.Raise vbObjectError + 5103, , _
"DocumentRegister has no rows marked exactly APPROVED."
End If
currentStep = "saving the preflighted workbook"
wb.Save
Set fso = CreateObject("Scripting.FileSystemObject")
EnsureFolder outputRoot
runFolder = CreateRunFolder(fso, outputRoot, grantId)
inputFolder = JoinPath(runFolder, "InputSnapshot")
templateSnapshotFolder = JoinPath(inputFolder, "Templates")
signatureSnapshotFolder = JoinPath(inputFolder, "Signatures")
stagingFolder = JoinPath(runFolder, "Staging")
completedFolder = JoinPath(runFolder, "Completed")
fso.CreateFolder inputFolder
fso.CreateFolder templateSnapshotFolder
fso.CreateFolder signatureSnapshotFolder
fso.CreateFolder stagingFolder
fso.CreateFolder completedFolder
Set templateSnapshots = NewTextDictionary(vbTextCompare)
Set signatureSnapshots = NewTextDictionary(vbTextCompare)
currentStep = "creating the controlled input snapshot"
For dataRow = 1 To UBound(registerData, 1)
If CStr(registerData(dataRow, 6)) = DOC_APPROVED Then
templateFile = CStr(registerData(dataRow, 2))
sourcePath = JoinPath(templateFolder, templateFile)
snapshotPath = JoinPath(templateSnapshotFolder, templateFile)
CopyFileToSnapshot fso, sourcePath, snapshotPath, _
"template " & templateFile
templateSnapshots.Add templateFile, snapshotPath
signatureFile = CStr(registerData(dataRow, 5))
If Len(signatureFile) > 0 And _
Not signatureSnapshots.Exists(signatureFile) Then
sourcePath = JoinPath(signatureFolder, signatureFile)
snapshotPath = JoinPath(signatureSnapshotFolder, signatureFile)
CopyFileToSnapshot fso, sourcePath, snapshotPath, _
"signature " & signatureFile
signatureSnapshots.Add signatureFile, snapshotPath
End If
End If
Next dataRow
currentStep = "starting Microsoft Word Desktop"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.DisplayAlerts = WD_ALERTS_NONE
wdApp.ScreenUpdating = False
For dataRow = 1 To UBound(registerData, 1)
excelRow = dataRow + 1
If CStr(registerData(dataRow, 6)) = DOC_APPROVED Then
currentStep = "rechecking the grant pack and register row " & excelRow
RequireRangeUnchanged grantWs.Range("A2:I2"), grantData, 1, 9, _
"GrantCase row 2"
RequireRangeUnchanged registerWs.Range("A" & excelRow & _
":F" & excelRow), registerData, dataRow, 6, _
"DocumentRegister row " & excelRow
documentKey = CStr(registerData(dataRow, 1))
templateFile = CStr(registerData(dataRow, 2))
outputSuffix = CStr(registerData(dataRow, 3))
bookmarkContract = CStr(registerData(dataRow, 4))
signatureFile = CStr(registerData(dataRow, 5))
Set requiredBookmarks = ParseBookmarkContract( _
bookmarkContract, excelRow)
outputStem = grantId & "_" & outputSuffix
ValidateFileComponent outputStem, "combined output name", excelRow
tempDocxPath = JoinPath(stagingFolder, outputStem & ".docx")
tempPdfPath = JoinPath(stagingFolder, outputStem & ".pdf")
finalDocxPath = JoinPath(completedFolder, outputStem & ".docx")
finalPdfPath = JoinPath(completedFolder, outputStem & ".pdf")
RequireTargetAbsent tempDocxPath
RequireTargetAbsent tempPdfPath
RequireTargetAbsent finalDocxPath
RequireTargetAbsent finalPdfPath
currentStep = "generating " & documentKey
Set doc = wdApp.Documents.Open( _
templateSnapshots(templateFile), False, True, False)
ValidateTemplate doc, requiredBookmarks, templateFile
PopulateRequiredBookmarks doc, requiredBookmarks, _
grantId, contactName, organizationName, projectTitle, _
grantPeriodDisplay, awardAmountDisplay, funderName, _
authorizedSignatoryName, signatureFile, signatureSnapshots
ValidateResultBeforeSave doc, requiredBookmarks, documentKey
doc.SaveAs2 tempDocxPath, WD_FORMAT_DOCX
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_PDF, False, 0, _
0, 1, 1, 0, True, True, 0, True, True, False
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
RequireNonemptyFile tempDocxPath, "staged DOCX " & documentKey
RequireNonemptyFile tempPdfPath, "staged PDF " & documentKey
Name tempDocxPath As finalDocxPath
Name tempPdfPath As finalPdfPath
generatedCount = generatedCount + 1
End If
Next dataRow
currentStep = "confirming the complete pack and document register"
If FindLastDataRow(grantWs, 9) <> 2 Then
Err.Raise vbObjectError + 5160, , _
"GrantCase row count changed after preflight."
End If
If FindLastDataRow(registerWs, 6) <> lastRegisterRow Then
Err.Raise vbObjectError + 5161, , _
"DocumentRegister row count changed after preflight."
End If
RequireTableUnchanged grantWs.Range("A2:I2"), grantData, 1, 9, _
"GrantCase"
RequireTableUnchanged registerWs.Range("A2:F" & lastRegisterRow), _
registerData, UBound(registerData, 1), 6, "DocumentRegister"
If generatedCount <> approvedCount Then
Err.Raise vbObjectError + 5162, , _
"Generated document count does not match the approved count."
End If
currentStep = "saving the generated status to GrantCase"
finalStatusPending = True
grantWs.Cells(2, 9).Value2 = STATUS_DONE
wb.Save
finalStatusPending = False
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
RestoreExcelState previousScreenUpdating, previousEnableEvents
stateCaptured = False
MsgBox "Grant document pack completed." & vbCrLf & _
"Documents generated: " & generatedCount & vbCrLf & _
"Completed folder: " & completedFolder & vbCrLf & vbCrLf & _
"Review organization, grant terms, signatures, DOCX, and PDF before release.", _
vbInformation
CleanExit:
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If stateCaptured Then _
RestoreExcelState previousScreenUpdating, previousEnableEvents
On Error GoTo 0
Exit Sub
CleanFail:
errorNumber = Err.Number
errorDescription = Err.Description
On Error Resume Next
If finalStatusPending And Not grantWs Is Nothing Then
grantWs.Cells(2, 9).Value2 = grantStatus
statusRecoveryMessage = vbCrLf & _
"DONE was restored to " & grantStatus & _
" in memory because the final workbook save was not confirmed."
End If
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then
wdApp.NormalTemplate.Saved = True
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
End If
If stateCaptured Then _
RestoreExcelState previousScreenUpdating, previousEnableEvents
On Error GoTo 0
MsgBox "Grant pack generation stopped while " & currentStep & "." & _
vbCrLf & "Error " & errorNumber & ": " & errorDescription & _
vbCrLf & IIf(Len(runFolder) > 0, _
"Inspect the retained run folder: " & runFolder, _
"No run folder was created.") & vbCrLf & _
"Existing completed files were not deleted." & _
statusRecoveryMessage, vbCritical
End Sub
Private Sub PopulateRequiredBookmarks(ByVal doc As Object, _
ByVal required As Object, _
ByVal grantId As String, _
ByVal contactName As String, _
ByVal organizationName As String, _
ByVal projectTitle As String, _
ByVal grantPeriod As String, _
ByVal awardAmountDisplay As String, _
ByVal funderName As String, _
ByVal authorizedSignatoryName As String, _
ByVal signatureFile As String, _
ByVal signatures As Object)
Dim token As Variant
For Each token In required.Keys
Select Case CStr(token)
Case "GrantID": WriteTextBookmark doc, CStr(token), grantId
Case "ContactName": WriteTextBookmark doc, CStr(token), contactName
Case "OrganizationName": WriteTextBookmark doc, CStr(token), organizationName
Case "ProjectTitle": WriteTextBookmark doc, CStr(token), projectTitle
Case "GrantPeriodDisplay"
WriteTextBookmark doc, CStr(token), grantPeriod
Case "AwardAmountDisplay": WriteTextBookmark doc, CStr(token), awardAmountDisplay
Case "FunderName": WriteTextBookmark doc, CStr(token), funderName
Case "AuthorizedSignatoryName"
WriteTextBookmark doc, CStr(token), authorizedSignatoryName
Case "SignatureImage"
InsertSignatureBookmark doc, CStr(token), _
signatures(signatureFile)
End Select
Next token
End Sub
Private Sub ValidateTemplate(ByVal doc As Object, _
ByVal required As Object, _
ByVal templateFile As String)
RejectTrackedChanges doc, "template " & templateFile
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5104, , _
"Remove comments from template " & templateFile & "."
End If
ValidateBookmarkContract doc, required, templateFile
End Sub
Private Sub ValidateResultBeforeSave(ByVal doc As Object, _
ByVal required As Object, _
ByVal documentKey As String)
RejectTrackedChanges doc, "generated document " & documentKey
If doc.Comments.Count > 0 Then
Err.Raise vbObjectError + 5105, , _
"Generated document contains comments: " & documentKey
End If
ValidateBookmarkContract doc, required, documentKey
End Sub
Private Sub ValidateBookmarkContract(ByVal doc As Object, _
ByVal required As Object, _
ByVal documentLabel As String)
Dim token As Variant
Dim allowed As Object
Set allowed = AllowedBookmarkNames()
For Each token In required.Keys
If Not doc.Bookmarks.Exists(CStr(token)) Then
Err.Raise vbObjectError + 5106, , _
"Missing bookmark " & token & " in " & documentLabel & "."
End If
Next token
For Each token In allowed.Keys
If doc.Bookmarks.Exists(CStr(token)) And _
Not required.Exists(CStr(token)) Then
Err.Raise vbObjectError + 5107, , _
"Bookmark " & token & " exists but is not listed in " & _
"RequiredBookmarks for " & documentLabel & "."
End If
Next token
End Sub
Private Function ParseBookmarkContract(ByVal contractText As String, _
ByVal excelRow As Long) As Object
Dim result As Object
Dim allowed As Object
Dim tokens As Variant
Dim token As Variant
Dim tokenText As String
Set result = NewTextDictionary(vbBinaryCompare)
Set allowed = AllowedBookmarkNames()
tokens = Split(contractText, ";")
For Each token In tokens
tokenText = CStr(token)
If Len(tokenText) = 0 Or tokenText <> Trim$(tokenText) Then
Err.Raise vbObjectError + 5108, , _
"RequiredBookmarks has an empty or padded token on row " & _
excelRow & "."
End If
If Not allowed.Exists(tokenText) Then
Err.Raise vbObjectError + 5109, , _
"Unknown or incorrectly cased bookmark on row " & excelRow & _
": " & tokenText
End If
If result.Exists(tokenText) Then
Err.Raise vbObjectError + 5110, , _
"Duplicate bookmark token on row " & excelRow & ": " & tokenText
End If
result.Add tokenText, True
Next token
If Not result.Exists("GrantID") Or Not result.Exists("OrganizationName") Then
Err.Raise vbObjectError + 5111, , _
"Every approved template must require GrantID and OrganizationName."
End If
Set ParseBookmarkContract = result
End Function
Private Function AllowedBookmarkNames() As Object
Dim result As Object
Dim item As Variant
Set result = NewTextDictionary(vbBinaryCompare)
For Each item In Array("GrantID", "ContactName", "OrganizationName", _
"ProjectTitle", "GrantPeriodDisplay", "AwardAmountDisplay", _
"FunderName", "AuthorizedSignatoryName", "SignatureImage")
result.Add CStr(item), True
Next item
Set AllowedBookmarkNames = result
End Function
Private Sub ValidateSignatureContract(ByVal required As Object, _
ByVal signatureFile As String, _
ByVal excelRow As Long)
If required.Exists("SignatureImage") And Len(signatureFile) = 0 Then
Err.Raise vbObjectError + 5112, , _
"SignatureFile is required when SignatureImage is listed on row " & _
excelRow & "."
End If
If Not required.Exists("SignatureImage") And Len(signatureFile) > 0 Then
Err.Raise vbObjectError + 5113, , _
"SignatureFile must be blank when SignatureImage is not listed " & _
"on row " & excelRow & "."
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String)
Dim targetRange As Object
Dim styleValue As Variant
Set targetRange = doc.Bookmarks(bookmarkName).Range
If targetRange.Fields.Count > 0 Or _
targetRange.ContentControls.Count > 0 Or _
targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 5114, , _
"Text bookmark contains a nested object: " & bookmarkName
End If
RejectRangeMarkers targetRange, bookmarkName
styleValue = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = styleValue
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 5115, , _
"Bookmark was not restored: " & bookmarkName
End If
If StrComp(doc.Bookmarks(bookmarkName).Range.Text, replacementText, _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5116, , _
"Bookmark value differs after replacement: " & bookmarkName
End If
End Sub
Private Sub InsertSignatureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal imagePath As String)
Dim targetRange As Object
Dim picture As Object
Set targetRange = doc.Bookmarks(bookmarkName).Range
If targetRange.Fields.Count > 0 Or _
targetRange.ContentControls.Count > 0 Or _
targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 5117, , _
"Signature bookmark contains a nested object."
End If
RejectRangeMarkers targetRange, bookmarkName
targetRange.Text = ""
targetRange.Collapse WD_COLLAPSE_START
Set picture = doc.InlineShapes.AddPicture( _
imagePath, False, True, targetRange)
If picture.Width <= 0 Or picture.Height <= 0 Then
Err.Raise vbObjectError + 5118, , _
"Word inserted an invalid signature image."
End If
picture.LockAspectRatio = OFFICE_TRUE
If picture.Width > 4.5 * POINTS_PER_CM Then _
picture.Width = 4.5 * POINTS_PER_CM
If picture.Height > 1.8 * POINTS_PER_CM Then _
picture.Height = 1.8 * POINTS_PER_CM
doc.Bookmarks.Add bookmarkName, picture.Range
If doc.Bookmarks(bookmarkName).Range.InlineShapes.Count <> 1 Then
Err.Raise vbObjectError + 5119, , _
"Signature bookmark does not contain one embedded image."
End If
End Sub
Private Sub RejectRangeMarkers(ByVal targetRange As Object, _
ByVal bookmarkName As String)
If InStr(1, targetRange.Text, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, targetRange.Text, Chr$(7), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5120, , _
"Bookmark includes a paragraph or cell marker: " & bookmarkName
End If
End Sub
Private Sub RejectTrackedChanges(ByVal doc As Object, _
ByVal documentLabel As String)
If doc.TrackRevisions Then
Err.Raise vbObjectError + 5121, , _
"Turn off Track Changes in " & documentLabel & "."
End If
If CountRevisionsInAllStories(doc) > 0 Then
Err.Raise vbObjectError + 5122, , _
"Resolve tracked revisions in every story of " & _
documentLabel & "."
End If
End Sub
Private Function CountRevisionsInAllStories(ByVal doc As Object) As Long
Dim root As Object
Dim story As Object
Dim total As Long
For Each root In doc.StoryRanges
Set story = root
Do While Not story Is Nothing
total = total + story.Revisions.Count
Set story = story.NextStoryRange
Loop
Next root
CountRevisionsInAllStories = total
End Function
Private Sub ValidateGrantHeaders(ByVal ws As Worksheet)
ValidateHeaders ws, Array("GrantID", "ContactName", "OrganizationName", _
"ProjectTitle", "GrantPeriodDisplay", "AwardAmountDisplay", _
"FunderName", "AuthorizedSignatoryName", "GrantStatus")
End Sub
Private Sub ValidateRegisterHeaders(ByVal ws As Worksheet)
ValidateHeaders ws, Array("DocumentKey", "TemplateFile", _
"OutputSuffix", "RequiredBookmarks", "SignatureFile", _
"DocumentStatus")
End Sub
Private Sub ValidateHeaders(ByVal ws As Worksheet, ByVal expected As Variant)
Dim columnIndex As Long
Dim valueText As Variant
For columnIndex = 1 To UBound(expected) + 1
valueText = ws.Cells(1, columnIndex).Value2
If IsError(valueText) Or VarType(valueText) <> vbString Then
Err.Raise vbObjectError + 5123, , _
"Header " & ws.Cells(1, columnIndex).Address(False, False) & _
" on " & ws.Name & " must contain exact text."
End If
If StrComp(CStr(valueText), CStr(expected(columnIndex - 1)), _
vbBinaryCompare) <> 0 Then
Err.Raise vbObjectError + 5124, , _
"Expected header " & expected(columnIndex - 1) & _
" in column " & columnIndex & " on " & ws.Name & "."
End If
Next columnIndex
End Sub
Private Sub RequireOneGrantRow(ByVal ws As Worksheet)
Dim lastRow As Long
lastRow = FindLastDataRow(ws, 9)
If lastRow < 2 Then
Err.Raise vbObjectError + 5125, , _
"GrantCase row 2 is missing."
End If
If lastRow > 2 Then
Err.Raise vbObjectError + 5126, , _
"Contractor logic accepts one reviewed GrantCase row per run."
End If
End Sub
Private Function FindLastDataRow(ByVal ws As Worksheet, _
ByVal lastColumn As Long) As Long
Dim lastCell As Range
Set lastCell = ws.Range(ws.Cells(1, 1), _
ws.Cells(ws.Rows.Count, lastColumn)).Find( _
What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=False)
If lastCell Is Nothing Then
FindLastDataRow = 0
Else
FindLastDataRow = lastCell.Row
End If
End Function
Private Function DataRowIsBlank(ByRef data As Variant, _
ByVal dataRow As Long, _
ByVal columnCount As Long) As Boolean
Dim columnIndex As Long
DataRowIsBlank = True
For columnIndex = 1 To columnCount
If IsError(data(dataRow, columnIndex)) Or _
Len(CStr(data(dataRow, columnIndex))) > 0 Then
DataRowIsBlank = False
Exit Function
End If
Next columnIndex
End Function
Private Function ReadRequiredText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
ReadRequiredText = ReadOptionalText(rawValue, fieldName, excelRow)
If Len(ReadRequiredText) = 0 Then
Err.Raise vbObjectError + 5127, , _
fieldName & " is required on row " & excelRow & "."
End If
End Function
Private Function ReadOptionalText(ByVal rawValue As Variant, _
ByVal fieldName As String, _
ByVal excelRow As Long) As String
If IsEmpty(rawValue) Then
ReadOptionalText = ""
Exit Function
End If
If IsError(rawValue) Or VarType(rawValue) <> vbString Then
Err.Raise vbObjectError + 5128, , _
fieldName & " must be stored as text on row " & excelRow & "."
End If
ReadOptionalText = CStr(rawValue)
If ReadOptionalText <> Trim$(ReadOptionalText) Then
Err.Raise vbObjectError + 5129, , _
fieldName & " has outer whitespace on row " & excelRow & "."
End If
If ContainsControlCharacter(ReadOptionalText) Then
Err.Raise vbObjectError + 5130, , _
fieldName & " contains a control character on row " & excelRow & "."
End If
End Function
Private Sub ValidateGrantStatus(ByVal statusText As String)
If statusText <> STATUS_READY And statusText <> STATUS_HOLD And _
statusText <> STATUS_DONE Then
Err.Raise vbObjectError + 5131, , _
"GrantStatus must be exactly READY, HOLD, or DONE."
End If
End Sub
Private Sub ValidateDocumentStatus(ByVal statusText As String, _
ByVal excelRow As Long)
If statusText <> DOC_APPROVED And statusText <> DOC_RETIRED Then
Err.Raise vbObjectError + 5132, , _
"DocumentStatus must be exactly APPROVED or RETIRED on row " & _
excelRow & "."
End If
End Sub
Private Sub ValidateKey(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
If Len(valueText) > 80 Then
Err.Raise vbObjectError + 5133, , _
fieldName & " exceeds 80 characters on row " & excelRow & "."
End If
End Sub
Private Sub ValidateFileComponent(ByVal valueText As String, _
ByVal fieldName As String, _
ByVal excelRow As Long)
Dim item As Variant
Dim baseName As String
Dim lowerName As String
If Len(valueText) > 100 Then
Err.Raise vbObjectError + 5134, , _
fieldName & " exceeds 100 characters on row " & excelRow & "."
End If
For Each item In Array("<", ">", ":", Chr$(34), "/", "\", _
"|", "?", "*")
If InStr(1, valueText, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5135, , _
fieldName & " contains a forbidden Windows character on row " & _
excelRow & "."
End If
Next item
If Right$(valueText, 1) = "." Or Right$(valueText, 1) = " " Then
Err.Raise vbObjectError + 5136, , _
fieldName & " ends with a period or space on row " & excelRow & "."
End If
lowerName = LCase$(valueText)
If Right$(lowerName, 5) = ".docx" Or _
Right$(lowerName, 4) = ".pdf" Then
Err.Raise vbObjectError + 5137, , _
fieldName & " must not include .docx or .pdf on row " & excelRow & "."
End If
baseName = valueText
If InStr(1, baseName, ".", vbBinaryCompare) > 0 Then
baseName = Left$(baseName, InStr(1, baseName, ".", _
vbBinaryCompare) - 1)
End If
If IsReservedWindowsName(baseName) Then
Err.Raise vbObjectError + 5138, , _
fieldName & " uses a reserved Windows name on row " & excelRow & "."
End If
End Sub
Private Sub ValidateDocxFileName(ByVal fileName As String, _
ByVal excelRow As Long)
ValidateAssetFileName fileName, excelRow
If LCase$(Right$(fileName, 5)) <> ".docx" Then
Err.Raise vbObjectError + 5139, , _
"TemplateFile must end with .docx on row " & excelRow & "."
End If
End Sub
Private Sub ValidateImageFileName(ByVal fileName As String, _
ByVal excelRow As Long)
Dim lowerName As String
ValidateAssetFileName fileName, excelRow
lowerName = LCase$(fileName)
If Right$(lowerName, 4) <> ".png" And _
Right$(lowerName, 4) <> ".jpg" And _
Right$(lowerName, 5) <> ".jpeg" Then
Err.Raise vbObjectError + 5140, , _
"SignatureFile must be PNG, JPG, or JPEG on row " & excelRow & "."
End If
End Sub
Private Sub ValidateAssetFileName(ByVal fileName As String, _
ByVal excelRow As Long)
Dim item As Variant
For Each item In Array("<", ">", ":", Chr$(34), "/", "\", _
"|", "?", "*")
If InStr(1, fileName, CStr(item), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 5141, , _
"Asset filename contains a path or wildcard on row " & _
excelRow & "."
End If
Next item
If Right$(fileName, 1) = "." Or Right$(fileName, 1) = " " Then
Err.Raise vbObjectError + 5142, , _
"Asset filename ends with a period or space on row " & excelRow & "."
End If
End Sub
Private Sub AddUnique(ByVal values As Object, ByVal valueText As String, _
ByVal fieldName As String, ByVal excelRow As Long)
If values.Exists(valueText) Then
Err.Raise vbObjectError + 5143, , _
"Duplicate " & fieldName & " on rows " & values(valueText) & _
" and " & excelRow & ": " & valueText
End If
values.Add valueText, excelRow
End Sub
Private Function NewTextDictionary(ByVal compareMode As Long) As Object
Dim result As Object
Set result = CreateObject("Scripting.Dictionary")
result.CompareMode = compareMode
Set NewTextDictionary = result
End Function
Private Sub RequireRangeUnchanged(ByVal liveRange As Range, _
ByRef snapshot As Variant, _
ByVal snapshotRow As Long, _
ByVal columnCount As Long, _
ByVal rangeLabel As String)
Dim liveData As Variant
Dim columnIndex As Long
liveData = liveRange.Value2
For columnIndex = 1 To columnCount
If Not ValuesExactlyEqual(snapshot(snapshotRow, columnIndex), _
liveData(1, columnIndex)) Then
Err.Raise vbObjectError + 5144, , _
rangeLabel & " changed after preflight. Start a new run."
End If
Next columnIndex
End Sub
Private Sub RequireTableUnchanged(ByVal liveRange As Range, _
ByRef snapshot As Variant, _
ByVal expectedRows As Long, _
ByVal expectedColumns As Long, _
ByVal tableLabel As String)
Dim liveData As Variant
Dim rowIndex As Long
Dim columnIndex As Long
If liveRange.Rows.Count <> expectedRows Or _
liveRange.Columns.Count <> expectedColumns Then
Err.Raise vbObjectError + 5163, , _
tableLabel & " dimensions changed after preflight."
End If
liveData = liveRange.Value2
For rowIndex = 1 To expectedRows
For columnIndex = 1 To expectedColumns
If Not ValuesExactlyEqual(snapshot(rowIndex, columnIndex), _
liveData(rowIndex, columnIndex)) Then
Err.Raise vbObjectError + 5164, , _
tableLabel & " changed after preflight at row " & _
(rowIndex + 1) & ", column " & columnIndex & "."
End If
Next columnIndex
Next rowIndex
End Sub
Private Function ValuesExactlyEqual(ByVal firstValue As Variant, _
ByVal secondValue As Variant) As Boolean
If IsError(firstValue) Or IsError(secondValue) Then Exit Function
If VarType(firstValue) <> VarType(secondValue) Then Exit Function
ValuesExactlyEqual = (StrComp(CStr(firstValue), CStr(secondValue), _
vbBinaryCompare) = 0)
End Function
Private Sub CopyFileToSnapshot(ByVal fso As Object, _
ByVal sourcePath As String, _
ByVal snapshotPath As String, _
ByVal fileLabel As String)
Dim sourceSize As Variant
Dim sourceModified As Date
Dim failureText As String
RequireNonemptyFile sourcePath, fileLabel
RequireTargetAbsent snapshotPath
On Error GoTo CopyFailed
sourceSize = fso.GetFile(sourcePath).Size
sourceModified = fso.GetFile(sourcePath).DateLastModified
fso.CopyFile sourcePath, snapshotPath, False
RequireNonemptyFile snapshotPath, fileLabel & " snapshot"
If fso.GetFile(snapshotPath).Size <> sourceSize Then
Err.Raise vbObjectError + 5145, , _
"Snapshot size differs for " & fileLabel & "."
End If
If fso.GetFile(sourcePath).Size <> sourceSize Or _
fso.GetFile(sourcePath).DateLastModified <> sourceModified Then
Err.Raise vbObjectError + 5146, , _
"Source changed while snapshotting " & fileLabel & "."
End If
Exit Sub
CopyFailed:
failureText = Err.Description
Err.Raise vbObjectError + 5147, , _
"Could not snapshot " & fileLabel & ": " & failureText
End Sub
Private Function CreateRunFolder(ByVal fso As Object, _
ByVal outputRoot As String, _
ByVal grantId As String) As String
Dim baseName As String
Dim candidate As String
Dim suffix As Long
baseName = "Run_" & grantId & "_" & Format$(Now, "yyyymmdd_hhnnss")
candidate = JoinPath(outputRoot, baseName)
Do While fso.FolderExists(candidate)
suffix = suffix + 1
candidate = JoinPath(outputRoot, baseName & "_" & _
Format$(suffix, "000"))
Loop
fso.CreateFolder candidate
CreateRunFolder = candidate
End Function
Private Sub RequireWritableMacroWorkbook(ByVal wb As Workbook)
If Len(wb.Path) = 0 Or LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 5148, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
If wb.ReadOnly Then
Err.Raise vbObjectError + 5149, , _
"The workbook is read-only; DONE cannot be saved."
End If
End Sub
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal sheetName As String) As Worksheet
On Error GoTo MissingSheet
Set RequireWorksheet = wb.Worksheets(sheetName)
Exit Function
MissingSheet:
Err.Raise vbObjectError + 5150, , _
"Missing worksheet: " & sheetName
End Function
Private Sub EnsureFolder(ByVal folderPath As String)
If FolderExistsStrict(folderPath) Then Exit Sub
On Error GoTo CreateFailed
MkDir folderPath
Exit Sub
CreateFailed:
Err.Raise vbObjectError + 5151, , _
"Could not create folder: " & folderPath & ". " & Err.Description
End Sub
Private Sub RequireFolder(ByVal folderPath As String, _
ByVal folderLabel As String)
If Not FolderExistsStrict(folderPath) Then
Err.Raise vbObjectError + 5152, , _
"Missing " & folderLabel & ": " & folderPath
End If
End Sub
Private Sub RequireTargetAbsent(ByVal filePath As String)
If FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 5153, , _
"Refusing to overwrite existing file: " & filePath
End If
End Sub
Private Sub RequireNonemptyFile(ByVal filePath As String, _
ByVal fileLabel As String)
If Not FileExistsStrict(filePath) Then
Err.Raise vbObjectError + 5154, , _
"Missing " & fileLabel & ": " & filePath
End If
On Error GoTo InspectFailed
If FileLen(filePath) <= 0 Then
Err.Raise vbObjectError + 5155, , _
"Empty " & fileLabel & ": " & filePath
End If
Exit Sub
InspectFailed:
Err.Raise vbObjectError + 5156, , _
"Could not inspect " & fileLabel & ": " & filePath & _
". " & Err.Description
End Sub
Private Function FileExistsStrict(ByVal filePath As String) As Boolean
On Error GoTo CheckFailed
FileExistsStrict = (Len(Dir$(filePath, _
vbNormal Or vbHidden Or vbSystem Or vbReadOnly)) > 0)
Exit Function
CheckFailed:
Err.Raise vbObjectError + 5157, , _
"Could not check file path: " & filePath & ". " & Err.Description
End Function
Private Function FolderExistsStrict(ByVal folderPath As String) As Boolean
Dim resultText As String
On Error GoTo CheckFailed
resultText = Dir$(folderPath, vbDirectory)
If Len(resultText) > 0 Then
FolderExistsStrict = _
((GetAttr(folderPath) And vbDirectory) = vbDirectory)
End If
Exit Function
CheckFailed:
Err.Raise vbObjectError + 5158, , _
"Could not check folder: " & folderPath & ". " & Err.Description
End Function
Private Function JoinPath(ByVal parentPath As String, _
ByVal childName As String) As String
If Right$(parentPath, 1) = "\" Then
JoinPath = parentPath & childName
Else
JoinPath = parentPath & "\" & childName
End If
End Function
Private Function ContainsControlCharacter(ByVal valueText As String) As Boolean
Dim index As Long
Dim code As Long
For index = 1 To Len(valueText)
code = AscW(Mid$(valueText, index, 1))
If code >= 0 And code <= 31 Then
ContainsControlCharacter = True
Exit Function
End If
Next index
End Function
Private Function IsReservedWindowsName(ByVal baseName As String) As Boolean
Dim upperName As String
Dim suffixText As String
Dim suffixCode As Long
upperName = UCase$(baseName)
Select Case upperName
Case "CON", "PRN", "AUX", "NUL", "CLOCK$"
IsReservedWindowsName = True
Exit Function
End Select
If Len(upperName) >= 4 Then
If Left$(upperName, 3) = "COM" Or Left$(upperName, 3) = "LPT" Then
suffixText = Mid$(upperName, 4)
If Len(suffixText) = 1 Then
suffixCode = AscW(suffixText)
If (suffixCode >= AscW("1") And suffixCode <= AscW("9")) Or _
suffixText = ChrW$(&HB9) Or suffixText = ChrW$(&HB2) Or _
suffixText = ChrW$(&HB3) Then
IsReservedWindowsName = True
End If
End If
End If
End If
End Function
Private Sub RestoreExcelState(ByVal screenUpdatingValue As Boolean, _
ByVal enableEventsValue As Boolean)
Application.EnableEvents = enableEventsValue
Application.ScreenUpdating = screenUpdatingValue
End SubThe example uses Word’s documented bookmark checks, embedded images, DOCX save, and PDF export.
Review the generated grant pack before release
Case data
Match GrantID, organization, contact, project title, period, displayed amount, funder, and document list.
Financial wording
Confirm that amounts and narrative match the approved budget and the funder’s required terminology.
Visual output
Inspect branding, signatures, tables, page breaks, attachments, DOCX, and PDF.
Submission controls
Check file names, portal rules, required evidence, approvals, and the final submission deadline separately.
DONE confirms that the approved register was generated and the workbook save succeeded. It is not evidence of legal approval, funder acceptance, or successful submission.
Know what the VBA example does not guarantee
No compliance decision
The macro cannot decide eligibility or interpret funder rules.
No budget verification
It does not reconcile formulas, totals, exchange rates, or supporting evidence.
No digital signature
An embedded signature image does not authenticate the signer.
No atomic pack commit
File moves and the final workbook save remain separate operations.
No portal submission
Uploading and confirming receipt remain separate controlled steps.
No silent normalization
Names, dates, amounts, statuses, and contracts must already be approved text.
Use Contractor for one multi-document grant case
DocxForge Pro Contractor reuses one reviewed case across several different documents created from different Word templates. That is the grant documentation pack described here.
Contractor fits a cover letter, project summary, budget narrative, and evidence document for one case. Factory is the separate workflow for many independent rows using one standard template.




Frequently asked questions
Is this a batch workflow for many grant applications?
No. This guide uses Contractor logic: one reviewed grant case produces several different documents from different Word templates. For many independent rows using one standard template, use Factory instead.
Which documents can the grant pack include?
The approved register can include a cover letter, project summary, budget narrative, evidence summary, or another controlled document with its own Word template and bookmark contract.
Can AwardAmountDisplay be blank?
Yes, unless an approved template lists AwardAmountDisplay in RequiredBookmarks. Store it as reviewed, document-ready text when the template requires it.
Does the macro confirm grant eligibility or funder compliance?
No. It validates the declared spreadsheet and template contract. A qualified reviewer must still check eligibility, portal requirements, amounts, evidence, wording, and submission readiness.
Does an embedded signature image prove authorization?
No. A signature image is not a digital signature or proof that the named person approved the document. Restrict access and approve its use separately before release.
Can a failed run leave part of the grant pack?
Yes. Each DOCX/PDF pair moves separately. Retain the run folder and reconcile it manually. DONE means generation completed and the workbook saved; it does not mean the pack was approved or submitted.