Populate Word Merge Fields from Excel Macro

[jessebh2003]

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.

    ' 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

 

[Cerberus262]

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.

 

[jessebh2003]

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?

 

[Cerberus262]

So you can use mergefields more than once, but need a unique script for each occurrence. I get it. Thanks!