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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jubal1234Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.