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

Strider71

New Member
Joined
Jan 27, 2012
Messages
7
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:


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

Any insight would be greatly appreciated!
Thanks.
-Strider71
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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:

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

Cheers!
 
Upvote 0
Glad you've sorted it out. And thanks for posting your solution.
 
Upvote 0
I have a similar issue, can someone pls point out why I get a 'object missing' error when I run this code ( Excel 2003), I'm not sure what library references are required either.... ( all I did was recorded a macro in Word and transferred it over here ) .... also whilst setting up the mail merge manually I directly selected the source file from desktop ( no idea whether the options New Data connection or Microsoft SQL connection needed to be selected (??) ... Urgently require help with this please... ( trying to get this sorted for work )


Set wordapp = CreateObject("word.Application")


On Error Resume Next
wordapp.documents.Open "C:\Documents and Settings\XXXX\Desktop\mergeletter.doc"




wordapp.Visible = True







ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Users\Germaine\Desktop\sourceofletters.xls", ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Users\XXXX\Desktop\sourceofletters.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess = True
 
Upvote 0
Try...

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

Hope this helps!
 
Upvote 0
Thanks for your code...but its not working and getting object required error...can you please provide any other code.. Thanks for your support..
 
Last edited:
Upvote 0
Does it help if you add the following line right after the one that opens your word document?

Code:
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top