Filling a Word document from Excel

Word's mail merge can turn out a hundred letters at once. What it cannot do is turn out one document, saved in the right place, under the right name, when you click a row. Here is the theoretical principle in VBA, and the many technical obstacles of a home-made build.

Updated on 17 September 2026 · 11 min read · Excel on Windows

Why mail merge is not enough

Mail merge is designed for a mass mailing: one data source, one template, and a merge that produces one big hundred-page document. Three things are missing from it for day-to-day use:

• it produces a single file containing every recipient, when what you want is one file per client, in that client's folder;
• it does not name the files — you rename them by hand;
• it is launched from Word, not from the Excel row you are working on.

The method below turns the logic around: Excel drives Word, opens a template, replaces tags with the row's values, and saves the result wherever you like, under whatever name you like.

Preparing the Word template

Create an ordinary Word document — a quote, an engagement letter, a contract — and replace the variable values with tags. The shape does not matter as long as it is unlikely to occur in ordinary text ; here we use <$Nom> :

Subject: <$Categorie> engagement

Dear <$Client>,

We confirm our attendance on <$Date>
for an amount of <$Montant> euros.

On the Excel side, a sheet called Donnees where the column headers carry exactly the names of the tags — that is what will save you touching the code when you add a field:

The VBA outline: the automation principle (~10% of the code)

The code below shows the elementary mechanism: instantiate Word through late binding, open a copy of the document and run a basic replacement on the main body. It is the theoretical skeleton (~10% of the real code).

Option Explicit

' Theoretical principle: drive Word from Excel to replace a piece of text
Public Sub RemplirDocument_Extrait()
    Dim appWord As Object, doc As Object
    Dim modele As String, cle As String, valeur As String

    modele = ThisWorkbook.Worksheets("Donnees").Range("B1").Value
    cle = "<$Client>"
    valeur = ThisWorkbook.Worksheets("Donnees").Range("A4").Text

    ' Elementary start-up of Word through late binding:
    Set appWord = CreateObject("Word.Application")
    appWord.Visible = False
    Set doc = appWord.Documents.Add(modele)

    ' Basic replacement in the body text:
    With doc.Content.Find
        .Text = cle
        .Replacement.Text = valeur
        .Execute Replace:=2 ' 2 = wdReplaceAll
    End With

    ' [ ... Rest of the code not shown: dynamic loop over every column,
    '   replacement in headers and footers, shapes and text boxes,
    '   handling of ghost Word processes, PDF export and tracked changes ... ]
End Sub

Why this extract is not enough in practice

This code illustrates the conversation between Excel and Word. But to generate quotes or contracts in production, this extract solves only a fraction of the problem :

• Headers and footers ignored : the doc.Content object only searches the running text. Tags sitting in the header (file number, date, logo) are never replaced and stay visible.
• Invisible shapes and text boxes : if your Word template uses side panels or graphical text boxes, the standard search misses them completely.
• Ghost Word processes (memory leak) : at the slightest uncaught VBA error, Word stays alive in the background with no visible window. After a few attempts, dozens of WINWORD.EXE processes fill the memory and lock your files read-only.
• The template changing over time : if the quote template changes three months later, how do you update dozens of existing files without wiping out what was typed by hand? A home-made macro cannot handle that.

Writing a quick script to replace one word takes an hour. Building a reliable document generator that never jams Word and covers every area of the document takes days of fine-tuning.

The trap of headers and footers

This is the most frequent mistake, and the most discreet. doc.Content only covers the body text: headers, footers, text boxes and notes are excluded from it. A macro that only handles Content produces a perfect document… with the tag <$Client> still showing at the top of every page.

Hence the double loop over Sections, then over Headers and Footers. A document has three headers per section — primary, first page, even pages — and the For Each loop walks through all of them.

For text boxes and shapes, one more loop is needed:

Dim forme As Object
For Each forme In doc.Shapes
    If forme.TextFrame.HasText Then
        forme.TextFrame.TextRange.Find.Execute cle, , , , , , , , , valeur, 2
    End If
Next forme

Saving as PDF, and the special cases

To produce a PDF directly, replace the save with:

doc.ExportAsFixedFormat sortie, 17     ' 17 = wdExportFormatPDF

To produce both, chain them: SaveAs2 then ExportAsFixedFormat with the extension changed.

Name the file from the row rather than typing B2 by hand:

sortie = ws.Range("B2").Value & "\" & Format(Date, "yyyy-mm-dd") & _
         "_quote_" & ws.Cells(ligne, 1).Value & ".docx"

Putting the date first, in year-month-day order, means the files sort themselves chronologically in Explorer.

Handle every row in one go : wrap the body of the macro in a loop For ligne = 4 To derniere, opening Word only once before the loop and closing it afterwards. Launching and closing Word on every row multiplies the processing time by ten.

When the template changes

The macro settles the case of a brand-new document. What remains is the question that turns up six months later: your contract template changes — a clause, a legal mention, a VAT number — and forty documents already produced carry the old version.

Going back over them one by one is out of the question; overwriting them with a new document would erase everything typed since. The only tenable route is to replay the merge on the existing documents as Word tracked changes : the differences appear as corrections you accept or reject one at a time, with a time-stamped backup taken beforehand.

It can be done in VBA — doc.TrackRevisions = True is the starting point — but the devil is in the detail: preserving what was typed by hand, coping with documents a colleague has open, knowing which files to revisit. It is one of the functions of the add-in we sell, and probably the one that saves the most time over the long run.

Frequently asked questions

Read next