VBA activating Word but Ribbon unresponsive and add-in not loaded

Question

I've got an Excel VBA macro in Office 2013 on Windows 8.1 that prompts the user to select a Word document, then drives a mailmerge in Word using the currently open spreadsheet in Excel and finishes by activating Word so the user can edit the merged document. It works just about perfectly, exactly the way I want it to.

Two problems though: The merged doc is now active in Word but a) clicks on Ribbon tabs and controls are not recognized (i.e., nothing happens), and b) an add-in in my Word startup directory is not loaded.

Both of these issues can be fixed by quickly Alt-tabbing to Excel and back to Word. But how can I prevent it in the first place? This is intended for use by users with limited knowledge and having to explain alt-tab and why they need to do it would not be pretty.

Public Sub MergeMarkedPatients()
    Dim wd As Word.Application
    Dim doc As Word.Document
    Dim src As String, tmpl As String, sh As String
    Dim savepath As String, filename As String
    tmpl = SelectFile(Array("Bill Templates", "*.doc; *.docx"), gDocumentsDir)
    If tmpl = "" Then Exit Sub
    Set wd = New Word.Application
    wd.Visible = True
    Set doc = wd.Documents.Open(filename:=tmpl)
    src = ActiveWorkbook.FullName
    sh = ActiveWorkbook.Worksheets(1).Name & "$"
    savepath = wd.ActiveDocument.Path & Application.PathSeparator
    filename = Replace(GetBasename(wd.ActiveDocument.FullName), "!", "") & " " _
        & Left(ActiveWorkbook.Worksheets(1).Name, 9)
    With doc.MailMerge
        .OpenDataSource Name:=src, ConfirmConversions:=False, ReadOnly:=False, _
            LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
            PasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
            Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" _
            & src & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:" _
            & "System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine " _
            &"Type=35;Jet OLEDB:Databa", SQLStatement:="SELECT * FROM `" & sh _
            & "` WHERE PRINT=""P""", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=True
    End With
    doc.Close savechanges:=wdDoNotSaveChanges
    ProcessMerges wd.ActiveDocument
    wd.ActiveDocument.BuiltinDocumentProperties(wdPropertyTitle).Value = savepath _
        & filename
    wd.Activate
End Sub

+1 I have seen this behaviour in other Office programs, when they are opened by another program (i.e. you download an xlsx file, and Excel automatically opens). Happens under Windows 7 too. No idea what causes it though. :( — vacip

ChipsLetten ChipsLetten · Accepted Answer · 2015-08-01T15:02:34

On the add-in issue, I used to have same issue when starting Excel using automation. The fix is to loop through the add-ins and unload, then re-load the ones that should be loaded. I suspect the same is true for Word. See this SO answer for the code

VBA activating Word but Ribbon unresponsive and add-in not loaded

1 Answers