Fix: Excel Rows Generating Blank Word Documents
Blank Word files usually mean the template opened and saved, but the Excel row was empty, incomplete, or never reached the expected Word bookmarks. Validate visible row data, require the key fields, verify every bookmark, and save only after all replacements succeed.
Quick answer
A valid DOCX can still contain none of the expected Excel values. Check the row before opening Word, stop when a required field is blank, confirm that every bookmark exists, and open a fresh read-only copy of the master template for each record. Save only after all bookmark replacements finish without error.
A completely empty row should be skipped. A partly populated row with no RecordID or ClientName should stop the run, because silently saving it would create an untraceable or blank document.
Blank Word output: symptom → cause → fix
The visible result helps identify which part of the Excel-to-Word workflow failed.
Every generated document is blank
Cause: the macro opens and saves the template, but the replacement code targets bookmarks that do not exist or never runs against the intended document.
Fix: verify the template path, check each bookmark name with Bookmarks.Exists, and stop before saving when any required bookmark is missing.
Only some Excel rows produce blank files
Cause: formulas return empty strings, required cells are blank, or the loop decides that a partial row is valid.
Fix: skip rows only when all relevant cells are visually empty. Treat a missing RecordID or ClientName in a partly populated row as an error.
The first row works, later rows fail
Cause: one modified document is reused, or replacing bookmark text removes the bookmark before the next record.
Fix: open the original template for every row and add each bookmark back to its replacement range after writing the value.
Dates, IDs, or amounts are missing
Cause: raw Excel values do not match the visible format, or a narrow column returns hashes through Range.Text.
Fix: read one cell at a time, reject Excel errors, and widen numeric or date columns when the displayed text contains #.
A document is created under the wrong name
Cause: the filename comes from an optional value, invalid Windows characters are not removed, or a previous output already uses the same name.
Fix: build the filename from a required stable ID, sanitize it, and refuse to overwrite an existing file.
Word stays open after the batch stops
Cause: the error handler starts too late or cleanup quits Word without explicitly closing the current document.
Fix: enable the handler before workbook and Word setup, then close the active document without saving in one shared cleanup block.
Define which Excel rows are allowed to generate documents
The example below uses a sheet named Data and makes the validation rules explicit.
A — RecordID
Required. Use a stable identifier that stays unique across reruns. It becomes the first part of the output filename.
B — ClientName
Required. It fills the ClientName Word bookmark and becomes the readable part of the filename.
C — DocumentDate
Optional. Its displayed Excel text fills the DocumentDate bookmark, so the Word output matches the visible date format.
D — Amount
Optional. Its displayed text fills the Amount bookmark, including the visible currency or number format.
In Word, each bookmark should enclose a short placeholder using one consistent style. For broader template preparation, see how to fill Word templates from Excel automatically and how to preserve template formatting during replacement.
A reliable row-to-document workflow
Keep validation, replacement, and saving in a fixed order so a failed row cannot quietly become a finished-looking blank file.
1. Find the real data range
Check all four relevant columns rather than relying on one optional field. Formula rows that display nothing can then be skipped safely.
2. Validate required visible values
Read the displayed text, reject Excel errors and truncated numeric output, and stop when a partly populated row has no stable ID or client name.
3. Open a fresh template
Use the same read-only master DOCX for every record. Never use the previous generated document as the next row's source.
4. Replace, verify, then save
Confirm every required bookmark exists, write the values, restore the bookmarks, check the output path, and only then call SaveAs2.
Test the blank-document guards before a full batch
Use a small disposable copy of the workbook and template. Each test should either create a populated file or stop with a specific message.
Normal populated row
Confirm that all three bookmarks receive the expected visible text and the filename contains the correct RecordID.
Completely empty row
Leave A:D visually empty. The row should be skipped and should not create a document.
Partial row with a missing required field
Leave RecordID or ClientName blank while another cell contains data. The batch should stop instead of producing an anonymous file.
Missing Word bookmark
Rename one bookmark in a copy of the template. The batch should identify that bookmark and save no document for the row.
Narrow date or amount column
Reduce the column width until Excel shows hashes. The macro should ask you to widen the column instead of inserting hashes into Word.
Repeat the same run
Run the sample twice. The second run should stop at the existing output rather than overwrite it. For more naming tests, use the duplicate filename guide.
Free Excel VBA: stop blank Word documents before saving
This example opens one clean template per visible row, requires RecordID and ClientName, checks all Word bookmarks, restores and verifies each bookmark after replacement, blocks overwrites, and closes Word cleanly after an error.
Save the workbook, create the Data sheet with columns A:D described above, place MasterTemplate.docx beside the workbook, and add the Word bookmarks ClientName, DocumentDate, and Amount.
Option Explicit
Sub GenerateVerifiedDocsFromRows()
Const wdFormatDocumentDefault As Long = 16
Const FIRST_DATA_ROW As Long = 2
Dim ws As Worksheet
Dim wdApp As Object
Dim doc As Object
Dim lastRow As Long
Dim currentRow As Long
Dim processedCount As Long
Dim templatePath As String
Dim outputFolder As String
Dim outputPath As String
Dim recordId As String
Dim clientName As String
Dim dateText As String
Dim amountText As String
Dim outputStem As String
Dim currentStep As String
On Error GoTo CleanFail
currentStep = "checking the workbook setup"
If Len(ThisWorkbook.Path) = 0 Then
Err.Raise vbObjectError + 1100, , _
"Save the Excel workbook before running the macro."
End If
Set ws = ThisWorkbook.Worksheets("Data")
templatePath = ThisWorkbook.Path & "\MasterTemplate.docx"
outputFolder = ThisWorkbook.Path & "\Generated"
If Dir(templatePath) = "" Then
Err.Raise vbObjectError + 1101, , _
"MasterTemplate.docx was not found next to the workbook."
End If
If Dir(outputFolder, vbDirectory) = "" Then MkDir outputFolder
lastRow = LastUsedRowAcross(ws, 1, 4)
If lastRow < FIRST_DATA_ROW Then
Err.Raise vbObjectError + 1102, , _
"No data rows were found on the Data sheet."
End If
currentStep = "starting Microsoft Word"
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = False
wdApp.ScreenUpdating = False
wdApp.DisplayAlerts = 0
For currentRow = FIRST_DATA_ROW To lastRow
If RowHasVisibleData(ws, currentRow, 1, 4) Then
currentStep = "validating Excel row " & currentRow
recordId = Trim$(CellDisplayText(ws.Cells(currentRow, 1), "Record ID"))
clientName = Trim$(CellDisplayText(ws.Cells(currentRow, 2), "Client name"))
dateText = CellDisplayText(ws.Cells(currentRow, 3), "Document date")
amountText = CellDisplayText(ws.Cells(currentRow, 4), "Amount")
If Len(Trim$(recordId)) = 0 Then
Err.Raise vbObjectError + 1103, , _
"Record ID is blank on row " & currentRow & "."
End If
If Len(Trim$(clientName)) = 0 Then
Err.Raise vbObjectError + 1104, , _
"Client name is blank on row " & currentRow & "."
End If
outputStem = SafeFileName(recordId & "_" & clientName)
If Len(outputStem) = 0 Then
Err.Raise vbObjectError + 1105, , _
"The output name is empty after cleanup on row " & currentRow & "."
End If
outputPath = outputFolder & "\" & outputStem & ".docx"
If Dir(outputPath) <> "" Then
Err.Raise vbObjectError + 1106, , _
"Output already exists for row " & currentRow & ": " & outputStem & ".docx"
End If
currentStep = "opening the template for Excel row " & currentRow
Set doc = wdApp.Documents.Open( _
FileName:=templatePath, _
ReadOnly:=True, _
AddToRecentFiles:=False)
currentStep = "writing Word bookmarks for Excel row " & currentRow
WriteRequiredBookmark doc, "ClientName", clientName, False
WriteRequiredBookmark doc, "DocumentDate", dateText, True
WriteRequiredBookmark doc, "Amount", amountText, True
currentStep = "saving the document for Excel row " & currentRow
doc.SaveAs2 _
FileName:=outputPath, _
FileFormat:=wdFormatDocumentDefault, _
AddToRecentFiles:=False
doc.Close SaveChanges:=False
Set doc = Nothing
processedCount = processedCount + 1
End If
Next currentRow
If processedCount = 0 Then
Err.Raise vbObjectError + 1107, , _
"No rows with visible data were found in columns A:D."
End If
MsgBox processedCount & " document(s) created in:" & vbCrLf & _
outputFolder, vbInformation
CleanExit:
On Error Resume Next
If Not doc Is Nothing Then doc.Close SaveChanges:=False
If Not wdApp Is Nothing Then wdApp.Quit
Set doc = Nothing
Set wdApp = Nothing
Exit Sub
CleanFail:
MsgBox "Generation stopped while " & currentStep & ":" & vbCrLf & _
Err.Description, vbExclamation
Resume CleanExit
End Sub
Private Function LastUsedRowAcross( _
ByVal ws As Worksheet, _
ByVal firstColumn As Long, _
ByVal lastColumn As Long) As Long
Dim columnNumber As Long
Dim candidateRow As Long
LastUsedRowAcross = 1
For columnNumber = firstColumn To lastColumn
candidateRow = ws.Cells(ws.Rows.Count, columnNumber).End(xlUp).Row
If candidateRow > LastUsedRowAcross Then
LastUsedRowAcross = candidateRow
End If
Next columnNumber
End Function
Private Function RowHasVisibleData( _
ByVal ws As Worksheet, _
ByVal rowNumber As Long, _
ByVal firstColumn As Long, _
ByVal lastColumn As Long) As Boolean
Dim columnNumber As Long
Dim cell As Range
For columnNumber = firstColumn To lastColumn
Set cell = ws.Cells(rowNumber, columnNumber)
If IsError(cell.Value) Then
RowHasVisibleData = True
Exit Function
End If
If Len(Trim$(CStr(cell.Text))) > 0 Then
RowHasVisibleData = True
Exit Function
End If
Next columnNumber
End Function
Private Function CellDisplayText( _
ByVal cell As Range, _
ByVal fieldLabel As String) As String
If IsError(cell.Value) Then
Err.Raise vbObjectError + 1110, , _
fieldLabel & " contains an Excel error in " & _
cell.Address(False, False) & "."
End If
CellDisplayText = CStr(cell.Text)
If (IsNumeric(cell.Value2) Or IsDate(cell.Value)) And _
InStr(1, CellDisplayText, "#", vbBinaryCompare) > 0 Then
Err.Raise vbObjectError + 1111, , _
fieldLabel & " is not fully visible in " & _
cell.Address(False, False) & _
". Widen the column or use a dedicated text column."
End If
End Function
Private Sub WriteRequiredBookmark( _
ByVal doc As Object, _
ByVal bookmarkName As String, _
ByVal replacementText As String, _
ByVal allowBlank As Boolean)
Dim targetRange As Object
Dim bookmarkStyle As Variant
Dim writtenText As String
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 1120, , _
"Required Word bookmark was not found: " & bookmarkName
End If
If Not allowBlank And Len(Trim$(replacementText)) = 0 Then
Err.Raise vbObjectError + 1121, , _
"Required value is blank for Word bookmark: " & bookmarkName
End If
Set targetRange = doc.Bookmarks(bookmarkName).Range
bookmarkStyle = targetRange.Style
targetRange.Text = replacementText
targetRange.Style = bookmarkStyle
doc.Bookmarks.Add Name:=bookmarkName, Range:=targetRange
If Not doc.Bookmarks.Exists(bookmarkName) Then
Err.Raise vbObjectError + 1122, , _
"Word bookmark was not restored: " & bookmarkName
End If
If Not allowBlank Then
writtenText = doc.Bookmarks(bookmarkName).Range.Text
writtenText = Replace(writtenText, vbCr, "")
writtenText = Replace(writtenText, vbLf, "")
writtenText = Replace(writtenText, Chr$(7), "")
If Len(Trim$(writtenText)) = 0 Then
Err.Raise vbObjectError + 1123, , _
"Required Word bookmark is empty after replacement: " & bookmarkName
End If
End If
End Sub
Private Function SafeFileName(ByVal value As String) As String
Dim invalidChars As Variant
Dim reservedNames As Variant
Dim item As Variant
Dim charCode As Long
Dim dotPosition As Long
Dim upperStem As String
invalidChars = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
SafeFileName = Trim$(value)
For Each item In invalidChars
SafeFileName = Replace(SafeFileName, CStr(item), "_")
Next item
For charCode = 0 To 31
SafeFileName = Replace(SafeFileName, Chr$(charCode), "")
Next charCode
Do While Len(SafeFileName) > 0 And _
(Right$(SafeFileName, 1) = "." Or Right$(SafeFileName, 1) = " ")
SafeFileName = Left$(SafeFileName, Len(SafeFileName) - 1)
Loop
reservedNames = Array("CON", "PRN", "AUX", "NUL", _
"COM1", "COM2", "COM3", "COM4", "COM5", "COM6", "COM7", "COM8", "COM9", _
"LPT1", "LPT2", "LPT3", "LPT4", "LPT5", "LPT6", "LPT7", "LPT8", "LPT9")
upperStem = UCase$(SafeFileName)
dotPosition = InStr(1, upperStem, ".", vbBinaryCompare)
If dotPosition > 1 Then upperStem = Left$(upperStem, dotPosition - 1)
For Each item In reservedNames
If upperStem = CStr(item) Then
SafeFileName = "_" & SafeFileName
Exit For
End If
Next item
If Len(SafeFileName) > 120 Then
SafeFileName = Left$(SafeFileName, 120)
End If
Do While Len(SafeFileName) > 0 And _
(Right$(SafeFileName, 1) = "." Or Right$(SafeFileName, 1) = " ")
SafeFileName = Left$(SafeFileName, Len(SafeFileName) - 1)
Loop
End FunctionWord's Range.Text replaces the existing range as plain text. The helper captures the bookmark range and style, inserts the value, reapplies the style, adds the bookmark back, and confirms that a required bookmark still contains visible text. Keep the placeholder in one consistent style and exclude paragraph marks from the bookmark.
Microsoft documents Excel Range.Text as the formatted text displayed in one cell and notes that narrow columns can change that text. Word's Range.Text replaces the existing range. The Bookmarks.Exists and Bookmarks.Add methods provide the explicit check-and-restore pattern used here.
Where this VBA fix still has limits
The guards make a small bookmark workflow safer. They do not turn it into a full production system.
It stops at the first invalid row
Stopping protects the current row, but files created for earlier rows remain in the output folder. A production batch may need a structured log and a reviewed retry process for several failed records.
Optional blanks still need template rules
An empty date or amount can leave awkward punctuation or spacing. Design the surrounding Word text so an optional value can disappear cleanly.
Bookmarks are one template technique
Tags, content controls, headers, images, repeating blocks, and conditional sections require different replacement logic and separate tests.
DOCX creation is not PDF verification
If the workflow also exports PDF, verify that step and its pagination separately. A populated Word file does not prove a correct PDF.
If replacements succeed but the layout breaks, continue with the Word formatting troubleshooting guide.
Free VBA, Factory, or Contractor?
Blank output can occur in a row-based batch or a reviewed case workflow. Choose by the shape of the input and the documents you need to produce.
Use free VBA for one stable template
The macro fits a controlled A:D workbook, one Word template, a modest batch, and an owner who can maintain the code when fields change.
Use Factory for repeated Excel batches
Factory is the row-based module: many selected Excel rows produce separate documents from one Word template, with naming and output settings handled in the workflow.
Use Contractor for reviewed case work
Contractor is the case-based module: review or correct one case dataset, then generate several different documents for that case from different Word templates.
Keep the same safeguards in every workflow
Check required fields, empty formula results, bookmark or tag names, long values, and a repeat run. Keep every master DOCX unchanged.
Contractor: reviewed case workflow
Review one case's fields before generating several documents from different Word templates.
Factory: Excel batch workflow
Review many Excel rows, then generate separate documents from one selected Word template.
Frequently asked questions
Short answers to common Excel-row and blank-Word-output problems.
Why does Word save a blank document without showing an error?
Opening and saving the template can succeed even when no Excel value reaches the expected bookmarks. Check required row values and bookmark existence before calling SaveAs2.
Should a completely empty Excel row stop the batch?
No. A visually empty row can be skipped. A partly populated row with no required RecordID or ClientName should stop the batch so it cannot create an untraceable file.
Why use Excel .Text instead of .Value2?
.Text returns the visible one-cell display, which preserves leading zeros and visible date, currency, or number formats. The column must be wide enough, so the macro rejects hash-filled output.
Why does the VBA add each Word bookmark back?
Replacing a bookmark range's text can remove the bookmark marker. Adding it back keeps the generated document structurally consistent and makes later checks or edits possible.
Does this macro overwrite an existing document?
No. It stops when the target DOCX already exists. Decide deliberately whether a production workflow should block, version, or archive previous output.
When should I use Factory or Contractor instead of the VBA example?
Use Factory when many Excel rows repeatedly generate separate documents from one Word template. Use Contractor when one reviewed case must produce several different documents from different templates.