Using Excel VBA to open a Word document and enable Word macros

[Strider71]

I have an Excel 2007 macro that opens up a Word document and runs a Word macro that does a mail merge. It works great in Excel 2007, but I just switched to Excel 2010 (and Word 2010 of course). The Excel code still opens the Word file, but the macros are disabled when it opens Word so it doesn't run the mail merge macro.
Even if I enable Word macros in the Trust Center Settings (I know that's not recommended) it still doesn't allow me to run the Word macro.
If I open the Word file on my own and enable the macro, it works just fine.

The Excel code to Open Word and run the macro is:

Sub Run_Mail_Merge()
Dim wordApp As Object
    Set wordApp = CreateObject("Word.Application")
    wordApp.Documents.Open Filename:="D:\Form Letter.docm"
    wordApp.Visible = True
    wordApp.Run "!newmacros.MailMergeMacro"
End Sub

My Word code (which won't run if the Excel macro opens the file), is:

Sub MailMergeMacro()
If ActiveDocument.MailMerge.State = wdMainAndDataSource Then
    ActiveDocument.MailMerge.Execute
End If
ActiveDocument.SaveAs FileName:="Completed Letter.doc", 
   FileFormat:=wdFormatDocument, LockComments:=False, Password:="",AddToRecentFiles:= True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
End Sub

Any insight would be greatly appreciated!
Thanks.
-Strider71

 

[tomcatonnet99]

Thanks Dominic, the only issue I have is the document once opened doesn't allow next/previous records to be visible. Whereas when manually opened the user can see next/prev records in word

When get object is used , that part works , and even the SQL query msg box is presented to user...

What I would ideally like is for user to open doc , presented with the query to update,... ( as also for doc to not lose its data source if there's nothing to merge)
Many thanks, much appreciate your patience

Regds
Tom

 

[Domenic]

I won't be able to look into this until this evening, but I'll definitely get back to you. ..

 

[Domenic]

First, you mentioned "query to update". Were you referring to the SQL message? Secondly, you're right. When GetObject is used, the SQL message is displayed when the document is opened. But, as far as I know, there's no way to allow the user to choose an option by code. As I mentioned previously, you can suppress the message by setting DisplayAlerts to False. In doing so, the default response is chosen, which in this case is "No". Then you can simply ask the user whether to open the data source. And, only if the user answers "Yes" will the data source be available so that one can preview the results. So maybe something like this...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] OpenMailMergeDocument()

    [COLOR=darkblue]Dim[/COLOR] wdApp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] wdDoc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sMainFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sDataFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Ans [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    sPath = "c:\users\domenic\desktop\"
    sMainFile = "main.docx"
    sDataFile = "data.xlsx"

    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wdApp = GetObject(, "Word.Application")
    [COLOR=darkblue]If[/COLOR] wdApp [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] wdApp = CreateObject("Word.Application")
        wdApp.Visible = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wdDoc = wdApp.Documents(sMainFile)
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
    [COLOR=darkblue]If[/COLOR] wdDoc [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        wdApp.DisplayAlerts = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Set[/COLOR] wdDoc = wdApp.Documents.Open(sPath & sMainFile)
        wdApp.DisplayAlerts = [COLOR=darkblue]True[/COLOR]
        Ans = MsgBox("Open the data source?", vbQuestion + vbYesNo, "Confirm Open Data Source ?")
        [COLOR=darkblue]If[/COLOR] Ans = vbYes [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] wdDoc.MailMerge
                .MainDocumentType = 0 [COLOR=green]'wdFormLetters[/COLOR]
                .OpenDataSource _
                    Name:=sPath & sDataFile, _
                    SQLStatement:="SELECT * FROM `Sheet1$`"
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    wdApp.Activate
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[tomcatonnet99]

1)What's the diff between set object and get object pls?

2) once mergefile is opened (by VBA), I'm unable to check records by use of prev/next in word, the same is possible if I open mergefile manually... ( what's the solution to this)? I did include wdfirstrecord wdlastrecord code but to no avail!, it picks all relevant records but doesn't allow preview of the same as it does with manual opening of mergefile

3) if mergefile doesn't find any data to merge, it seems to lose data source! (is that a known issue?) - this is also true if I use followhyperlink method (being the easiest!!, but for the delayed virus cautionary msg it pops up, well after the sendtoprinter command has been fired off)

4) would using set wordapp as word application followed by get word document help in any way??

Many thanks once agin

Regds
Tom

 

[Domenic]

GetObject is used to return a reference to an application that's already running or to start an application with a file already loaded.

CreateObject is used to return a reference to a new instance of an application. This function is used when an application isn't running. If one is already running, a new instance is started.

With the code I offered, the merge file is opened, and the user is asked whether to open the source data. If the user answers "Yes", the source data is loaded. At this point, the user should be able preview the results and use Previous/Next Record. Did you try my code? Can you post the exact code you're using?

 

[tomcatonnet99]

Hi Domenic, thanks for your reply, will try your code at work tomorrow (sorry don't have my code at home either)

Just a clarification pls, wouldn't opening a doc not automatically open an instance of application anyway? (I'm not entirely clear about get object)

Also I have tried your .opendatasource method but it didn't allow for preview of prev/next (although on .sendtoprinter, picked up all records being sorted just fine) so I'm pretty sure user answering 'yes' to SQL msg will not work.
I should point out that .opendatasource I used didn't include connection as 'named range', I will try naming the entire range on sheet1 to see if that helps? (your thoughts?), although at the moment its an exact copy paste of this event generated by recording macro in word

Also SQL statement has 2 conditions (now),
SELECT FROM `sheet1` WHERE `Printed`='N' And `Area`='XYZ'.....

 

[Domenic]

Just a clarification pls, wouldn't opening a doc not automatically open an instance of application anyway?

If Word is already open, GetObject is used to return a reference to the application. Then you can proceed to open a document...

    Dim wdApp As Object
    Dim wdDoc As Object
    Set wdApp = GetObject(, "Word.Application")
    Set wdDoc = wdApp.Documents.Open("c:\...")

If Word isn't already open, GetObject can still be used to start the application with the file already loaded...

    [COLOR=darkblue]Dim[/COLOR] Doc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Doc = GetObject("c:\users\domenic\desktop\main.docx")

If Word isn't already open, CreateObject can be used to start the application. Then you can proceed to open a document...

    Dim wdApp As Object
    Dim wdDoc As Object
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open("c:\...")

Note that if Word is already open and you use CreateObject, a new instance of the application is started.

Here's an example that opens a mail merge document...

https://onedrive.live.com/redir?resid=41260C1C818928B2!652&authkey=!AMDIsbBLCwQe7zw&ithint=file,zip

Make sure that all files reside within the same folder and run the the macro in the workbook called "open mail merge document.xlsm".

 

[tomcatonnet99]

Hi Domenic, Many thanks once again, did just try out your code and the results were no different to my code (below)

Private Sub CommandButton1_Click()


Set wordapp = CreateObject("word.Application")

wordapp.documents.Open "C:\Documents and Settings\Germaine\Desktop\mergeletter.doc"

wordapp.Visible = True
wordapp.Activate



wrddoc = wordapp.documents("C:\Documents and Settings\Germaine\Desktop\mergeletter.doc")





With wordapp.activedocument.mailmerge
.OpenDataSource Name:= _
"C:\Users\Germaine\Desktop\sourceofletters.xls", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=True, Format:=wdOpenFormatAuto, _
Connection:="Table", SQLStatement:="SELECT * FROM `Sheet1$` WHERE `Printed`='N'", SQLStatement1:=""

.DataSource.FirstRecord = wdDefaultFirstRecord
.DataSource.LastRecord = wdDefaultLastRecord

.Destination = wdSendToPrinter
'.Execute Pause:=False
End With

End Sub

 

[tomcatonnet99]

For checking preview I have prevented last 2lines of code...
' .destination
' .execute wdsendtoprinter...

 

[tomcatonnet99]

I reckon followhyperlink would B the easiest solution with least code lines... (your thoughts?)
To prevent virus cautionary msg that MS pops up, I think
Followhyperlink "file:///c:\..." could do the trick..
Not sure how to retain data source which seems to be quite volatile!

FURTHER to my earlier code, the source file itself fires the macros to open mail merge doc, open data source.... (just something I thought I'd mention if at all of any significance
.)