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.1 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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Author
Commented:GetObject will only get the first open Excel application. If you have multiple Excel sessions running you cannot use GetObject to identify each of them.
Dale