Activating Excel From Another Application

This page describes how to activate the Excel application when you are controlling it via Automation from another application.

Introduction

If you are doing cross-application programming, such as working in Word with code to automate Excel, you may find it useful for the user's convenience to activate the main Excel window from code running in Word while Word is the active application. You can try to use the AppActivate statement, but this requires that you know in advance the caption of the window you want to activate. Moreover, I have found AppActivate to be somewhat flakey. Sometimes it works and sometime not (raising an error 5, Invalid Procedure Call). Moreover it does not always set keyboard focus to the application.

You can work around these problems with AppActivate with a few simple Windows API functions.

An API call is a procedure call directly to one of the DLL library files that make up Windows. While using API functions often allows you to do things that can't be done within VBA itself, APIs don't have the error handling features of VBA code. If you call an API function with invalid parameters, you will likely crash Excel and lose all your work. API functions should be used with great caution.

The following code will activate the main Excel window and set keyboard focus to the ActiveSheet in Excel. Paste the following code into a new code module, and run the macro ActivateExcel. Note that you must not have any VBA Editors open. If you have a VBA Editor open, the system will set focus to that Editor window, rather than to Excel. This problem has largely been fixed in Excel 2007.

For a list of application window classes for most Office applications (versions 2003 and 2007 only) click here.

You can download a bas file containing the code on this page.

This code should work for any Office application whose window class is known. Change the value of C_MAIN_WINDOW_CLASS from XLMAINto the window class of the other application (e.g., OpusApp for Word).

Compelete VBA Code

Option Explicit
Option Compare Text
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modActivateExcel
' By Chip Pearson, www.cpearson.com, chip@cpearson.com
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Window API Declarations
' These Declares MUST appear at the top of the
' code module, above and before any VBA procedures.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function BringWindowToTop Lib "user32" ( _
    ByVal HWnd As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    
Private Declare Function SetFocus Lib "user32" ( _
    ByVal HWnd As Long) As Long

Public Sub ActivateExcel()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ActivateExcel
' This procedure activates the main Excel application window,
' ("XLMAIN") moving it to the top of the Z-Order and sets keyboard
' focus to Excel.
'
' !!!!!!!!!!!!!!!!!!!!!!!!!
' NOTE: This will not work properly if a VBA Editor is open.
' If a VBA Editor window is open, the system will set focus
' to that window, rather than the XLMAIN window.
' !!!!!!!!!!!!!!!!!!!!!!!!!
'
' This code should work to activate any application. Change the
' value of C_MAIN_WINDOW_CLASS to the application's main window
' class (e.g., "OpusApp" for Word).
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Res As Long     ' General purpose Result variable
    Dim XLHWnd As Long  ' Window handle of Excel
    Const C_MAIN_WINDOW_CLASS = "XLMAIN"
    '''''''''''''''''''''''''''''''''''''''''''
    ' Get the window handle of the main
    ' Excel application window ("XLMAIN"). If
    ' more than one instance of Excel is running,
    ' you have no control over which
    ' instance's HWnd will be retrieved.
    ' Related Note: You MUST use vbNullString
    ' not an empty string "" in the call to
    ' FindWindow. When calling API functions
    ' there is a difference between vbNullString
    ' and an empty string "".
    ''''''''''''''''''''''''''''''''''''''''''
    XLHWnd = FindWindow(lpClassName:=C_MAIN_WINDOW_CLASS, _
                    lpWindowName:=vbNullString)
        
        If XLHWnd > 0 Then
        '''''''''''''''''''''''''''''''''''''''''
        ' If HWnd is > 0, FindWindow successfully
        ' found the Excel main application window.
        ' Move XLMAIN to the top of the
        ' Z-Order.
        '''''''''''''''''''''''''''''''''''''''''
        Res = BringWindowToTop(HWnd:=XLHWnd)
         
        If Res = 0 Then
            Debug.Print "Error With BringWindowToTop:  " & _
                CStr(Err.LastDllError)
          Else
            '''''''''''''''''''''''''''''''''
            ' No error.
            ' Set keyboard input focus XLMAIN
            '''''''''''''''''''''''''''''''''
            SetFocus HWnd:=XLHWnd
          End If
    Else
        '''''''''''''''''''''''''''''''''
        ' HWnd was 0. FindWindow couldn't
        ' find Excel.
        '''''''''''''''''''''''''''''''''
        Debug.Print "Can't find Excel"
    End If
End Sub

This page last updated: 25-April-2016.