How to Fill Word Templates from Excel Automatically
Turn structured Excel data into finished Word documents by defining one source contract, one reusable template contract, and one mapping between them. Test a selected row first, then scale the approved workflow to a batch or document pack.
Quick answer: connect fields, not visual cell positions
Excel should provide named fields; Word should provide named destinations. The automation maps each field to its destination, opens a fresh copy of the approved template, inserts the values, verifies required content, and saves new DOCX and PDF files without changing the source template.
One row, one standard document
Use one Excel table and one Word template. Each approved row supplies the text and optional images for one separate output file.
One case, several document types
Use one case record and several Word templates when the same case must create a contract, cover letter, checklist, certificate, or other different documents.
Define the data contract → define the template contract → map fields → test one row → test six difficult rows → approve DOCX and PDF → scale the run.
Choose the workflow shape before building the template
The same words—“Excel to Word”—can describe different operational jobs. Pick one primary shape so the source table, template set, naming rules, and review process agree.
Factory: many rows, one template
Each row is an independent record. One standard Word template creates a separate document for every approved row.
Contractor: one case, several templates
One case supplies shared data to several different Word templates, producing a coordinated pack of different documents.
Mail Merge or custom VBA
Useful when the workflow is narrow and maintained internally. Document the field contract, error handling, output naming, and desktop dependencies.
Stop before mixing the shapes
Do not describe a multi-template case pack as one mass generation, and do not treat independent rows as one case. The distinction controls validation and output structure.
Build a stable Excel source contract
A clean source table prevents most mapping failures before Word opens.
One header per field
Use unique row-1 headers such as DocumentID, ClientName, DocumentDateDisplay, TotalDisplay, LogoFile, and Status.
One record per row
Keep values for the same output record together. Avoid merged cells, decorative header rows, and blank separators inside the data table.
Document-ready display fields
Keep raw dates and numbers for calculations, then create explicit display columns containing final text when Word needs exact date, currency, percentage, or localized output. The pilot rejects numeric values in mapped headers ending with Display.
Identifiers stored as text
Format ID columns as Text before entry or import when leading zeros matter. A value already converted from 0042 to 42 cannot be recovered from formatting alone.
Image references controlled
Use approved file names or paths and validate that every required image exists. Do not let free-form paths silently select unrelated files.
Status is a release gate
Generate only rows explicitly marked READY. Draft, incomplete, and rejected records should remain outside the production run.
Build a reusable Word template contract
The template owns the layout. Excel supplies values; it should not be expected to rebuild Word styling for every record.
Use named destinations
Bookmarks, content controls, merge fields, or literal tags can work. Choose one convention and give every dynamic field an unambiguous name.
Keep fixed wording outside fields
Only the changing value belongs inside the destination. Labels, punctuation, and explanatory text should remain fixed in the template.
Keep text ranges structurally small
Do not include paragraph marks or table end-of-cell marks in a replacement bookmark. Use one uniform text format per destination.
Give images their own slot
Use a dedicated image bookmark around an inline placeholder with tested maximum dimensions. Floating shapes need separate handling. Define whether a blank image removes the placeholder or stops the run.
Test realistic maximum lengths
Long names and addresses can wrap, expand table rows, or move content to another page. Design the template with real limits, not sample abbreviations.
Version the approved template
Record which template release the workflow expects. A version value in Excel is useful, but it does not authenticate the actual DOCX file.
Microsoft references: Bookmarks.Exists and Bookmarks.Add.
Define the mapping in a separate FieldMap sheet
The VBA pilot below uses a small configuration table instead of hard-coded Excel column letters.
A — ExcelHeader
The exact header from the Data sheet, for example ClientName or LogoFile.
B — WordBookmark
The unique bookmark in Templates\StandardTemplate.docx. Use letters, numbers, and underscores; start with a letter.
C — FieldType
Use TEXT for text replacement or IMAGE for a file stored inside the workbook’s Assets folder.
D — Required
Use YES when a blank value must stop the pilot and NO when the destination may be cleared.
ClientName | ClientName | TEXT | YESDocumentDateDisplay | DocumentDate | TEXT | YESLogoFile | LogoImage | IMAGE | NO
The pilot changes only bookmarks explicitly listed in FieldMap. Any other bookmark, tag, field, or content control remains untouched and must be reviewed separately before release.
Free VBA pilot: fill one selected Excel row into Word
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Create Data and FieldMap sheets, then place Templates\StandardTemplate.docx, Assets\, and an empty GeneratedDocuments\ folder beside the workbook. Select one READY data row and run the macro from a standard Excel VBA module.
Configuration is validated
The helper checks both sheets, exact map headers, source headers, field types, required flags, bookmark names, and duplicate bookmark mappings.
Text and images are supported
Text destinations keep one uniform style and restore their bookmark. Single-line values containing tabs or breaks are rejected. Text and image bookmarks containing Word fields or content controls are rejected before replacement. Images are embedded at the mapped bookmark and fitted to a tested box.
Existing files are blocked
The pilot stops when the final DOCX or PDF already exists. It writes unique temporary files before promoting them to the final names.
Scope stays deliberately small
The macro processes one selected row so you can inspect the mapping, layout, images, naming, DOCX, and PDF before a batch repeats the result.
Option Explicit
Private Const DATA_SHEET As String = "Data"
Private Const MAP_SHEET As String = "FieldMap"
Private Const TEMPLATE_RELATIVE_PATH As String = "\Templates\StandardTemplate.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 FIRST_MAP_ROW As Long = 2
Private Const READY_STATUS As String = "READY"
Private Const HEADER_DOCUMENT_ID As String = "DocumentID"
Private Const HEADER_STATUS As String = "Status"
Private Const MAP_HEADER_EXCEL As String = "ExcelHeader"
Private Const MAP_HEADER_BOOKMARK As String = "WordBookmark"
Private Const MAP_HEADER_TYPE As String = "FieldType"
Private Const MAP_HEADER_REQUIRED As String = "Required"
Private Const FIELD_TYPE_TEXT As String = "TEXT"
Private Const FIELD_TYPE_IMAGE As String = "IMAGE"
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 MSO_TRUE As Long = -1
Private Const WORD_ALERTS_NONE As Long = 0
Public Sub FillSelectedRowIntoWordTemplate()
Dim wb As Workbook
Dim dataWs As Worksheet
Dim mapWs As Worksheet
Dim selectedRow As Long
Dim documentIdColumn As Long
Dim statusColumn As Long
Dim lastMapRow As Long
Dim mapRow As Long
Dim sourceColumn As Long
Dim documentId As String
Dim statusText As String
Dim excelHeader As String
Dim bookmarkName As String
Dim fieldType As String
Dim requiredText As String
Dim fieldValue As String
Dim isRequired As Boolean
Dim templatePath As String
Dim outputFolder As String
Dim assetFolder As String
Dim assetPath As String
Dim safeBaseName As String
Dim finalDocxPath As String
Dim finalPdfPath As String
Dim tempDocxPath As String
Dim tempPdfPath As String
Dim runToken As String
Dim seenBookmarks 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 + 2400, , _
"Save the workbook before running the pilot."
End If
If LCase$(Right$(wb.Name, 5)) <> ".xlsm" Then
Err.Raise vbObjectError + 2401, , _
"Save this workbook as an Excel Macro-Enabled Workbook (.xlsm)."
End If
Set dataWs = RequireWorksheet(wb, DATA_SHEET)
Set mapWs = RequireWorksheet(wb, MAP_SHEET)
If ActiveWorkbook.Name <> wb.Name Then
Err.Raise vbObjectError + 2402, , _
"Activate this macro workbook before selecting a pilot row."
End If
If ActiveSheet.Name <> dataWs.Name Then
Err.Raise vbObjectError + 2402, , _
"Select one data cell on the " & DATA_SHEET & " sheet before running the pilot."
End If
selectedRow = ActiveCell.Row
If selectedRow < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 2403, , _
"Select a data row below the header row."
End If
documentIdColumn = RequireHeaderColumn(dataWs, HEADER_DOCUMENT_ID)
statusColumn = RequireHeaderColumn(dataWs, HEADER_STATUS)
ValidateMapHeaders mapWs
documentId = RequireCellText(dataWs, selectedRow, documentIdColumn, HEADER_DOCUMENT_ID)
statusText = UCase$(Trim$(CStr(dataWs.Cells(selectedRow, statusColumn).Value2)))
If statusText <> READY_STATUS Then
RaiseRowError selectedRow, HEADER_STATUS, _
"The selected row must have Status = " & READY_STATUS & "."
End If
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"
safeBaseName = CleanFileName(documentId)
If Len(safeBaseName) = 0 Then
RaiseRowError selectedRow, HEADER_DOCUMENT_ID, _
"The ID does not contain a usable Windows file name."
End If
finalDocxPath = outputFolder & "\" & safeBaseName & ".docx"
finalPdfPath = outputFolder & "\" & safeBaseName & ".pdf"
If Len(finalDocxPath) > 218 Or Len(finalPdfPath) > 218 Then
RaiseRowError selectedRow, HEADER_DOCUMENT_ID, _
"The output path is too long for this example workflow."
End If
If FileExists(finalDocxPath) Or FileExists(finalPdfPath) Then
RaiseRowError selectedRow, HEADER_DOCUMENT_ID, _
"A DOCX or PDF output already exists. No file will be replaced."
End If
lastMapRow = LastUsedRowInColumns(mapWs, Array(1, 2, 3, 4))
If lastMapRow < FIRST_MAP_ROW Then
Err.Raise vbObjectError + 2404, , _
"No mappings were found on the " & MAP_SHEET & " sheet."
End If
Set seenBookmarks = CreateObject("Scripting.Dictionary")
seenBookmarks.CompareMode = vbTextCompare
For mapRow = FIRST_MAP_ROW To lastMapRow
ReadMapRow mapWs, mapRow, excelHeader, bookmarkName, fieldType, requiredText
sourceColumn = RequireHeaderColumn(dataWs, excelHeader)
isRequired = ParseRequiredFlag(requiredText, mapRow)
ValidateFieldType fieldType, mapRow
ValidateBookmarkName bookmarkName, mapRow
If seenBookmarks.Exists(bookmarkName) Then
RaiseMapError mapRow, MAP_HEADER_BOOKMARK, _
"The bookmark is already mapped on row " & seenBookmarks(bookmarkName) & "."
End If
seenBookmarks.Add bookmarkName, mapRow
fieldValue = ReadCellText(dataWs, selectedRow, sourceColumn, excelHeader)
If isRequired And Len(Trim$(fieldValue)) = 0 Then
RaiseRowError selectedRow, excelHeader, "A required mapped value is blank."
End If
If fieldType = FIELD_TYPE_IMAGE And Len(fieldValue) > 0 Then
assetPath = ResolveAssetFile(assetFolder, fieldValue, selectedRow, excelHeader)
RequireFile assetPath, "image for " & excelHeader
End If
Next mapRow
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)
For mapRow = FIRST_MAP_ROW To lastMapRow
ReadMapRow mapWs, mapRow, excelHeader, bookmarkName, fieldType, requiredText
sourceColumn = RequireHeaderColumn(dataWs, excelHeader)
isRequired = ParseRequiredFlag(requiredText, mapRow)
fieldValue = ReadCellText(dataWs, selectedRow, sourceColumn, excelHeader)
If fieldType = FIELD_TYPE_TEXT Then
WriteTextBookmark doc, bookmarkName, fieldValue, Not isRequired
Else
If Len(fieldValue) > 0 Then
assetPath = ResolveAssetFile(assetFolder, fieldValue, selectedRow, excelHeader)
Else
assetPath = vbNullString
End If
WritePictureBookmark doc, bookmarkName, assetPath, Not isRequired
End If
Next mapRow
runToken = Format$(Now, "yyyymmdd_hhnnss") & "_" & _
Format$(CLng(Timer * 1000), "00000000")
tempDocxPath = outputFolder & "\.__fillpilot_" & runToken & ".docx"
tempPdfPath = outputFolder & "\.__fillpilot_" & runToken & ".pdf"
If FileExists(tempDocxPath) Or FileExists(tempPdfPath) Then
Err.Raise vbObjectError + 2405, , _
"A temporary file name collision was detected. Run the pilot again."
End If
doc.SaveAs2 tempDocxPath, WD_FORMAT_XML_DOCUMENT, False, vbNullString, False
If Not FileExists(tempDocxPath) Then
Err.Raise vbObjectError + 2406, , _
"Word did not create the temporary DOCX file."
End If
doc.ExportAsFixedFormat tempPdfPath, WD_EXPORT_FORMAT_PDF
If Not FileExists(tempPdfPath) Then
Err.Raise vbObjectError + 2407, , _
"Word did not create the temporary PDF file."
End If
doc.Close WD_DO_NOT_SAVE_CHANGES
Set doc = Nothing
If FileExists(finalDocxPath) Or FileExists(finalPdfPath) Then
Err.Raise vbObjectError + 2408, , _
"An output appeared after preflight. No existing file was replaced."
End If
fso.MoveFile tempDocxPath, finalDocxPath
tempDocxPath = vbNullString
fso.MoveFile tempPdfPath, finalPdfPath
tempPdfPath = vbNullString
wdApp.Quit WD_DO_NOT_SAVE_CHANGES
Set wdApp = Nothing
MsgBox "Pilot complete for " & documentId & "." & vbCrLf & _
"Review both files before processing another row:" & vbCrLf & _
finalDocxPath & vbCrLf & finalPdfPath, _
vbInformation, "Word template filled"
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 "The selected-row pilot stopped." & vbCrLf & _
"Review the output folder for a partial result if promotion failed." & vbCrLf & _
"Error " & errorNumber & ": " & errorMessage, _
vbCritical, "Template filling stopped"
End Sub
Private Sub ValidateMapHeaders(ByVal ws As Worksheet)
If StrComp(Trim$(CStr(ws.Cells(1, "A").Value2)), _
MAP_HEADER_EXCEL, vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2410, , _
"FieldMap A1 must be " & MAP_HEADER_EXCEL & "."
End If
If StrComp(Trim$(CStr(ws.Cells(1, "B").Value2)), _
MAP_HEADER_BOOKMARK, vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2411, , _
"FieldMap B1 must be " & MAP_HEADER_BOOKMARK & "."
End If
If StrComp(Trim$(CStr(ws.Cells(1, "C").Value2)), _
MAP_HEADER_TYPE, vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2412, , _
"FieldMap C1 must be " & MAP_HEADER_TYPE & "."
End If
If StrComp(Trim$(CStr(ws.Cells(1, "D").Value2)), _
MAP_HEADER_REQUIRED, vbTextCompare) <> 0 Then
Err.Raise vbObjectError + 2413, , _
"FieldMap D1 must be " & MAP_HEADER_REQUIRED & "."
End If
End Sub
Private Sub ReadMapRow(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByRef excelHeader As String, _
ByRef bookmarkName As String, _
ByRef fieldType As String, _
ByRef requiredText As String)
excelHeader = ReadMapCell(ws, rowNumber, "A", MAP_HEADER_EXCEL)
bookmarkName = ReadMapCell(ws, rowNumber, "B", MAP_HEADER_BOOKMARK)
fieldType = UCase$(ReadMapCell(ws, rowNumber, "C", MAP_HEADER_TYPE))
requiredText = UCase$(ReadMapCell(ws, rowNumber, "D", MAP_HEADER_REQUIRED))
If Len(excelHeader) = 0 Then
RaiseMapError rowNumber, MAP_HEADER_EXCEL, "A source header is required."
End If
If Len(bookmarkName) = 0 Then
RaiseMapError rowNumber, MAP_HEADER_BOOKMARK, "A Word bookmark is required."
End If
End Sub
Private Function ReadMapCell(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnLetter As String, _
ByVal fieldName As String) As String
Dim cellValue As Variant
cellValue = ws.Cells(rowNumber, columnLetter).Value2
If IsError(cellValue) Then
RaiseMapError rowNumber, fieldName, "The mapping cell contains an Excel error."
End If
ReadMapCell = Trim$(CStr(cellValue))
End Function
Private Sub ValidateFieldType(ByVal fieldType As String, ByVal mapRow As Long)
If fieldType <> FIELD_TYPE_TEXT And fieldType <> FIELD_TYPE_IMAGE Then
RaiseMapError mapRow, MAP_HEADER_TYPE, _
"Use TEXT or IMAGE."
End If
End Sub
Private Function ParseRequiredFlag(ByVal requiredText As String, _
ByVal mapRow As Long) As Boolean
If requiredText = "YES" Then
ParseRequiredFlag = True
ElseIf requiredText = "NO" Then
ParseRequiredFlag = False
Else
RaiseMapError mapRow, MAP_HEADER_REQUIRED, _
"Use YES or NO."
End If
End Function
Private Sub ValidateBookmarkName(ByVal bookmarkName As String, _
ByVal mapRow As Long)
Dim position As Long
Dim characterText As String
If Len(bookmarkName) > 40 Then
RaiseMapError mapRow, MAP_HEADER_BOOKMARK, _
"Word bookmark names must be 40 characters or fewer."
End If
If Not Left$(bookmarkName, 1) Like "[A-Za-z]" Then
RaiseMapError mapRow, MAP_HEADER_BOOKMARK, _
"Start the bookmark name with a letter."
End If
For position = 1 To Len(bookmarkName)
characterText = Mid$(bookmarkName, position, 1)
If Not characterText Like "[A-Za-z0-9_]" Then
RaiseMapError mapRow, MAP_HEADER_BOOKMARK, _
"Use only letters, numbers, and underscores."
End If
Next position
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 doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2420, , _
"Required Word bookmark is missing: " & bookmarkName
End If
If Not allowBlank And Len(Trim$(replacementText)) = 0 Then
Err.Raise vbObjectError + 2421, , _
"A required replacement is blank: " & bookmarkName
End If
ValidateReplacementText replacementText, bookmarkName
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
ValidateTextRange targetRange, bookmarkName
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 + 2422, , _
"Bookmark was not restored: " & bookmarkName
End If
If doc.Bookmarks(bookmarkName).Range.Text <> replacementText Then
Err.Raise vbObjectError + 2423, , _
"Bookmark text did not verify: " & bookmarkName
End If
End Sub
Private Sub ValidateTextRange(ByVal targetRange As Object, _
ByVal bookmarkName As String)
If targetRange.Start = targetRange.End Then
Err.Raise vbObjectError + 2430, , _
"Text bookmark must contain a styled placeholder: " & bookmarkName
End If
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2431, , _
"Text bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Then
Err.Raise vbObjectError + 2433, , _
"Text bookmark contains a Word field: " & bookmarkName
End If
If targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2434, , _
"Text bookmark contains a content control: " & bookmarkName
End If
With targetRange.Font
If .Bold = WD_UNDEFINED Or .Italic = WD_UNDEFINED Or _
.Underline = WD_UNDEFINED Or .Size = WD_UNDEFINED Or _
.Color = WD_UNDEFINED Then
Err.Raise vbObjectError + 2432, , _
"Text bookmark uses mixed character formatting: " & bookmarkName
End If
End With
End Sub
Private Sub ValidateReplacementText(ByVal replacementText As String, _
ByVal bookmarkName As String)
If InStr(1, replacementText, vbTab, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbCr, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, vbLf, vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(11), vbBinaryCompare) > 0 Or _
InStr(1, replacementText, Chr$(12), vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 2435, , _
"This single-line text mapping contains a tab, line break, or page break: " & _
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 Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 2440, , _
"Required picture bookmark is missing: " & bookmarkName
End If
If Len(picturePath) = 0 And Not allowBlank Then
Err.Raise vbObjectError + 2441, , _
"A required picture is blank: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range.Duplicate
If RangeContainsParagraphOrCellMark(targetRange) Then
Err.Raise vbObjectError + 2442, , _
"Picture bookmark includes a paragraph or end-of-cell mark: " & bookmarkName
End If
If targetRange.Fields.Count > 0 Then
Err.Raise vbObjectError + 2445, , _
"Picture bookmark contains a Word field: " & bookmarkName
End If
If targetRange.ContentControls.Count > 0 Then
Err.Raise vbObjectError + 2446, , _
"Picture bookmark contains a content control: " & bookmarkName
End If
If targetRange.InlineShapes.Count > 1 Then
Err.Raise vbObjectError + 2443, , _
"Picture bookmark may contain one placeholder image at most: " & bookmarkName
End If
If targetRange.InlineShapes.Count = 1 Then
maxWidth = targetRange.InlineShapes(1).Width
maxHeight = targetRange.InlineShapes(1).Height
Else
maxWidth = 216!
maxHeight = 144!
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
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 + 2444, , _
"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 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 + 2450, , _
"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 + 2451, , _
"Required header missing from row 1: " & headerText
ElseIf matchCount > 1 Then
Err.Raise vbObjectError + 2452, , _
"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 ReadCellText(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
If IsDisplayHeader(headerText) Then
If Len(Trim$(CStr(cellValue))) > 0 And VarType(cellValue) <> vbString Then
RaiseRowError rowNumber, headerText, _
"Display fields must contain document-ready text, not a formatted number or date."
End If
End If
ReadCellText = CStr(cellValue)
End Function
Private Function IsDisplayHeader(ByVal headerText As String) As Boolean
If Len(headerText) < 7 Then Exit Function
IsDisplayHeader = _
(StrComp(Right$(headerText, 7), "Display", vbTextCompare) = 0)
End Function
Private Function RequireCellText(ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal columnNumber As Long, _
ByVal headerText As String) As String
RequireCellText = Trim$(ReadCellText(ws, rowNumber, columnNumber, headerText))
If Len(RequireCellText) = 0 Then
RaiseRowError rowNumber, headerText, "A value is required."
End If
End Function
Private Function ResolveAssetFile(ByVal assetFolder As String, _
ByVal fileNameOnly As String, _
ByVal rowNumber As Long, _
ByVal headerText As String) As String
Dim cleanedName As String
cleanedName = Trim$(fileNameOnly)
If InStr(cleanedName, "\") > 0 Or InStr(cleanedName, "/") > 0 Or _
InStr(cleanedName, ":") > 0 Or InStr(cleanedName, "*") > 0 Or _
InStr(cleanedName, "?") > 0 Or InStr(cleanedName, "<") > 0 Or _
InStr(cleanedName, ">") > 0 Or InStr(cleanedName, "|") > 0 Or _
InStr(cleanedName, Chr$(34)) > 0 Or cleanedName = "." Or cleanedName = ".." Then
RaiseRowError rowNumber, headerText, _
"Use a file name only. Store the image inside the Assets folder."
End If
ResolveAssetFile = assetFolder & "\" & cleanedName
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 + 2460, , _
"Missing " & labelText & ": " & filePath
End If
If FileLen(filePath) = 0 Then
Err.Raise vbObjectError + 2462, , _
"The " & labelText & " is empty: " & filePath
End If
End Sub
Private Sub RequireFolder(ByVal folderPath As String, ByVal labelText As String)
If Not FolderExists(folderPath) Then
Err.Raise vbObjectError + 2461, , _
"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, "\.__fillpilot_", 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 + 2470, , _
"Data row " & rowNumber & ", " & fieldName & ": " & messageText
End Sub
Private Sub RaiseMapError(ByVal rowNumber As Long, _
ByVal fieldName As String, _
ByVal messageText As String)
Err.Raise vbObjectError + 2471, , _
"FieldMap row " & rowNumber & ", " & fieldName & ": " & messageText
End SubMicrosoft references: Range.Text, InlineShapes.AddPicture, SaveAs2, and ExportAsFixedFormat.
Approve six representative records before the full run
A successful normal record proves very little. Choose values that exercise the weak points of the source and template.
1. Normal complete record
Confirm that every mapped field reaches the correct destination and both output formats open normally.
2. Longest realistic text
Test wrapping, table growth, page breaks, headers, footers, and content near the bottom of a page.
3. Leading-zero identifiers
Verify that IDs and invoice numbers remain exact in Excel, the file name, Word content, and PDF.
4. Blank optional field
Confirm that an optional text or image destination clears cleanly without leaving a token or breaking spacing.
5. Extreme image ratio
Use one wide and one tall image. Confirm that neither is distorted or allowed to overflow its intended slot.
6. Existing output collision
Place a file with the expected name in the output folder and confirm that the pilot stops without replacing it.
Review both DOCX and PDF on the actual Windows machine that will run production. Fonts, Office version, printer metrics, protected templates, and local security settings can change the result.
Choose the matching DocxForge Pro module
DocxForge Pro keeps generation local on the Windows computer. Choose the module from the document relationship, not from the number of fields in the spreadsheet.
Factory input
Many independent Excel rows, one standard Word template, and a release decision for each row.
Factory output
One separate DOCX or PDF per row, with repeatable naming and the same approved document structure.
Contractor input
One case containing shared facts, a defined document structure, and several different Word templates.
Contractor output
A coordinated pack of different documents for that case. It is not a multi-row run through one template.




Frequently asked questions
Can Excel fill a Word template automatically?
Yes. A reliable workflow gives each Excel field an explicit destination in a reusable Word template, validates required values, and creates a fresh output document instead of editing an old file manually.
Should one Excel row create one Word document?
That is the usual Factory pattern: many rows, one standard Word template, and one separate document per row. If one case must create several different document types from different templates, use the Contractor pattern instead.
How should dates, currency, and IDs be stored?
Keep calculation fields in their proper Excel types, but map document-ready display columns when Word needs exact text. Store identifiers as text before import when leading zeros must be preserved.
Can the workflow insert images from Excel?
Yes, when Excel provides a validated image file name or path and the template has a dedicated image destination. The VBA pilot accepts file names from a local Assets folder and embeds the selected image.
Why does the VBA example process only the selected row?
It is a controlled pilot. Reviewing one deliberately chosen row prevents a mapping or layout mistake from being repeated across a full batch. After six representative rows pass, use a production batch workflow.
Where do Factory and Contractor fit?
Factory handles many Excel rows through one standard Word template. Contractor handles one case that must produce several different documents from different Word templates. Do not combine their counts when they represent the same underlying work.