Excel VBA: Create a Word Document from a Template

To create a Word document from an Excel VBA macro on Windows, use Word’s automation object to open a template with Documents.Add, replace template placeholders with values from the workbook, and save the result as a new document. The example below uses late binding, so you do not need to add a Word reference in Excel’s VBA editor.

How Excel VBA creates a document from a template

A Word template is a starting file, usually saved as .dotx or .dotm. It can contain page layouts, styles, logos, tables, standard wording, headers, footers, and placeholders for information that changes. Excel VBA can ask Word to create a new document based on that template, then populate the new document with values from cells.

The key distinction is that the macro should create a document from the template rather than edit the template itself. In Word’s object model, Documents.Add creates a new document based on the specified template. The example saves that document as a regular .docx, leaving the original template available for future runs.

This approach is useful for repeatable outputs such as invoices, letters, certificates, reports, and order forms. Excel supplies or calculates the changing data; Word controls the document’s presentation. It is not the same as copying a worksheet into Word: the result is a Word document whose layout comes from the template.

Prepare the template and workbook

First, create and save a Word template in a known folder. In the template, type unique placeholder text exactly where each changing value should appear. For example, use «CustomerName», «InvoiceDate», and «InvoiceTotal». The angle quotation marks are simply readable markers; the macro searches for the exact characters you choose.

In Excel, put the corresponding values in a worksheet named Data. The sample macro below expects the customer name in B2, the invoice date in B3, and the total in B4. Change those references, worksheet name, template path, and output naming to suit your workbook. Save the Excel file as a macro-enabled workbook, .xlsm, so its VBA code is retained.

Keep each placeholder unique and spell it consistently in the template and code.
Put a placeholder in the document body, including a table cell, for the sample replacement routine.
Save the template and workbook before running the macro so their paths are available.
Use a copy of the workbook while developing or changing the macro.

Example VBA macro for Windows

In Excel, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the following code. It starts Word, creates a document from the template, replaces all occurrences of each placeholder in the main document body, and saves a new file beside the workbook. Because it uses late binding, the declarations are generic objects and no Word library reference is required.

Option Explicit

Sub CreateWordDocumentFromTemplate()
    Dim wordApp As Object
    Dim wordDoc As Object
    Dim ws As Worksheet
    Dim templatePath As String
    Dim outputPath As String
    Dim customerName As String
    Dim invoiceDate As String
    Dim invoiceTotal As String

    On Error GoTo Failed

    ' Save the workbook first so ThisWorkbook.Path is not empty.
    If Len(ThisWorkbook.Path) = 0 Then
        Err.Raise vbObjectError + 1000, , "Save the workbook before running this macro."
    End If

    Set ws = ThisWorkbook.Worksheets("Data")
    templatePath = ThisWorkbook.Path & Application.PathSeparator & "InvoiceTemplate.dotx"
    outputPath = ThisWorkbook.Path & Application.PathSeparator & _
                 "Invoice-" & Format$(Now, "yyyymmdd_hhnnss") & ".docx"

    If Len(Dir$(templatePath)) = 0 Then
        Err.Raise vbObjectError + 1001, , "Template not found: " & templatePath
    End If

    ' Read values from the example cell layout.
    customerName = CStr(ws.Range("B2").Value)
    invoiceDate = Format$(ws.Range("B3").Value, "mmmm d, yyyy")
    invoiceTotal = Format$(ws.Range("B4").Value, "$#,##0.00")

    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True
    Set wordDoc = wordApp.Documents.Add(Template:=templatePath, NewTemplate:=False)

    ReplaceToken wordDoc, "«CustomerName»", customerName
    ReplaceToken wordDoc, "«InvoiceDate»", invoiceDate
    ReplaceToken wordDoc, "«InvoiceTotal»", invoiceTotal

    ' 16 is Word's wdFormatDocumentDefault (.docx).
    wordDoc.SaveAs2 FileName:=outputPath, FileFormat:=16
    MsgBox "Created: " & outputPath, vbInformation
    Exit Sub

Failed:
    MsgBox "The Word document was not created: " & Err.Description, vbExclamation
    On Error Resume Next
    If Not wordDoc Is Nothing Then wordDoc.Close 0
    If Not wordApp Is Nothing Then wordApp.Quit
End Sub

Private Sub ReplaceToken(ByVal wordDoc As Object, _
                         ByVal token As String, _
                         ByVal replacement As String)
    Const wdReplaceAll As Long = 2
    Const wdFindContinue As Long = 1

    With wordDoc.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Text = token
        .Replacement.Text = replacement
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .Execute , , , , , , , , , , wdReplaceAll
    End With
End Sub

The example formats the date as a readable month-day-year string and the total with a dollar sign and two decimal places. Adjust the date format and currency symbol to match the document; the dollar format is only an example, not a currency conversion. The output filename includes a timestamp to reduce the chance of overwriting an earlier result. If you prefer a fixed filename, check whether it already exists before saving.

Run the macro and check the output

With the workbook saved, return to Excel and run CreateWordDocumentFromTemplate from Developer > Macros, or use Alt+F8 on Windows to open the Macro dialog. The Developer tab may need to be enabled through File > Options > Customize Ribbon. The macro opens Word visibly so you can review the generated document, and it also displays the saved file path.

Alt+F11 is the Windows shortcut for opening the VBA editor. On a Mac, Option+F11 or Fn+Option+F11 may open the editor depending on the keyboard settings, but the Windows automation code here will not run as written. Excel for the web does not run VBA macros. Save and open the workbook in desktop Excel for Windows to use this particular example.

Before distributing the output, inspect more than the replaced text. Check page breaks, table widths, dates, currency formatting, blank values, and whether the generated filename and destination are appropriate. A successful save only confirms that Word wrote a file; it does not verify that the document’s contents are correct.

What the replacement code does—and its limits

The helper searches the document’s main content and replaces every occurrence of a token. That includes ordinary paragraphs and tables in the document body, but it does not automatically search headers, footers, text boxes, or every other Word story range. If placeholders are in those areas, add code to search the relevant Word ranges or use content controls or bookmarks designed for the document structure.

Keep each placeholder as uninterrupted text. If it is split by a line break or other structure, a simple text search may not find it. Also use distinctive tokens that are unlikely to appear in ordinary prose. The sample uses Word’s replace-all operation; replacement text containing Word find-and-replace control sequences may be interpreted specially. For arbitrary long text, complex formatting, or data that may include such sequences, use bookmarks or content controls and assign their range text instead.

Replacing a token changes text, not document logic. It will not automatically refresh a table of contents, recalculate Word fields, resize a logo, or create a different number of table rows. Those requirements need additional Word automation code and should be handled as separate steps.

Common errors and practical fixes

Template not found: Confirm that InvoiceTemplate.dotx is in the same folder as the saved workbook, or change templatePath to the full correct path. Check the extension as well as the filename.
Subscript out of range: The workbook does not have a worksheet named Data. Update the worksheet name in the code or rename the sheet.
Placeholder remains unchanged: Compare the template text and token character by character. Check that the placeholder is in the main body and has not been divided by a line break or inserted as an image.
Word does not start: This code requires the desktop version of Word to be installed on Windows. Excel for the web and a computer without Word cannot provide the Word automation object used here.
Save error: Check that the destination folder is writable and that a file with the same name is not locked. The timestamp makes name collisions less likely but cannot resolve folder permissions.

For debugging, run the macro from the VBA editor with F8 to step through it, and inspect the values of templatePath and outputPath. If Word is left open after an interrupted run, close any unwanted Word process or document before trying again. The example closes Word on its error path, but if you change its cleanup code, avoid closing a Word instance that your workflow still needs.

Macro security and safe use

VBA can create files and control desktop applications, so only enable macros in workbooks from sources you trust. Do not weaken Excel’s macro security globally just to run this example. If an organization manages Office security, follow its approved process for signed macros or trusted files. A workbook saved as .xlsx does not retain VBA code; use .xlsm for a workbook containing this macro.

Save a copy of the workbook before running or modifying a macro. VBA actions cannot be undone with Ctrl+Z, and a macro can overwrite or create files without the usual editing history. This sample creates a separate timestamped Word file and does not intentionally change worksheet values, but reviewing the paths and output before running remains sensible.

When to use mail merge or another approach

For a single document or a custom workflow that prepares a few Word files from Excel, VBA offers control over paths, formatting, validation, and follow-up actions. For a standard letter or label sent to many spreadsheet rows, Word’s mail merge may be easier to maintain. In Word for Microsoft 365 on Windows, the mail merge tools are on the Mailings tab; Excel can serve as the recipient list. Mail merge is designed for producing multiple personalized documents without writing a separate automation routine.

If the output is primarily a report with calculations or charts, consider whether an Excel workbook or PDF export is more suitable than Word. If users need a button-driven process with little or no desktop code, an approved Microsoft 365 workflow tool may be an option, depending on the organization’s licensing and policies. The right choice depends on output layout, document volume, and how much control the process needs—not simply on whether Excel can launch Word.

Frequently Asked Questions

Can Excel VBA create a Word document without opening Word?

Word still needs to be installed for this Windows COM automation method. The macro can set wordApp.Visible = False to run Word hidden, but Word is still being launched in the background and errors may be less visible. The sample leaves it visible so the result is easier to inspect.

Do I need to add a Word reference in the VBA editor?

No. The example uses late binding with As Object and CreateObject("Word.Application"), so it does not require selecting a Word Object Library reference. Early binding is another option, but then the project depends on an available Word library reference.

Can the macro replace placeholders in a Word header or footer?

Not with the sample’s wordDoc.Content search alone. Headers and footers use separate ranges in Word’s object model. Add explicit searches for the required section headers and footers, or place the changing fields in supported content controls.

Will this macro work in Excel for Mac?

No, not as written. It relies on Windows COM automation to create Word.Application. Mac automation needs a different approach, such as AppleScript-based integration, and Excel for the web does not execute VBA.

Can one Excel row create one Word file?

Yes. Add a loop over the worksheet rows, read each row’s values, replace the corresponding placeholders, and save each generated document under a distinct filename. Validate the row data and ensure filenames are unique before scaling up to a large batch.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.