asked on

Calling Word macros from Excel to add MailMerge fields

I have some specific questions relating to Excel vba, Word vba and MailMerge.

My questions are:
1. I can get this instruction to work from Word vba:
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="FirstName"
I can access a Word document from Excel vba, however this instruction fails. So my question is can this instruction
be issued from an Excel macro?

2. From Word it is possible to format the output of a MailMerge field using Toggle Field Codes. How can this be done from a vba macro? I have managed to do it using find and replace but surely there is a better way?

3. From an Excel macro I can invoke a simple Word macro, with code like:
Set WD = CreateObject("Word.Application")
WD.Run "runTxtConversion", txtFile
For me this works fine so long as there is only a single Word document open.
How can I make it work if there are multiple Word document?

I would be grateful for either direct answers or references to books or on-line documents that can answer my questions.

Many thanks,

Peter

GrahamSkan

It isn't clear whether your code knows what the ActiveDocument is, but the main problem is the Selection object. There is one in Excel and one in Word, and Excel will assume that you mean its own. Personally I try to avoid it in either application, but use Range objects instead

This illustrative macro uses early binding, so needs a reference (Tools/References) to the MIcrosoft Word Object library.

Option Explicit

Sub WordFromExcel()
    Dim WD As Word.Application
    Dim Doc1 As Word.Document
    Dim Doc2 As Word.Document
    Dim wrdRange As Word.Range
    
    Set WD = CreateObject("Word.Application")
    Set Doc1 = WD.Documents.Open("C:\MyFolder\doc1.docx")
    Set wrdRange = Doc1.Content
    Doc1.MailMerge.Fields.Add wrdRange, Name = "FirstName"
    
    Set Doc2 = WD.Documents.Open("C:\MyFolder\doc2.docx")
    WD.ActiveWindow.View.ShowFieldCodes = True
    WD.Run "runTxtConversion", txtFile
    WD.ActiveWindow.View.ShowFieldCodes = False

End Sub

Open in new window

Norie

Peter

To use Application.Run when there are multiple documents open specify the document the sub you want to run is in.

For example

Application.Run "'My Document.docm'!runTxtConversion"

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.