Dale Fye
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.
() 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?
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
The problem is that the GetReferenceToXLApp() function is not returning an object (oXLApp), probably because the AccessibleObjectFromWindowAnybody, 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?
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
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.
What version of Office?
Jim.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks, anyway.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
Jim.
ASKER
not abandoned, just neglected. Still need to work this project but overcome by events.
ASKER
Thanks, guys. That turned out to be a reasonable alternative for what I was looking for.
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.