Populate Word Merge Fields from Excel Macro

jessebh2003

Board Regular
Joined
Feb 28, 2020
Messages
71
Office Version
  1. 365
Platform
  1. Windows
I'm writing an Excel macro that would open a Word template and replace the merge fields with data from a specific cell in Excel. I've been searching the internet for weeks and have been mostly unsuccessful. Pulling from what I could find, I came up with the below. However, this didn't populate the merge fields.

For context, the Excel data is exported from our tracking system and then a summary macros is ran. The below macro would run second. Can someone help me figure out how to find a merge field in the template and replace it with the data from Excel? I'm really struggling with this one. Thanks.


VBA Code:
    ' Starts a Word document
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Add(Template:="SomeWordTemplate.dotx", _
    NewTemplate:=False, DocumentType:=0)
    
    With wrdDoc
        .Application.Selection.Find.Text = "<<MergeFieldName1>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("B5")
        
        .Application.Selection.Find.Text = "<<MergeFieldName2>>"
        .Application.Selection.Find.Execute
        .Application.Selection = Range("C5")
        

        .SaveAs2 Filename:=("NewFileName"), _
        FileFormat:=wdFormatDocumentDefault, AddtoRecentFiles:=True
        
    End With
    
End Sub
 
So do you need to create a separate mergefield for each instance in the Word doc? For example, if I want "Name" (taken from Excel worksheet cell) to appear in several places in the Word doc, do I need to define a unique mergefield for each instance and then replace via script? Or is there a way I can create a NAME mergefield and use that one field multiple times in the doc and have the script replace each one? I hope you understand my question... TIA for any input.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I use the same merge field in multiple places in my document. The script would replace the NAME merge field each time it’s in the document. You would need to add the code for each occurrence that you use NAME and in the order they appear on your document. For instance, if your merge fields were and appeared in this order: NAME, TITLE, ADDRESS, NAME, DATE, NAME - you would need a the script for each merge field. Does that make sense/help?
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,775
Members
448,298
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top