Word Templates • Formatting-Safe Replacement

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.

Uniform bookmarks
Style restored
Image range preserved
Six-record pilot
A replacement routine can preserve a defined, uniform format. It cannot reliably reconstruct arbitrary mixed formatting hidden inside one placeholder.

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.

Do not promise universal fidelity

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.

Template layout used by the helper

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.

Example row

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.

Excel VBA — formatting-safe Word generationWord Desktop required
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 Sub

References: 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.

Static example, not a desktop execution claim

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.

Compare Word and PDF

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.

DocxForge Pro Factory Excel data screen
Factory maps many Excel rows to one standard Word template.
DocxForge Pro Factory documents screen
Review the generated files only after the template passes the six-record pilot.
One approved template. A separate document for every Excel row.Try DocxForge Pro Factory for 7 days free. Then $38 every 3 months • 14-day refund after purchase.
Factory batch workflowExcel → WordLocal processingOptional PDF
Start Free 7-Day Trial

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.

Keep the formatting you approved. Generate the rest with Factory.Test six records first, then produce separate DOCX files and optional PDFs from one Word template. 7 days free, then $38 every 3 months • 14-day refund after purchase.
One Word templateMany Excel rowsControlled filenamesDOCX + optional PDF
Start Free 7-Day Trial

Prefer to test the free VBA approach first? Review the bookmark contract.