Microsoft, Excel/Word, 2007, VB - Opening word and creating a merge
Posted on 2008-11-06
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.