Microsoft, Excel/Word, 2007, VB - Opening word and creating a merge

Posted on 2008-11-06
Last Modified: 2010-04-21

I have an excel macro which runs a lot of external odbc extracts and 'plonks them onto the sheets, it then saves and closes the workbook, the location it saves is the data source for a mail merge in a word document, but i want my excel macro to continue by opening word, printing the mail merge to office document writer and then choosing the location of the save.

I have managed to create word as an object within excel doing this

 Dim wdApp As Object
    Set wdApp = CreateObject("word.application")
    wdApp.Visible = True

Now there is also a Getobject() function i have found but this seems to produce an error when I give it the arguments (a file path as a string then "word.application")

I Then want to open the document and run the mail merge, I have recorded a macro in word (as I have never used VB in word before) and got this

'    ActivePrinter = "Microsoft Office Live Meeting 2007 Document Writer"
'    With MailMerge
'        .Destination = wdSendToPrinter
'        .SuppressBlankLines = True
'        With .DataSource
'            .FirstRecord = wdDefaultFirstRecord
'            .LastRecord = wdDefaultLastRecord
'        End With
'        .Execute Pause:=False
'    End With

What i have noticed is that this does not give any file path for saving the file as I did during the recording and Im aware that I will have to add wdapp. to the beginning of anything I do within word, i just cant seem to get this right!

any extra explanation please ask as I know this isn't the clearest post in the world.


Question by:CraigLowdon
    LVL 4

    Accepted Solution

    Hi Graigh,

    First of all I would advise you to set a permanent link from Excel to Word. You can achieve this by clicking Tools - references in the VBA screen. In the list you see click the "Microsoft Word 12.0 Object Library". After doing so you can use another kind of binding:

    Sub test()
    Dim W As New Word.Application
    Dim D As Word.Document

        W.Visible = True
        Set D = W.Documents.Open("myfilename")

    End Sub

    Above example opens Word, makes it visible and then opens a document called MyFileName. Now for the rest:

    Sub test()
    Dim W As New Word.Application
    Dim D As Word.Document

        W.Visible = True
        Set D = W.Documents.Open("c:\temp\myfilename") --> This document would be your MailMerge document

    'The next command will link the database. In my case a MDB file. Could be a csv, SQL server or what ever. If you need to other examples; Use the Macro recorder in Word and link a database to a word document to see what the exact code would be. Be aware that there is also a SQL statement below that determines the records you are extracting.

        D.MailMerge.OpenDataSource Name:= _
            "c:temp\MyDatabase.mdb", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Temp\MyDatabase.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Da" _
            , SQLStatement:="SELECT * FROM `qAlgemeneGegevens`", SQLStatement1:="", _

    'And last: start the mailmerge and (in this case) send directly to the default printer.

        With D.MailMerge
            .Destination = wdSendToPrinter
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = wdDefaultFirstRecord
                .LastRecord = wdDefaultLastRecord
            End With
            .Execute Pause:=False
        End With
    End Sub

    Hope this helps.
    Gerard Verbruggen

    Author Comment

    This really helps,

    I have the word document I am opening already directly linked to my datasource so can skip that step, the only question I have is the mail merge print It will ask me which directly I would like to save the image to (as im printing to the hard drive in mdi format rather than a physical printer) and I would like the macro to automatically know the path that this image should go instead of asking me.

    Thanks again.


    Author Closing Comment

    Hi, after no further comment i have closed this question, thank you for your help.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now