This is a discussion on Using Excel VBA to open a Word document and enable Word macros within the Excel Questions forums, part of the Question Forums category; I have an Excel 2007 macro that opens up a Word document and runs a Word macro that does a ...
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:
Code: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:
Any insight would be greatly appreciated!Code: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
Thanks for your reply Domenic. However I think the problem occurs before this part. I was testing it some more this morning, and I now believe the problem lies when the Word document is opened. There is a prompt that comes on the screen because the document is a mail merge and it says "Opening this will run the following SQL command". If I open this myself, I have to answer yes or no (the default being no).
When the macro goes to open this, it seems that it's answering no.
I've been looking up how to do an Excel VBA Open statment that answers yes to this question. Microsoft Supposrt suggests changing the registry (http://support.microsoft.com/kb/825765), but this macro will be used on many computers so I'd like to avoid having to change the registry on 50+ computers.
Do you know of a way to say "yes" to any prompts when doing a "wordApp.Documents.Open ..." statement? I couldn't find it in the Word help.
OK I solved the issue, so I thought I'd post what I came up with in case anyone else comes across the same thing.
At first I thought the issue was that it wasn't enabling the macros, but discovered this morning that it was the pop up box that warns that the Word file is about to run an SQL command i.e. because it's a Mail Merge document linked to another file. The macro was always saying no to the prompt, so it would lose the link to the mail merge data source. Instead of finding a way for the macro to say "yes" to the prompt instead, I just allowed it to say no, then used an extra piece of code in the Word macro to re-link the data to the data source in my Excel file.
This is the code I used:
Cheers!Code:Sub Link_to_Source() ActiveDocument.MailMerge.OpenDataSource Name:= _ "D:\Data for Mail Merge.xlsm", 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="D:\Data for Mail Merge.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 L" _ , SQLStatement:="SELECT * FROM `DTSdata$`", SQLStatement1:="", SubType:= _ wdMergeSubTypeAccess End Sub
Glad you've sorted it out. And thanks for posting your solution.