Link to home
Start Free TrialLog in
Avatar of Dale Fye
Dale FyeFlag for United States of America

asked on

Set reference to Excel instance which has no workbooks open from Access VBA

I'm working on an application that needs to identify all instances of Excel which are open, and be able to set the reference to the Excel object.

I found some code (slightly modified below) which does a good job of finding all of the instances where there is a workbook open in the instance, but I cannot figure out how to see the reference to an instance that doesn't have a workbook open.
Option Compare Database
Option Explicit
 
#If VBA7 Then

    Private 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

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

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

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

#End If
 
#If VBA7 Then

    Private 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
    
    Private 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
 
Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type
 
Private Const RETURN_OK As Long = &H0
Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0
 
Sub EnumerateExcelInstances()
     
    Dim iCounter As Long
    #If VBA7 Then
        Dim hWndXL As LongPtr
    #Else
        Dim hWndXL As Long
    #End If
    Dim oXLApp As Object
    Dim oWB As Object
    Dim oWS As Object
    Dim strSQL As String
    
    '// Build table first if it doesn't already exist
    Call BuildTable
     
     '// First the first Excel Window
    hWndXL = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
     
     '// Got one, at least...?
    Do While hWndXL > 0
         
        '// Increment counter
        iCounter = iCounter + 1
         
        '// Print Instance & Handle to Debug window
        '//Debug.Print "Instance #" & iCounter & ": "; "Handle: " & hWndXL
         
        '// Get a reference to the instance
        If GetReferenceToXLApp(hWndXL, oXLApp) Then
             
            If oXLApp.workbooks.Count = 0 Then
                '//If an Excel instance is open with no open workbook then
                strSQL = "INSERT INTO Temp_Excel_Instances (Instance, Handle, Workbook, Worksheet) " _
                       & "VALUES (" & iCounter & ", " _
                                    & hWndXL & ", " _
                                    & Quotes("No Open orkbooks") & ", " _
                                    & Quotes("No worksheets") & ")"
                CodeDb.Execute strSQL, dbFailOnError
            
            Else
                '// Iterate through the workbook
                For Each oWB In oXLApp.workbooks
                     '// Print Workbook name to DEBUG window
                    'Debug.Print , oWB.Name
                     
                     '// It would be easy enough to check for a workbook name
                     '// and close it here - the name would be passed as a
                     '// parameter and the procedure changed to a function
                     '// to return Success, or otherwise, to the calling procedure
                     '// With oWB
                     '//    .Saved = True
                     '//    .Close
                     '// End With
                     
                     '// List worksheets
                    For Each oWS In oWB.Worksheets
                        '//Debug.Print , , oWS.Name
                        strSQL = "INSERT INTO Temp_Excel_Instances (Instance, Handle, Workbook, Worksheet) " _
                               & "VALUES (" & iCounter & ", " _
                                            & hWndXL & ", " _
                                            & Quotes(oWB.Name) & ", " _
                                            & Quotes(oWS.Name) & ")"
                        CodeDb.Execute strSQL, dbFailOnError
                    Next
                Next
            End If
        End If
         
         '// Find the next Excel Window
        hWndXL = FindWindowEx(0, hWndXL, "XLMAIN", vbNullString)
         
    Loop
     
End Sub
 
 '// Returns a reference to a specific instance of Excel.
 '// The Instance is defined by the Handle (hWndXL) passed
 '// by the calling procedure

#If VBA7 Then
    Function GetReferenceToXLApp(hWndXL As LongPtr, oXLApp As Object) As Boolean
#Else
    Function GetReferenceToXLApp(hWndXL As Long, oXLApp As Object) As Boolean
#End If
    
    #If VBA7 Then
        Dim hWinMain As LongPtr
        Dim hWinDesk As LongPtr
        Dim hWin7 As LongPtr
    #Else
        Dim hWinMain As Long
        Dim hWinDesk As Long
        Dim hWin7 As Long
    #End If
    Dim obj As Object
    Dim iID As GUID
     
     '// Rather than explaining, go read
     '// http://msdn.microsoft.com/en-us/library/windows/desktop/ms687262(v=vs.85).aspx
    Call IIDFromString(StrPtr(IID_IDispatch), iID)
     
     '// We have the XL App (Class name XLMAIN)
    hWinMain = FindWindowEx(hWndXL, 0&, "XLMAIN", vbNullString)
     
     '// This window has a child called 'XLDESK' (which I presume to mean 'XL desktop')
     '// XLDesk is the container for all XL child windows....
    hWinDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
     
     '// EXCEL7 is the class name for a Workbook window (and probably others, as well)
     '// This is used to check there is actually a workbook open in this instance.
    hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)
     
     '// Deep API... read up on it if interested.
     '// http://msdn.microsoft.com/en-us/library/windows/desktop/dd317978(v=vs.85).aspx
    If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
        Set oXLApp = obj.Application
        GetReferenceToXLApp = True
    End If
     
End Function

Open in new window

The problem is that the GetReferenceToXLApp() function is not returning an object (oXLApp), probably because the AccessibleObjectFromWindow() API call is not returning an 'obj'.  

Anybody, know how to modify this so that I can document the open instance and it's handle, and then set the reference to that instance of Excel, so a user can actually open a new workbook in that instance?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Seems to me that the AccessibleObject call should be looking at this handle:

 hWinMain = FindWindowEx(hWndXL, 0&, "XLMAIN", vbNullString)

or possibly:

    hWinDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)

but certainly not:

     hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)

 which is a workbook window.

Jim.
Avatar of Dale Fye

ASKER

Yeah, Jim, that's what I thought too, but I tried adding a couple of additional if statements to that as well, and none of them worked either.
If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
    Set oXLApp = obj.Application
    GetReferenceToXLApp = True
ElseIf AccessibleObjectFromWindow(hWinDesk, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
    Set oXLApp = obj.Application
    GetReferenceToXLApp = True
ElseIf AccessibleObjectFromWindow(hWinMain, OBJID_NATIVEOM, iID, obj) = RETURN_OK Then
    Set oXLApp = obj.Application
    GetReferenceToXLApp = True
End If

Open in new window

So, at this point, I need something that will provide me with the object identified by a the handle (hWinMain).
Dale,

 What version of Office?

Jim.
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Jim, i'm writing this app to hopefully work with 2007, 2010, and 2013.  Jack Leach, on the mvp google group, mentioned he had tried something a while back that had worked, so maybe i'll check back in with him.

Thanks, anyway.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Macro,

That has potential.  I had a chance to read it the other day, but have not had a chance to test it yet as I've been working a couple of other projects.
No reason it won't work.   The trick there is he's creating a workbook by basically doing a SendKeys to the window, so Excel opens a blank workbook, at which point AccessibleObjectFromWindow() will work because a workbook is now open.

 The timing might be a bit iffy because your really not in control.  If the workbook doesn't open for some reason, your stuck or it might be slow to open.

  I would probably put a in a loop to check for the Excel7 class or an error trap on using the AccessibleObjectFromWindow() allowing only a certain number of retries so you don't get stuck.

Jim.
not abandoned, just neglected.  Still need to work this project but overcome by events.
Thanks, guys.  That turned out to be a reasonable alternative for what I was looking for.