How to Use Excel Columns to Control Output Names and File Structure
Use Excel columns to choose each document's filename and destination subfolder. This free Excel VBA example creates a safe folder structure and matching DOCX/PDF files, without manually renaming output.
Quick answer
Put the destination group in one Excel column, the document type in another, and an optional filename stem in a third. One macro can then create Output/Group/DocType/DOCX and Output/Group/DocType/PDF, fill a Word template, and save matching files. Check both paths before saving to avoid overwriting earlier sequential runs.
For OutputGroup = West, DocType = Contract, and FileStem = ACME-1042, the macro creates Output/West/Contract/DOCX/ACME-1042.docx and Output/West/Contract/PDF/ACME-1042.pdf.
Why control output folders from Excel?
A naming convention answers what a file is called; a column-driven workflow also decides where each output belongs. That distinction matters when documents must be separated by team, location, project, or type.
Move records without editing VBA
Change the OutputGroup cell from West to East; the next document goes into that group’s folder without changing the macro.
Use one template across groups
A single Word template can serve many departments or projects while the spreadsheet provides different names, text, and output locations.
Keep editable and PDF copies aligned
Each DOCX/PDF pair uses the same filename stem, even though it is placed in a separate format folder.
Make the structure auditable
Predictable group and document-type folders make it easier to review, copy, archive, or pass completed document sets to a later backup or reporting workflow.
Common mistakes with spreadsheet-driven paths
The risky part is not reading a column; it is trusting arbitrary text as a Windows folder name or writing output before checking whether a name already exists.
A full path is copied directly from a cell
Untrusted or mistyped paths such as ../, slashes, or drive prefixes can send output to the wrong location. Use one safe folder segment per column instead.
Names are built from clients alone
Two people or projects may share names. A separate FileStem field or a stable record ID is easier to control.
PDF export uses a different base name
A changed DOCX filename paired with an old PDF filename leaves mismatched results.
A rerun replaces an earlier file
Check existing DOCX and PDF names together, then choose _2, _3, etc. for a later version.
For naming rules in general, see how to organize batch filenames. If output is already being overwritten, see the duplicate-name fix.
Build a column-controlled output workflow
Use a Data sheet with headers in row 1. Each filled row is one document. Column letters are fixed in this example, so the worksheet and code stay in sync.
Switching from the earlier example? Its VBA used column A to detect the last record, B for client name, C for date, D for document type and E for an optional image path. This updated example uses A = RecordID, B = ClientName, C = OutputGroup, D = DocType, E = DocumentDate and F = FileStem. Rearrange the columns before running this macro. To group output by client as in the original example, copy an appropriate client or account identifier into OutputGroup.
A — RecordID
A stable ID, such as C-1042. Blank values use Row_4 as a fallback.
B — ClientName
Text inserted into {{ClientName}} in the Word body; avoid putting private names into public folder paths.
C — OutputGroup
One destination folder, for example West or HR. Unsafe path characters are replaced.
D — DocType
One nested folder, such as Contract or Letter; also fills a Word placeholder.
E — DocumentDate
A real Excel date. The macro writes an ISO-format date into Word and uses YYYYMMDD for the fallback filename.
F — FileStem (optional)
An explicit base filename such as ACME-1042. When blank, the macro uses RecordID_YYYYMMDD_DocType.
The spreadsheet selects both the group folder and document-type folder. The code then creates these two format folders underneath:
BatchData.xlsm
Template.docx
Output/
West/
Contract/
DOCX/ACME-1042.docx
DOCX/ACME-1042_2.docx
PDF/ACME-1042.pdf
PDF/ACME-1042_2.pdf
East/
Letter/
DOCX/E-305_20261116_Letter.docx
PDF/E-305_20261116_Letter.pdfThe _2 pair represents a later record or run with an already occupied filename. This is a single-computer sequential example, not an atomic file reservation service.
Test the Excel column rules before a large batch
Create three test records, confirm their dates are stored as actual Excel dates, and inspect both folders and document contents. Start with a copy of your data.
Change the destination group
For the same FileStem, change OutputGroup from West to East. The documents should be placed in a different group folder.
Repeat a filename in the same folder
Use ACME-1042 twice with the same group and document type. Expect ACME-1042 and ACME-1042_2 for both file formats.
Use a blank custom filename
Leave FileStem empty and enter a record ID, document date and type. Expect a name such as C-1042_20261115_Contract.
Try an unsafe folder value
Set OutputGroup to West/Finance. The slash should be replaced inside a single safe folder name, rather than creating an extra folder level.
Free Excel VBA: folder and filename routing by column
This complete VBA example reads all six columns, creates controlled relative subfolders under the saved workbook, fills the Word template, and checks both DOCX and PDF destinations before saving. Unlike the earlier naming-convention guide, editing the OutputGroup and FileStem values changes the routing without changing code.
Save the workbook as BatchData.xlsm and the Word template as Template.docx in the same folder. Create a Data sheet with columns A–F exactly as listed above. Put {{RecordID}}, {{ClientName}}, {{DocType}}, and {{DocumentDate}} as plain-text placeholders in the main body of the Word template. Paste the macro below into a standard Excel VBA module and run GenerateDocsFromExcelColumns. Excel and Word Desktop on Windows are required.
Option Explicit
' Excel (.xlsm) macro: one nonblank Data row -> one DOCX/PDF pair.
' Keep Template.docx next to this workbook.
' Data columns: A RecordID, B ClientName, C OutputGroup,
' D DocType, E DocumentDate (real Excel date), F FileStem (optional).
' Word body tokens: {{RecordID}}, {{ClientName}}, {{DocType}},
' {{DocumentDate}}. The macro fills body text, not headers/shapes.
Public Sub GenerateDocsFromExcelColumns()
Const MAKE_PDF As Boolean = True
Dim ws As Worksheet
Dim appWord As Object, doc As Object
Dim root As String, templatePath As String, groupDir As String
Dim docDir As String, pdfDir As String, folderName As String
Dim recordID As String, groupName As String, docType As String
Dim fileStem As String, chosen As String, datePart As String
Dim docPath As String, pdfPath As String
Dim lastRow As Long, r As Long, countDone As Long, n As Long
Dim col As Long, lastColRow As Long, errText As String
On Error GoTo Failed
If Len(ThisWorkbook.Path) = 0 Then
Err.Raise vbObjectError + 201, , "Save the XLSM workbook first."
End If
Set ws = ThisWorkbook.Worksheets("Data")
root = ThisWorkbook.Path & "\Output"
templatePath = ThisWorkbook.Path & "\Template.docx"
If Len(Dir$(templatePath)) = 0 Then
Err.Raise vbObjectError + 202, , "Missing Template.docx next to workbook."
End If
EnsureFolder root
lastRow = 1
For col = 1 To 6
lastColRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
If lastColRow > lastRow Then lastRow = lastColRow
Next col
Set appWord = CreateObject("Word.Application")
appWord.Visible = False
appWord.DisplayAlerts = 0
For r = 2 To lastRow
If Application.WorksheetFunction.CountA(ws.Range("A" & r & ":F" & r)) > 0 Then
recordID = Trim$(CStr(ws.Cells(r, "A").Text))
If Len(recordID) = 0 Then recordID = "Row_" & CStr(r)
If Not IsNumeric(ws.Cells(r, "E").Value2) Or _
Not IsDate(ws.Cells(r, "E").Value) Then
Err.Raise vbObjectError + 203, , _
"DocumentDate must be a real Excel date (column E)."
End If
datePart = Format$(CDate(ws.Cells(r, "E").Value), "yyyymmdd")
groupName = SafePathPart(CStr(ws.Cells(r, "C").Text), "General")
docType = SafePathPart(CStr(ws.Cells(r, "D").Text), "Document")
' Columns C and D control a predictable, safe folder hierarchy.
groupDir = root & "\" & groupName
folderName = groupDir & "\" & docType
docDir = folderName & "\DOCX"
pdfDir = folderName & "\PDF"
EnsureFolder groupDir
EnsureFolder folderName
EnsureFolder docDir
If MAKE_PDF Then EnsureFolder pdfDir
' Column F overrides the default filename stem without changing VBA.
If Len(Trim$(CStr(ws.Cells(r, "F").Text))) > 0 Then
fileStem = SafePathPart(CStr(ws.Cells(r, "F").Text), "Record")
Else
fileStem = SafePathPart(recordID, "Record") & "_" & _
datePart & "_" & docType
End If
fileStem = Left$(fileStem, 100)
chosen = fileStem
n = 2
' Check BOTH extensions before writing; works for sequential runs.
Do
docPath = docDir & "\" & chosen & ".docx"
pdfPath = pdfDir & "\" & chosen & ".pdf"
If Len(Dir$(docPath)) = 0 Then
If Not MAKE_PDF Then Exit Do
If Len(Dir$(pdfPath)) = 0 Then Exit Do
End If
chosen = fileStem & "_" & CStr(n)
n = n + 1
Loop
Set doc = appWord.Documents.Open(FileName:=templatePath, _
ReadOnly:=True, _
AddToRecentFiles:=False)
FillBodyToken doc, "{{RecordID}}", recordID
FillBodyToken doc, "{{ClientName}}", _
CStr(ws.Cells(r, "B").Text)
FillBodyToken doc, "{{DocType}}", _
CStr(ws.Cells(r, "D").Text)
FillBodyToken doc, "{{DocumentDate}}", _
Format$(CDate(ws.Cells(r, "E").Value), "yyyy-mm-dd")
doc.SaveAs2 FileName:=docPath, FileFormat:=16, _
AddToRecentFiles:=False
If MAKE_PDF Then
doc.ExportAsFixedFormat OutputFileName:=pdfPath, _
ExportFormat:=17, OpenAfterExport:=False
End If
doc.Close SaveChanges:=False
Set doc = Nothing
countDone = countDone + 1
End If
Next r
MsgBox "Generated " & countDone & " document(s)." & vbCrLf & _
"Output: " & root, vbInformation
GoTo Cleanup
Failed:
errText = Err.Description
If r >= 2 Then errText = "Data row " & r & ": " & errText
MsgBox "Batch stopped after " & countDone & _
" complete row(s). " & errText & vbCrLf & _
"Check for a partial DOCX/PDF pair before rerunning.", _
vbExclamation
Cleanup:
On Error Resume Next
If Not doc Is Nothing Then doc.Close SaveChanges:=False
If Not appWord Is Nothing Then appWord.Quit SaveChanges:=False
Set doc = Nothing
Set appWord = Nothing
Set ws = Nothing
On Error GoTo 0
End Sub
Private Sub EnsureFolder(ByVal path As String)
If Len(Dir$(path, vbDirectory)) = 0 Then MkDir path
End Sub
Private Function SafePathPart(ByVal rawText As String, _
ByVal fallback As String) As String
Dim s As String, chars As Variant, item As Variant, i As Long
Dim stem As String
s = Trim$(rawText)
chars = Array("\", "/", ":", "*", "?", Chr$(34), "<", ">", "|")
For Each item In chars
s = Replace$(s, CStr(item), "_")
Next item
For i = 0 To 31
s = Replace$(s, Chr$(i), "_")
Next i
s = Left$(s, 70)
Do While Len(s) > 0
If Right$(s, 1) <> "." And Right$(s, 1) <> " " Then Exit Do
s = Left$(s, Len(s) - 1)
Loop
If Len(s) = 0 Then s = fallback
stem = UCase$(Split(s, ".")(0))
If stem = "CON" Or stem = "PRN" Or stem = "AUX" Or _
stem = "NUL" Or stem = "COM1" Or stem = "LPT1" Or _
stem = "COM2" Or stem = "LPT2" Or stem = "COM3" Or _
stem = "LPT3" Or stem = "COM4" Or stem = "LPT4" Or _
stem = "COM5" Or stem = "LPT5" Or stem = "COM6" Or _
stem = "LPT6" Or stem = "COM7" Or stem = "LPT7" Or _
stem = "COM8" Or stem = "LPT8" Or stem = "COM9" Or _
stem = "LPT9" Then s = "_" & s
SafePathPart = s
End Function
Private Sub FillBodyToken(ByVal doc As Object, ByVal token As String, _
ByVal newValue As String)
Dim scope As Object
Set scope = doc.Content
With scope.Find
.ClearFormatting
.Text = token
.MatchWildcards = False
.Forward = True
.Wrap = 0 ' Stop at the end of the document body.
End With
Do While scope.Find.Execute()
scope.Text = newValue
scope.Collapse 0 ' Continue after the inserted text.
Loop
End SubTo generate DOCX only, change MAKE_PDF = True to False. The example replaces text in the Word main body; headers, footers, shapes, and photos need separate handling. Run macros only from trusted local files.
Where the free VBA example still has limits
The code demonstrates a reproducible local workflow, not a general-purpose document-management or multi-user file-locking engine.
Partial output can remain after failure
If PDF export fails after DOCX has been saved, that DOCX may remain without its PDF. Check output folders after a failed run.
Simultaneous runs are not collision-proof
File existence checks are not atomic locks. Run sequentially or separate concurrent jobs into different root folders.
Excel dates and long paths require care
Column E must contain a real Excel date, not a text approximation. Other fields are read using Excel .Text to preserve displayed formatting, so widen columns that show ####. Very long workbook paths or deeply nested destinations may still fail on Windows.
Templates beyond body text need extensions
Images, table-cell edge cases, headers, footers, complex Word fields, and advanced logging require more VBA. Performance depends on the PC, Word template, image sizes, and PDF export.
What happened to the image option? The original draft read a photo path from column E and attempted to insert it at the photo_logo Word bookmark. This naming-focused macro deliberately does not insert photos: column E now stores DocumentDate. For optional media, add a separate PhotoPath column (for example G) and extend the macro with checks for both the picture file and the bookmark before inserting an embedded image. Do not assume that the old example's silent On Error Resume Next handled missing images correctly.
For more complex tag replacement, see filling Word templates from Excel.
When to use VBA and when to use Factory
The free macro above is useful when you need a specific per-row folder hierarchy. DocxForge Pro Factory is useful for repeatable Excel-to-Word/PDF production with desktop-controlled output naming, batch settings, template tags and photo handling. Do not assume that every custom folder expression in the macro is available as a Factory setting.
Free VBA: custom folder routing
The example reads OutputGroup and DocType from each row and constructs a custom nested folder structure.
Factory: configurable output names
Choose a filename source field and supported naming options in the desktop Settings screen rather than editing macro expressions.
Factory: repeatable batch processing
Import Excel data, preview template mapping and generate DOCX with optional PDF export on your Windows PC.
Choose what you actually need
Keep VBA for this custom nested-path requirement. Consider Factory for repeated document production where supported GUI settings replace ongoing script maintenance.


If the macro already meets your needs, keep using it. If you're producing documents regularly, explore DocxForge Pro Factory and the available plans. Contractor is included too, for reviewed case data and selected document sets.
Frequently asked questions
Answers about Excel-controlled paths, safe names and how the free macro differs from Factory.
Can Excel send different rows to different output folders?
Yes. In this example, OutputGroup and DocType select safe relative subfolders beneath the workbook’s Output folder.
Can I choose the filename from a separate Excel column?
Yes. Enter the desired base name in FileStem (column F). If it is blank, the macro builds a fallback from RecordID, DocumentDate and DocType.
Does the macro prevent duplicate DOCX/PDF filenames?
For sequential runs, it checks both target paths and appends _2, _3 and so on. Simultaneous writers are not protected by atomic locks.
Can Excel specify an absolute folder path such as C:\Archive?
Not in this safe example. It uses sanitized single folder segments from Excel, always beneath the workbook’s local Output directory.
Can I keep a photo or logo path from the original workbook?
Yes, but the column layout has changed. The updated macro uses E for DocumentDate and F for FileStem. Add a separate PhotoPath column, such as G, and implement checked Word image insertion. The VBA shown here does not insert photos.
Does DocxForge Pro Factory support this exact per-row nested folder routing?
The screenshot confirms configurable filename and batch settings, not this exact custom nested-folder expression. The free VBA demonstrates that specific routing; Factory supports desktop-based batch naming and Word/PDF generation.