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]

Try...

[COLOR=#ff0000]wordapp.[/COLOR]ActiveDocument.MailMerge

Hope this helps!

I am guessing the above suggested will do the trick,as active document is not an excel object.. Will try and get back many thanks

 

[tomcatonnet99]

not really, but I with the change Wordapp.activedocument.mailmerge I could get Word to open but yet without the SQL message which requires user to click 'Yes/No' to update the mailmerge...

what am I doing wrong?

the word file already has the link to source file and all mergefields are set up too, all I want for it to do is give the user the 'Update Yes/NO' option and allow for me to check through all the merged records << 1 >> through to as many..

 

[tomcatonnet99]

Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "I:\S_HSFCOL\HLFS\SCQ's Auckland\Self Completion Form Template.docm"
wordapp.Visible = True

ActiveDocument.MailMerge.OpenDataSource Name:= _
"I:\S_HSFCOL\HLFS\SCQ's Auckland\Forms.xlsm", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=True, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=""I:\S_HSFCOL\HLFS\SCQ's Auckland\Forms.xlsm"";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Loc" _
, SQLStatement:="SELECT * FROM `Forms$`WHERE `Printed` ='N'", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

Returns a option box of table , forms …etc but not the Update Y/N query…

Also the mailmerge tab is blank

I've added the MS Word object reference

does revert need to be False or True?? ( my source file is the file which runs this code, so it must be open at all times )

Whats missing?? do I need to do a get object?? ( whats the exact purpose of the same please ?)

any help will be most appreciated

 

[Domenic]

I don't really use mail merge, so I'm not sure what you mean by "Update Y/N query". I suggest you start a new thread, and post your question there. If you provide a link here to your new thread, I'll have another look when I get a chance if no one else replies.

 

[tomcatonnet99]

to answer your query about the 'Update' box...

When a mailmerge doc is opened there is a message which says " Opening this document will run the following SQL command" , "Select *FROM `Sheet1`..... " with a 'Yes/No' option to update the merge from the source file...

and to clarify the box that I get it says " Select Table" ...

 

[tomcatonnet99]

Here's more I found on what I'm after but I need just the part which helps open the document and update merge records as my Word file has its own VBA code to take care of the actual merge
Are you able to help in light of this one?? ( for some reason this code runs really slow.....)

Sub RunMailMerge()

Dim wdOutputName, wdInputName As String
wdOutputName = ThisWorkbook.Path & "\Reminder Letters " & Format(Date, "d mmm yyyy")
wdInputName = ThisWorkbook.Path & "\MailMergeLayout.doc"

' open the mail merge layout file
Dim wdDoc As Object
Set wdDoc = GetObject(wdInputName, "Word.document")
wdDoc.Application.Visible = True

With wdDoc.MailMerge
.MainDocumentType = wdFormLetters
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute Pause:=False
End With

' show and save output file
wdDoc.Application.Visible = True
wdDoc.Application.ActiveDocument.SaveAs wdOutputName

' cleanup
wdDoc.Close SaveChanges:=False
Set wdDoc = Nothing

End Sub

 

[Domenic]

Oh I see. It looks like the default option is "No". If you want to choose "No", you can suppress the dialog box and choose the default response by setting DisplayAlerts to False...

Application.DisplayAlerts = False
wordapp.ActiveDocument.MailMerge.OpenDataSource...
Application.DisplayAlerts = True

Otherwise, see if the following thead helps...

https://social.msdn.microsoft.com/F...l-run-the-following-sql-command?forum=worddev

 

[tomcatonnet99]

what lead you to believe the default option to be 'No' ?

all I want is for the message box "Opening the document will run the following SQL command " to be made available to the user , from that point forward the user could either select 'Yes' or 'No'.

In short I want for Excel to open the existing mailmerge document and maintain the source link ( even if there is nothing to merge - which at present is lost if there isn't anything to merge)

 

[tomcatonnet99]

Private Sub CommandButton1_Click()


Set wordapp = CreateObject("word.Application")

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


wordapp.Visible = True

wrddoc = wordapp.documents("C:\Users\User\Desktop\sourceofletters.xls") ' <<< Error!!


wrddoc.mailmerge.maindocumenttype = wdformletters '<<<< Doesn't work either

With wrddoc.activedocument.mailmerge

.OpenDataSource Name:="C:\Users\User\Desktop\sourceofletters.xls", _
SQLStatement:="SELECT * FROM `Sheet1`"




End With

End Sub

 

[Domenic]

Sorry, it looks like I misunderstood. If I now understand you correctly, you want the user to decide whether to open the data source when opening the main document. When it's opened manually, you'll get that dialog box asking whether to open the data base. With VBA, you use the Open method of the Document object to open the main document. And, if you want to open the data source, you use the OpenDataSource method of the MailMerge object. So you'll need to use a MsgBox to ask the user whether to open the data source. If the user answers/clicks "Yes", the code then proceeds to open the data base. Try...

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

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()

    [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] Ans [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wdApp = CreateObject("Word.Application")
    wdApp.Visible = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] wdDoc = wdApp.Documents.Open("C:\Documents and Settings\User\Desktop\mergeletter.doc")
    
    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:="C:\Users\User\Desktop\sourceofletters.xls", _
                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]Sub[/COLOR]

Does this help?