Automating Microsoft Excel from Access (Part II)

In Part I of this series, I discussed how to use early binding during application development and late binding during deployment to avoid reference issues associated with opening an Excel application in a late version Office and then again in an earlier version.  I then discussed how to create Access objects which point to Excel and to an Excel Workbook by using the GetObject and CreateObject functions.  Finally, I discussed the importance of closing Excel and releasing Excel objects in the proper order to prevent leaving instances of Excel open in memory, but invisible to the user.

Getting Started

The problem with the GetObject function mentioned in the first part of this series, is that it only returns a single instance of Excel (the first one), and there is no "simple" method to identify multiple instances. However, judicious use of several library functions will allow you to identify each open instance of Excel and the workbooks associated each of those open instances.  My only regret is that I cannot recall who it was that helped me develop this solution several years ago.

Before getting started with the actual process of identifying the instances of Excel, there are a number of library functions which must be declared.

#If VBA7 Then

    Public Declare PtrSafe Function FindWindowEx Lib "USER32" _
    Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _
    ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
   
#Else

    Public Declare Function FindWindowEx Lib "USER32" _
    Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
   
#End If
 
#If VBA7 Then

    Public Declare PtrSafe Function IIDFromString Lib "ole32" _
    (ByVal lpsz As LongPtr, ByRef lpiid As GUID) As LongPtr
   
#Else

    Public Declare Function IIDFromString Lib "ole32" _
    (ByVal lpsz As Long, ByRef lpiid As GUID) As Long

#End If
 
#If VBA7 Then

    Public Declare PtrSafe Function AccessibleObjectFromWindow Lib "oleacc" _
    (ByVal hWnd As LongPtr, ByVal dwId As Long, ByRef riid As GUID, _
    ByRef ppvObject As Object) As LongPtr
 
#Else
   
    Public Declare Function AccessibleObjectFromWindow Lib "oleacc" _
    (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As GUID, _
    ByRef ppvObject As Object) As Long
   
#End If

 For those of you not familiar with compiler declarations, the #IF...#Else...#EndIf construct displayed above might be a bit confusing.  Conditional compilation is typically used to compile the same program for different platforms. It is also used to prevent debugging code from  appearing in an executable file. Code excluded during conditional  compilation is completely omitted from the final executable file, so it  has no effect on size or performance.¹  Although the use of conditional compilation displayed above is not about "different platforms", it does allow the code to work properly in both 32 and 64 bit versions of Office.  The VBA7 compiler constant lets you check whether the code runs in Office 2010 or later.  If it does, "PtrSafe" tells the VBA interpreter in Office 2010 and later to handle the bitness intelligently; LongPtr will be a 32-bit integer in the 32-bit version of Office, and a 64-bit integer in the 64-bit version.

Enumerating Excel Instances

Instead of using the GetObject( ) function to identify instances of Excel, you can use the FindWindowEx library function to identify the handle associated with each instance.  At it's simplest, this would look like:

#If VBA7 Then
    Dim hwndXL As LongPtr
#Else
    Dim hwndXL As Long
#End If
hwndXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
Do While hwndXL > 0
    debug.print hwndXL
    hwndXL = FindWindowEx(0&, hwndXL, "XLMAIN", vbNullString)
Loop

Unfortunately, the handle retrieved by this first step of the process does not actually identify the handle of the Excel, it is more like the handle of the Window in which Excel is displayed.  In order to get the actual Excel "object", you must drill down two additional levels to identify the workbook(s) open in the Excel instance; this looks like:

#If VBA7 Then
    Dim hWinDesk As LongPtr
    Dim hWinWbk As LongPtr
#Else
    Dim hWinDesk As Long
    Dim hWinWbk As Long
#End If

hWinDesk = FindWindowEx(hwndXL, 0&, "XLDESK", vbNullString)
'Finds the handle of the first workbook on the "XLDESK"
hWinWbk = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)

If there is no workbook open within the Excel instance, the last step above will return a zero.  In all my efforts, I've been unable to figure out how to capture the Excel object associated with an open instance of Excel which does not contain at least one workbook.

Selecting the Excel object

However, if hWinWbk is not equal to zero, then you can capture the Excel object associated with the workbooks parent using two additional calls to the library functions identified above.

The MSDN reference for IIDFromString is located here.

The MSDN reference for AccessibleObjectFromWindow is located here.

Dim iid As GUID
Dim oXLApp as Object    'Excel.Application    '

Call IIDFromString(StrPtr("{00020400-0000-0000-C000-000000000046}"), iid)
If AccessibleObjectFromWindow(hWinWbk, OBJID_NATIVEOM, iid, obj) = RETURN_OK Then
    Set oXLApp = obj.Application
Else
    Set oXLApp = Nothing
End If

Identifying all of the worksheets and named ranges within an instance of Excel

Once you have the reference to the Excel object, you merely need to loop through the workbooks within the Excel object, and then the worksheets and named ranges within each workbook.

For Each oWB In oXLApp.Workbooks
                
    'Loop through worksheets
    For Each oWS In oWB.Worksheets
        Debug.Print iCounter, oWB.Name, oWB.FullName, "Worksheet", oWS.Name
    Next

    'Loop through named ranges
    For Each oName In oWB.Names
        Debug.Print iCounter, oWB.Name, oWB.FullName, "Range", oName.Name
    Next
                
Next

Helpful Tip: In my applications, I generally write the information displayed in the Print statements above to a table and use that info to display the information in a "wizard".  The attached module should run fine in any recent version of Access.

Finally

I've wrapped up all the code into easy to use functions for you to include in your projects.  Just include this file mod_XL_Instances.bas and you should be able to extend your applications and create your own user interface to work with Excel. 

In the next instalment of the series, I will discuss how to link data from an Excel Instance/Workbook and Worksheet or Named Range into your application or export data to a specific worksheet/range within a workbook.