[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-06
3
Medium Priority
?
533 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:CraigLowdon
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
Jubal1234 earned 1500 total points
ID: 22894736
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
 

Author Comment

by:CraigLowdon
ID: 22894805
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
 

Author Closing Comment

by:CraigLowdon
ID: 31513828
Hi, after no further comment i have closed this question, thank you for your help.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

834 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