How to make Microsoft Word visible after opening an Excel file in Word VBA

Question

I have a VBA Word macro that opens a Word doc, then opens an Excel file, selects a cell reference and finally displays a message using Msgbox. After the Excel file has been opened I am having trouble finding the code to make Word visible so that the user can view the Msgbox message without having to using the task bar to switch from Excel to Word. I tried oWord.Visible = True but VBA gives me an error. Any hints are appreciated.

See the code below:

Sub Module_Test()
Dim oExcel As Object
Dim oWord_Doc as object
Dim wb_open as workbook
Set oExcel = New Excel.Application
str_Excel_Filename = "C:\Test\Excel_Template.xlsx"
Documents.Open ("C:\Test\Doc_to_process.docx")
Set oWord_Doc = activedocument
oExcel.Visible = True
oExcel.ScreenUpdating = True
oExcel.Workbooks.Open str_Excel_Filename
Set wb_open = activeworkbook
wb_open.ActiveSheet.range("a6").Select
' At this point Excel is visible.  But the Msgbox statement below is not visible except when one switches to Word using the task bar.  What statement do I put here to make Word visible?
Msgbox "Here is a message that should be visible when viewing the window containing the Doc_to_process.docx"
End Sub

Can you try adding oWord_Doc.Activate below the wb_open line — Jchang43
Try AppActivate "Microsoft Word" or AppActivate Application.Caption — Tim Williams
As Tim Williams suggested: between the lines 'wb_open ...' and 'MsgBox ... ' insert the line 'AppActivate "Microsoft Word"' and the MsgBox will display and when hitting OK, Word will be active (visible) and Excel in the background. — VBasic2008
I tried all three options above. The results are oWord_Doc.Activate compiles just fine but doesn't switch the active application to Word (but it does if you put a stop on the line in the VBA editor). AppActivate "Microsoft Word" and AppActivate Application.Caption both results in an execution error of Invalid procedure call or argument. Thanks Brotato and @tim-williams for your suggestions. — Kaiser Otto

freeflow freeflow · Accepted Answer · 2018-10-30T21:49:56

Visible is at the Application level. Your oExcel variable gives you the clue. You don't have a variable called oWord.

Edited to add the following code

Option Explicit

Sub Module_Test()

Const MY_WB_PATH                As String = "C:\Test\Excel_Template.xlsx"
Const MY_DOC_PATH               As String = "C:\Test\Doc_to_process.docx"

Dim my_xl_app                   As Excel.Application
Dim my_doc                      As Word.Document
Dim my_wb                       As Excel.Workbook

    Set my_xl_app = New Excel.Application

    With my_xl_app
        .Visible = True
        .ScreenUpdating = True
        Set my_wb = .Workbooks.Open(MY_WB_PATH)

    End With

    my_wb.Activate
    my_wb.activeworksheet.Range("a6").Select

    ' At this point Excel is visible.  But the Msgbox statement
    ' below is not visible except when one switches to Word using
    ' the task bar.  What statement do I put here to make Word visible?
    Set my_doc = Documents.Open(MY_DOC_PATH)
    my_doc.Activate
    ' If required
    my_doc.Application.Visible = True

    MsgBox "Here is a message that should be visible when viewing the window containing the Doc_to_process.docx"
End Sub

If you are new to VBA then the following should always be used.

In the VBA IDE ensure each module starts with 'Option explicit'
In the VBA IDE ensure that all checkboxes in Tools.Option.Code Settings are ticked

How to make Microsoft Word visible after opening an Excel file in Word VBA

1 Answers