Results 1 to 5 of 5
Like Tree1Likes
  • 1 Post By Strider71

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

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 ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    7

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

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,505

    Default Re: Using Excel VBA to open a Word document and enable Word macros

    Try...

    Code:
    wordApp.Run "MailMergeMacro"
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    7

    Default Re: Using Excel VBA to open a Word document and enable Word macros

    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.

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    7

    Default Re: Using Excel VBA to open a Word document and enable Word macros

    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!
    James Snyder likes this.

  5. #5
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    14,505

    Default Re: Using Excel VBA to open a Word document and enable Word macros

    Glad you've sorted it out. And thanks for posting your solution.
    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com