How to Keep Word Template Formatting While Replacing Excel Values
Keep Word styles, table layout, and image placement stable by giving every Excel value a controlled destination. Use one uniformly formatted bookmark per value, restore the bookmark after replacement, and validate six difficult records before a full batch.
Quick answer: preserve a formatting contract, not just placeholder text
Microsoft documents that setting Range.Text replaces a range with plain text. The reliable pattern is to make the target range simple, capture its uniform formatting, replace only that range, reapply the formatting, and restore the bookmark.
Text values
Give each value its own Word bookmark. Keep the bookmark inside one paragraph or table cell, exclude paragraph and end-of-cell marks, and apply one character style across the placeholder.
Images
Give the logo or photo a separate bookmark. Insert the image with that exact range, embed it in the document, preserve aspect ratio, and fit it inside a tested bounding box.
A bookmark that spans bold and regular words, several paragraphs, nested fields, or an end-of-cell marker needs to be redesigned or handled by a purpose-built routine. Splitting the range is safer than trying to infer its original layout after replacement.
Reference: Microsoft Word Range.Text property.
Define one formatting-safe bookmark contract
Build the template so the macro does not have to guess which formatting belongs to the replacement.
One value per bookmark
Use names such as ClientName, InvoiceNumber, and InvoiceDateDisplay. Do not place several unrelated values inside one bookmark.
One uniform text format
Apply one character style and one direct-formatting state to the whole placeholder. If a sentence needs mixed formatting, keep the fixed words outside the bookmark.
No structural marks
Show formatting marks in Word and confirm the bookmark does not include ¶ or the table end-of-cell marker. Those marks carry structure beyond the text value.
Separate image bookmark
Use LogoImage around one placeholder picture or a short token. Keep it separate from captions, surrounding text, and floating shapes.
Templates\FormattingSafeTemplate.docx contains the four bookmarks. Assets\ contains optional logo files. GeneratedDocuments\ must already exist and should be empty for the six-record pilot.
References: Word Bookmarks.Add and Word Font.Duplicate.
Diagnose six common formatting failures
The visible result usually identifies which part of the template contract was broken.
Font changes after replacement
The routine wrote plain text but did not reapply the target font or style. Capture them before replacement and restore them on the updated range.
Only part of the value is bold
The placeholder already contained mixed character formatting. Split the destination into uniform bookmarks instead of collapsing several formats into one value.
Paragraph spacing changes
The bookmark probably included a paragraph mark. Recreate it around the placeholder text only, leaving ¶ outside the range.
Table cell layout changes
The bookmark may include the end-of-cell marker or the inserted text may exceed the tested cell capacity. Fix the bookmark boundary and test long values.
Image appears at page one
The insertion call omitted the intended range. Pass the saved bookmark range to InlineShapes.AddPicture rather than relying on the selection or document start.
Bookmark disappears
Replacing its contents can remove the bookmark. Add it back around the new text or inserted image and verify it before saving.
Prepare document-ready Excel values
The helper finds headers by name, processes only READY rows, and treats display fields as the exact text Word should receive.
Required headers
Row 1 must contain DocumentID, ClientName, InvoiceNumber, InvoiceDateDisplay, LogoFile, and Status. Their order may vary.
Display text stays explicit
Store InvoiceDateDisplay exactly as it should appear in Word. Format DocumentID and InvoiceNumber as Text before importing values that require leading zeros.
LogoFile is a file name
Use a file name such as client-logo.png, not a drive path. The macro accepts files only from the workbook’s Assets folder.
READY is the release gate
Mark only the six pilot rows READY. Rows with other statuses are ignored until the template and output have passed review.
INV-0042 | Northwind Studio | 000184 | November 17, 2026 | northwind.png | READY
Free VBA helper: replace values and restore uniform formatting
Save the Excel file as an Excel Macro-Enabled Workbook (.xlsm). Put the three folders beside it, create the six headers on a sheet named Data, and add this code to a standard Excel VBA module.
Preflight before Word
The macro validates the workbook, headers, READY rows, single-line fields, output names, asset files, and existing outputs before it opens Word.
Template validation
It confirms all four bookmarks exist in the main document body, rejects structural marks and embedded fields inside text placeholders, and stops on common mixed-format states.
Formatting restoration
For each text value it duplicates the target font, records the style, replaces the text, reapplies both, restores the bookmark, and verifies the result.
Safer file promotion
Word first writes uniquely named temporary files. The macro moves them to final names only after checking again that no output appeared after preflight.
Option Explicit
Private Const DATA_SHEET As String = "Data"
Private Const TEMPLATE_RELATIVE_PATH As String = "\Templates\FormattingSafeTemplate.docx"
Private Const OUTPUT_RELATIVE_FOLDER As String = "\GeneratedDocuments"
Private Const ASSET_RELATIVE_FOLDER As String = "\Assets"
Private Const FIRST_DATA_ROW As Long = 2
Private Const READY_STATUS As String = "READY"
Private Const EXPORT_PDF As Boolean = True
Private Const HEADER_DOCUMENT_ID As String = "DocumentID"
Private Const HEADER_CLIENT_NAME As String = "ClientName"
Private Const HEADER_INVOICE_NUMBER As String = "InvoiceNumber"
Private Const HEADER_INVOICE_DATE As String = "InvoiceDateDisplay"
Private Const HEADER_LOGO_FILE As String = "LogoFile"
Private Const HEADER_STATUS As String = "Status"
Private Const BOOKMARK_CLIENT_NAME As String = "ClientName"
Private Const BOOKMARK_INVOICE_NUMBER As String = "InvoiceNumber"
Private Const BOOKMARK_INVOICE_DATE As String = "InvoiceDateDisplay"
Private Const BOOKMARK_LOGO As String = "LogoImage"
Private Const WD_DO_NOT_SAVE_CHANGES As Long = 0
Private Const WD_FORMAT_XML_DOCUMENT As Long = 12
Private Const WD_EXPORT_FORMAT_PDF As Long = 17
Private Const WD_COLLAPSE_START As Long = 1
Private Const WD_UNDEFINED As Long = 9999999
Private Const WD_MAIN_TEXT_STORY As Long = 1
Private Const MSO_TRUE As Long = -1
Private Const WORD_ALERTS_NONE As Long = 0
Public Sub GenerateFormattingSafeDocuments()
Dim wb As Workbook
Dim ws As Worksheet
Dim documentIdColumn As Long
Dim clientNameColumn As Long
Dim invoiceNumberColumn As Long
Dim invoiceDateColumn As Long
Dim logoFileColumn As Long
Dim statusColumn As Long
Dim lastRow As Long
Dim rowNumber As Long
Dim readyCount As Long
Dim completedCount As Long
Dim documentId As String
Dim clientName As String
Dim invoiceNumber As String
Dim invoiceDateText As String
Dim logoFile As String
Dim logoPath As String
Dim statusText As String
Dim safeBaseName As String
Dim templatePath As String
Dim outputFolder As String
Dim assetFolder As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim runToken As String
Dim seenNames As Object
Dim fso As Object
Dim wdApp As Object
Dim doc As Object
On Error GoTo CleanFail
Set wb = ThisWorkbook
If Len(wb.Path) = 0 Then
Err.Raise vbObjectError + 2300, , _
"Save the workbook before running document generation."
End If
If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 2301, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
Set ws = RequireWorksheet(wb, DATA_SHEET)
documentIdColumn = RequireHeaderColumn(ws, HEADER_DOCUMENT_ID)
clientNameColumn = RequireHeaderColumn(ws, HEADER_CLIENT_NAME)
invoiceNumberColumn = RequireHeaderColumn(ws, HEADER_INVOICE_NUMBER)
invoiceDateColumn = RequireHeaderColumn(ws, HEADER_INVOICE_DATE)
logoFileColumn = RequireHeaderColumn(ws, HEADER_LOGO_FILE)
statusColumn = RequireHeaderColumn(ws, HEADER_STATUS)
templatePath = wb.Path & TEMPLATE_RELATIVE_PATH
outputFolder = wb.Path & OUTPUT_RELATIVE_FOLDER
assetFolder = wb.Path & ASSET_RELATIVE_FOLDER
RequireFile templatePath, "Word template"
RequireFolder outputFolder, "output folder"
RequireFolder assetFolder, "asset folder"
lastRow = LastUsedRowInColumns(ws, Array( _
documentIdColumn, clientNameColumn, invoiceNumberColumn, _
invoiceDateColumn, logoFileColumn, statusColumn))
If lastRow < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 2302, , _
"No records were found on the " & DATA_SHEET & " sheet."
End If
Set seenNames = CreateObject("Scripting.Dictionary")
seenNames.CompareMode = vbTextCompare
For rowNumber = FIRST_DATA_ROW To lastRow
statusText = UCase$(Trim$(CStr(ws.Cells(rowNumber, statusColumn).Value2)))
If statusText = READY_STATUS Then
documentId = RequireCellText(ws, rowNumber, documentIdColumn, HEADER_DOCUMENT_ID)
clientName = RequireCellText(ws, rowNumber, clientNameColumn, HEADER_CLIENT_NAME)
invoiceNumber = RequireCellText(ws, rowNumber, invoiceNumberColumn, HEADER_INVOICE_NUMBER)
invoiceDateText = RequireCellText(ws, rowNumber, invoiceDateColumn, HEADER_INVOICE_DATE)
logoFile = Trim$(CStr(ws.Cells(rowNumber, logoFileColumn).Value2))
safeBaseName = CleanFileName(documentId)
If Len(safeBaseName) = 0 Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The ID does not contain a usable Windows file name."
End If
If seenNames.Exists(safeBaseName) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The cleaned output name duplicates READY row " & seenNames(safeBaseName) & "."
End If
seenNames.Add safeBaseName, rowNumber
finalDocxPath = outputFolder & "\" & safeBaseName & ".docx"
finalPdfPath = outputFolder & "\" & safeBaseName & ".pdf"
If Len(finalDocxPath) > 218 Or Len(finalPdfPath) > 218 Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The output path is too long for this example workflow."
End If
If FileExists(finalDocxPath) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The DOCX output already exists: " & finalDocxPath
End If
If EXPORT_PDF And FileExists(finalPdfPath) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The PDF output already exists: " & finalPdfPath
End If
If Len(logoFile) > 0 Then
logoPath = ResolveAssetFile(assetFolder, logoFile, rowNumber)
RequireFile logoPath, "logo for row " & rowNumber
End If
readyCount = readyCount + 1
End If
Next rowNumber
If readyCount = 0 Then
Err.Raise vbObjectError + 2303, , _
"No rows have Status = " & READY_STATUS & "."
End If
Set fso = CreateObject("Scripting.FileSystemObject")
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = WORD_ALERTS_NONE
Set doc = wdApp.Documents.Open(templatePath, False, True, False)
ValidateTemplate doc
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & Format$(CLng(Timer * 1000), "00000000")
For rowNumber = FIRST_DATA_ROW To lastRow
statusText = UCase$(Trim$(CStr(ws.Cells(rowNumber, statusColumn).Value2)))
If statusText = READY_STATUS Then
documentId = Trim$(CStr(ws.Cells(rowNumber, documentIdColumn).Value2))
clientName = RequireCellText(ws, rowNumber, clientNameColumn, HEADER_CLIENT_NAME)
invoiceNumber = RequireCellText(ws, rowNumber, invoiceNumberColumn, HEADER_INVOICE_NUMBER)
invoiceDateText = RequireCellText(ws, rowNumber, invoiceDateColumn, HEADER_INVOICE_DATE)
logoFile = Trim$(CStr(ws.Cells(rowNumber, logoFileColumn).Value2))
safeBaseName = CleanFileName(documentId)
If Len(logoFile) > 0 Then
logoPath = ResolveAssetFile(assetFolder, logoFile, rowNumber)
Else
logoPath = vbNullString
End If
finalDocxPath = outputFolder & "\" & safeBaseName & ".docx"
finalPdfPath = outputFolder & "\" & safeBaseName & ".pdf"
tempDocxPath = outputFolder & "\.__formatpilot_" & runToken & _
"_" & Format$(rowNumber, "000000") & ".docx"
tempPdfPath = outputFolder & "\.__formatpilot_" & runToken & _
"_" & Format$(rowNumber, "000000") & ".pdf"
If FileExists(tempDocxPath) Or FileExists(tempPdfPath) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"A temporary file name collision was detected. Run the macro again."
End If
Set doc = wdApp.Documents.Open(templatePath, False, False, False)
WriteTextBookmark doc, BOOKMARK_CLIENT_NAME, clientName, False
WriteTextBookmark doc, BOOKMARK_INVOICE_NUMBER, invoiceNumber, False
WriteTextBookmark doc, BOOKMARK_INVOICE_DATE, invoiceDateText, False
WritePictureBookmark doc, BOOKMARK_LOGO, logoPath, True
doc.SaveAs2 tempDocxPath, WD_FORMAT_XML_DOCUMENT, False, vbNullString, False
If Not FileExists(tempDocxPath) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"Word did not create the temporary DOCX file."
End If
If FileLen(tempDocxPath) < 100 Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The temporary DOCX file is unexpectedly small."
End If
If EXPORT_PDF Then
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_FORMAT_PDF
If Not FileExists(tempPdfPath) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"Word did not create the temporary PDF file."
End If
If FileLen(tempPdfPath) < 100 Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"The temporary PDF file is unexpectedly small."
End If
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If FileExists(finalDocxPath) Or _
(EXPORT_PDF And FileExists(finalPdfPath)) Then
RaiseRowError rowNumber, HEADER_DOCUMENT_ID, _
"An output appeared after preflight. No existing file was replaced."
End If
fso.MoveFile tempDocxPath, finalDocxPath
tempDocxPath = vbNullString
If EXPORT_PDF Then
fso.MoveFile tempPdfPath, finalPdfPath
tempPdfPath = vbNullString
End If
completedCount = completedCount + 1
End If
Next rowNumber
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
MsgBox completedCount & " formatting-safe documents created." & vbCrLf & _
"Inspect the six-record pilot before marking the remaining rows READY.", _
vbInformation, "Generation complete"
Exit Sub
CleanFail:
Dim errorNumber As Long
Dim errorMessage As String
errorNumber = Err.Number
errorMessage = Err.Description
On Error Resume Next
If Not doc Is Nothing Then doc.Close WD_DO_NOT_SAVE_CHANGES
If Not wdApp Is Nothing Then wdApp.Quit WD_DO_NOT_SAVE_CHANGES
SafeDeleteTempFile tempDocxPath
SafeDeleteTempFile tempPdfPath
On Error GoTo 0
MsgBox "Generation stopped. Existing output files were not intentionally replaced." & _
vbCrLf & "Error " & errorNumber & ": " & errorMessage, _
vbCritical, "Formatting-safe generation stopped"
End Sub
Private Sub ValidateTemplate(ByVal doc As Object)
ValidateTextBookmark doc, BOOKMARK_CLIENT_NAME
ValidateTextBookmark doc, BOOKMARK_INVOICE_NUMBER
ValidateTextBookmark doc, BOOKMARK_INVOICE_DATE
ValidatePictureBookmark doc, BOOKMARK_LOGO
End Sub
Private Sub ValidateTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2310, , _
"Required Word bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.Start = targetRange.End Then
Err.Raise vbObjectError + 2311, , _
"Text bookmark must contain a styled placeholder: " & bookmarkName
End If
If targetRange.StoryType <> WD_MAIN_TEXT_STORY Then
Err.Raise vbObjectError + 2317, , _
"Text bookmark must be in the main document body: " & bookmarkName
End If
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2312, , _
"Bookmark must not include a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Or targetRange.InlineShapes.Count > 0 Then
Err.Raise vbObjectError + 2318, , _
"Text bookmark must contain plain placeholder text, not fields or images: " & bookmarkName
End If
If HasMixedCharacterFormatting(targetRange) Then
Err.Raise vbObjectError + 2313, , _
"Bookmark uses mixed character formatting. Split it into uniform bookmarks: " & bookmarkName
End If
End Sub
Private Sub ValidatePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String)
Dim targetRange As Object
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2314, , _
"Required picture bookmark is missing: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.StoryType <> WD_MAIN_TEXT_STORY Then
Err.Raise vbObjectError + 2319, , _
"Picture bookmark must be in the main document body: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Then
Err.Raise vbObjectError + 2323, , _
"Picture bookmark must not contain fields: " & bookmarkName
End If
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2315, , _
"Picture bookmark must not include a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 1 Then
Err.Raise vbObjectError + 2316, , _
"Picture bookmark may contain one placeholder image at most: " & bookmarkName
End If
End Sub
Private Sub WriteTextBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String, _
ByVal allowBlank As Boolean)
Dim targetRange As Object
Dim savedFont As Object
Dim savedStyle As Variant
If Not allowBlank And Len(Trim$(replacementText)) = 0 Then
Err.Raise vbObjectError + 2320, , _
"A required replacement is blank: " & bookmarkName
End If
ValidateTextBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
Set savedFont = targetRange.Font.Duplicate
savedStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = savedStyle
targetRange.Font = savedFont
doc.Bookmarks.Add bookmarkName, targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2321, , _
"Bookmark was not restored after replacement: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.Text <> replacementText Then
Err.Raise vbObjectError + 2322, , _
"Bookmark text did not verify after replacement: " & bookmarkName
End If
End Sub
Private Sub WritePictureBookmark(ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal picturePath As String, _
ByVal allowBlank As Boolean)
Dim targetRange As Object
Dim picture As Object
Dim startPosition As Long
Dim maxWidth As Single
Dim maxHeight As Single
If Len(picturePath) = 0 And Not allowBlank Then
Err.Raise vbObjectError + 2330, , _
"A required picture is blank: " & bookmarkName
End If
ValidatePictureBookmark doc, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If targetRange.InlineShapes.Count = 1 Then
maxWidth = targetRange.InlineShapes(1).Width
maxHeight = targetRange.InlineShapes(1).Height
Else
maxWidth = 144!
maxHeight = 72!
End If
startPosition = targetRange.Start
targetRange.Delete
targetRange.SetRange startPosition, startPosition
targetRange.Collapse WD_COLLAPSE_START
If Len(picturePath) = 0 Then
doc.Bookmarks.Add bookmarkName, targetRange
Exit Sub
End If
RequireFile picturePath, "picture for bookmark " & bookmarkName
Set picture = doc.InlineShapes.AddPicture( _
picturePath, False, True, targetRange)
picture.LockAspectRatio = MSO_TRUE
If picture.Width > maxWidth Then picture.Width = maxWidth
If picture.Height > maxHeight Then picture.Height = maxHeight
doc.Bookmarks.Add bookmarkName, picture.Range
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2331, , _
"Picture bookmark was not restored: " & bookmarkName
End If
End Sub
Private Function RangeContainsParagraphOrCellMark(ByVal targetRange As Object) As Boolean
Dim rangeText As String
rangeText = targetRange.Text
RangeContainsParagraphOrCellMark = _
(InStr(1, rangeText, vbCr, vbBinaryCompare) > 0) Or _
(InStr(1, rangeText, Chr$(7), vbBinaryCompare) > 0)
End Function
Private Function HasMixedCharacterFormatting(ByVal targetRange As Object) As Boolean
With targetRange.Font
HasMixedCharacterFormatting = _
(.Bold = WD_UNDEFINED) Or _
(.Italic = WD_UNDEFINED) Or _
(.Underline = WD_UNDEFINED) Or _
(.Size = WD_UNDEFINED) Or _
(.Color = WD_UNDEFINED)
End With
End Function
Private Function RequireWorksheet(ByVal wb As Workbook, _
ByVal worksheetName As String) As Worksheet
On Error Resume Next
Set RequireWorksheet = wb.Worksheets(worksheetName)
On Error GoTo 0
If RequireWorksheet Is Nothing Then
Err.Raise vbObjectError + 2340, , _
"Required worksheet is missing: " & worksheetName
End If
End Function
Private Function RequireHeaderColumn(ByVal ws As Worksheet, _
ByVal headerText As String) As Long
Dim lastHeaderColumn As Long
Dim columnNumber As Long
Dim matchCount As Long
lastHeaderColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
For columnNumber = 1 To lastHeaderColumn
If StrComp(Trim$(CStr(ws.Cells(1, columnNumber).Value2)), _
headerText, vbTextCompare) = 0 Then
RequireHeaderColumn = columnNumber
matchCount = matchCount + 1
End If
Next columnNumber
If matchCount = 0 Then
Err.Raise vbObjectError + 2341, , _
"Required header missing from row 1: " & headerText
ElseIf matchCount > 1 Then
Err.Raise vbObjectError + 2342, , _
"Duplicate header in row 1: " & headerText
End If
End Function
Private Function LastUsedRowInColumns(ByVal ws As Worksheet, _
ByVal columnsToCheck As Variant) As Long
Dim item As Variant
Dim candidateRow As Long
For Each item In columnsToCheck
candidateRow = ws.Cells(ws.Rows.Count, CLng(item)).End(xlUp).Row
If candidateRow > LastUsedRowInColumns Then
LastUsedRowInColumns = candidateRow
End If
Next item
End Function
Private Function RequireCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnNumber).Value2
If IsError(cellValue) Then
RaiseRowError rowNumber, headerText, "The cell contains an Excel error."
End If
RequireCellText = Trim$(CStr(cellValue))
If Len(RequireCellText) = 0 Then
RaiseRowError rowNumber, headerText, "A value is required for READY rows."
End If
If InStr(RequireCellText, vbCr) > 0 Or InStr(RequireCellText, vbLf) > 0 Or _
InStr(RequireCellText, vbTab) > 0 Or InStr(RequireCellText, Chr$(11)) > 0 Or _
InStr(RequireCellText, Chr$(7)) > 0 Then
RaiseRowError rowNumber, headerText, _
"A single-line bookmark value must not contain line breaks or control markers."
End If
End Function
Private Function ResolveAssetFile(ByVal assetFolder As String, _
ByVal fileNameOnly As String, _
ByVal rowNumber As Long) As String
Dim cleanedName As String
cleanedName = Trim$(fileNameOnly)
If cleanedName <> DirNameOnly(cleanedName) Then
RaiseRowError rowNumber, HEADER_LOGO_FILE, _
"Use a file name only, not a path. Store the file inside the Assets folder."
End If
If InStr(cleanedName, "\") > 0 Or InStr(cleanedName, "/") > 0 Or _
InStr(cleanedName, ":") > 0 Or cleanedName = "." Or cleanedName = ".." Then
RaiseRowError rowNumber, HEADER_LOGO_FILE, _
"Use a file name only, without folders or drive letters."
End If
ResolveAssetFile = assetFolder & "\" & cleanedName
End Function
Private Function DirNameOnly(ByVal fileNameOnly As String) As String
Dim invalidCharacters As Variant
Dim item As Variant
DirNameOnly = fileNameOnly
invalidCharacters = Array("<", ">", "|", Chr$(34), "?", "*")
For Each item In invalidCharacters
If InStr(DirNameOnly, CStr(item)) > 0 Then
DirNameOnly = vbNullString
Exit Function
End If
Next item
End Function
Private Function CleanFileName(ByVal rawName As String) As String
Dim result As String
Dim invalidCharacters As Variant
Dim item As Variant
Dim characterCode As Long
Dim position As Long
result = Trim$(rawName)
invalidCharacters = Array("<", ">", ":", Chr$(34), "/", "\", "|", "?", "*")
For Each item In invalidCharacters
result = Replace(result, CStr(item), "_")
Next item
For position = 1 To Len(result)
characterCode = AscW(Mid$(result, position, 1))
If characterCode >= 0 And characterCode < 32 Then
Mid$(result, position, 1) = "_"
End If
Next position
result = Left$(result, 120)
Do While Len(result) > 0 And _
(Right$(result, 1) = "." Or Right$(result, 1) = " ")
result = Left$(result, Len(result) - 1)
Loop
If IsReservedWindowsName(result) Then result = "_" & result
CleanFileName = result
End Function
Private Function IsReservedWindowsName(ByVal fileName As String) As Boolean
Dim baseName As String
Dim dotPosition As Long
Dim numberValue As Long
baseName = UCase$(fileName)
dotPosition = InStr(1, baseName, ".", vbBinaryCompare)
If dotPosition > 0 Then baseName = Left$(baseName, dotPosition - 1)
Select Case baseName
Case "CON", "PRN", "AUX", "NUL"
IsReservedWindowsName = True
Exit Function
End Select
If Left$(baseName, 3) = "COM" Or Left$(baseName, 3) = "LPT" Then
If Len(baseName) = 4 And IsNumeric(Right$(baseName, 1)) Then
numberValue = CLng(Right$(baseName, 1))
IsReservedWindowsName = (numberValue >= 1 And numberValue <= 9)
End If
End If
End Function
Private Sub RequireFile(ByVal filePath As String, ByVal labelText As String)
If Not FileExists(filePath) Then
Err.Raise vbObjectError + 2350, , _
"Missing " & labelText & ": " & filePath
End If
End Sub
Private Sub RequireFolder(ByVal folderPath As String, ByVal labelText As String)
If Not FolderExists(folderPath) Then
Err.Raise vbObjectError + 2351, , _
"Missing " & labelText & ": " & folderPath
End If
End Sub
Private Function FileExists(ByVal filePath As String) As Boolean
On Error Resume Next
FileExists = (Len(Dir$(filePath, vbNormal Or vbReadOnly Or vbHidden Or vbSystem)) > 0)
On Error GoTo 0
End Function
Private Function FolderExists(ByVal folderPath As String) As Boolean
Dim attributes As Long
On Error Resume Next
attributes = GetAttr(folderPath)
FolderExists = (Err.Number = 0) And ((attributes And vbDirectory) = vbDirectory)
Err.Clear
On Error GoTo 0
End Function
Private Sub SafeDeleteTempFile(ByVal filePath As String)
If Len(filePath) = 0 Then Exit Sub
If InStr(1, filePath, "\.__formatpilot_", vbTextCompare) = 0 Then Exit Sub
If FileExists(filePath) Then Kill filePath
End Sub
Private Sub RaiseRowError(ByVal rowNumber As Long, _
ByVal fieldName As String, _
ByVal messageText As String)
Err.Raise vbObjectError + 2360, , _
"Row " & rowNumber & ", " & fieldName & ": " & messageText
End SubReferences: Word InlineShapes.AddPicture, Word Range.ParagraphFormat, and Word SaveAs2.
Know what this helper does not guarantee
The code is deliberately narrow. It makes one tested template contract repeatable; it is not a general Word layout repair engine.
No arbitrary mixed formatting
The helper rejects common mixed-format states. It does not preserve different formatting for individual characters inside one replacement value.
Main template contract only
Place the four bookmarks in the main document body or its tables. Headers, footers, text boxes, floating shapes, tracked changes, and protected documents require separate testing.
No concurrent writers
Use one workbook process and one output folder at a time. A second process can still create a race between a collision check and the final move.
No silent overwrite
The macro stops when a DOCX or PDF already exists. DOCX and PDF are moved into place sequentially, not as an atomic pair; if the second move fails, the first file may remain and must be reviewed before retrying.
The VBA is designed for Excel and Word Desktop on Windows and must be tested in the actual Office version, template, fonts, printers, and security environment used for production.
Run one six-record formatting pilot
Choose records that challenge the template rather than six convenient short values.
1. Normal record
Confirm the expected font, style, alignment, table position, DOCX, and PDF with ordinary data.
2. Long client name
Use a realistic maximum-length name and check wrapping, row height, nearby labels, and page breaks.
3. Leading-zero values
Verify that identifiers such as 000184 remain text from Excel through both output formats.
4. Blank optional logo
Leave LogoFile empty and confirm the placeholder disappears without changing spacing or surrounding content.
5. Wide or tall logo
Use an extreme aspect ratio and confirm the image is embedded, not distorted, and remains inside the tested box.
6. Existing output
Create a file with the intended name and confirm the macro stops without replacing it. Remove the test file only after review.
A DOCX can look correct while PDF pagination changes because of font availability or layout calculations. Approve both formats before marking the remaining rows READY.
When the template is stable, use the Factory workflow
DocxForge Pro Factory matches this batch shape: many Excel rows, one standard Word template, and one separate DOCX or PDF per row. The formatting contract still belongs in the Word template and must pass the pilot first.
Best fit
Repeated invoices, letters, certificates, reports, or similar files where every row uses the same approved template structure.
Source control
Prepare one clean Excel table with document-ready values, stable identifiers, explicit image file names, and a reviewed release status.
Template control
Keep placeholder boundaries, styles, table behavior, and image boxes consistent. A generator cannot compensate for an ambiguous Word template contract.
Where it does not fit
If one case must create several different documents from different Word templates, use Contractor. If arbitrary mixed formatting must be inferred, redesign the placeholders first.


Frequently asked questions
Does Range.Text preserve all Word formatting automatically?
No. Microsoft documents that Range.Text returns plain, unformatted text and replaces the existing range when set. A controlled macro can capture and reapply one uniform style and font, but a single bookmark containing mixed formatting should be split into smaller bookmarks.
Why must the bookmark exclude the paragraph mark?
Paragraph formatting is associated with the paragraph mark. Keeping that mark outside the replacement range reduces the chance that alignment, spacing, list behavior, or table-cell structure will be changed when the placeholder text is replaced.
Why does the macro add the bookmark again after writing text?
Replacing the text inside a Word bookmark can remove the bookmark itself. The helper adds the bookmark back around the new range and verifies that it exists before the document is saved.
Can one bookmark contain bold and regular words?
Do not use that pattern with this helper. Use one uniformly formatted placeholder per bookmark, or split the sentence into separate bookmarks so each replacement has one clear formatting contract.
How is a logo inserted without moving to the start of the document?
The macro passes the saved LogoImage bookmark range to InlineShapes.AddPicture. It embeds the file, fits it inside the placeholder image’s box or a documented default box, and restores the bookmark around the inserted image.
Where does DocxForge Pro Factory fit this workflow?
Factory is the direct fit when many Excel rows should create separate documents from one standard Word template. The template must still be designed and tested correctly; Factory does not turn a mixed-format placeholder into a safe replacement range automatically.
Prefer to test the free VBA approach first? Review the bookmark contract.