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

Hi,

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.

Thanks

CraigLowdonAsked:
Who is Participating?
 
Jubal1234Connect With a Mentor Commented:
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:="", _
        SubType:=wdMergeSubTypeAccess

'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.
Regards,
Gerard Verbruggen
0
 
CraigLowdonAuthor Commented:
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.

0
 
CraigLowdonAuthor Commented:
Hi, after no further comment i have closed this question, thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.