Simple Example to Explain Defining all Open Workbooks

Assume:

1. Launch Excel. File > Open "Test.xlsm". From Excel, File > Open "Test2.xlsm". From Excel, File Open "Test3.xlsm"
2. Start > Launch new instance of excel. File > Open "Test4.xlsm", File > Open "Test5.xlsm".

1 instance of excel with 3 "open" workbooks, another separate instance of excel with two "open" workbooks".

How do I properly identify each of the files for manipulation?

It is easier to open the workbook via a macro. You can assign the Object after each is opened:

Sub alvaro()
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
Workbooks.Open "C:\TestFolder\name3.xlsm"
Set wb1 = ActiveWorkbook
Workbooks.Open "C:\TestFolder\Text.xlsm"
Set wb2 = ActiveWorkbook
Workbooks.Open "C:\TestFolder\Xporter.xlsm"
Set wb3 = ActiveWorkbook
End Sub

if you know the full paths to each file you can use getobject to return a reference to them

if not you will require api calls to locate each excel window (XLMAIN) then iterate the workbook window handles within-you can use the accessibility objects to convert a window handle to an automation object

if you know the full paths to each file you can use getobject to return a reference to them

if not you will require api calls to locate each excel window (XLMAIN) then iterate the workbook window handles within-you can use the accessibility objects to convert a window handle to an automation object

That's one of those sentences where I understand all the words, or at least the great majority, but, joined together, it's just like a really loud buzzing in my head.

Care to elucidate?

Regards, TMS

+1 @ TM. I need to see a sample script to understand the API calls.

Expanding on OP, the scenario is driven off a vbYesNo whereby user is asked if the data they want to retrieve is in an open workbook (which theoretically could be an open workbook in "active" instance of Excel, or another instance, which may include one or many other workbooks); or, if "no", .GetOpenFileName to a closed workbook.

I have the closed workbook via ADO method working. I am now trying to understand how to ask the user to pick which of all open workbooks, despite instance of excel, contains their data, set a path, and then consume.

gee I was hoping nobody would ask ;-)

I don't have complete code to hand for that-but can probably put it together at lunch time if work clears a little

this is the code to get an Application object from a workbook handle (orig from stackoverflow I think)

Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long 
Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long 
 
Type UUID 'GUID
    Data1 As Long 
    Data2 As Integer 
    Data3 As Integer 
    Data4(7) As Byte 
End Type 
 
Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}" 
Const OBJID_NATIVEOM As Long = &HFFFFFFF0 
 
Public Function GetExcelObjectFromHwnd(ByVal hWnd As Long) As Excel.Application 
     
     ' requires the handle to a workbook (i.e. EXCEL7 class) window
     
    On Error Goto MyErrorHandler 
     
    Dim iid As UUID 
    Call IIDFromString(StrPtr(IID_IDispatch), iid) 
     
    Dim obj As Object 
    If AccessibleObjectFromWindow(hWnd, OBJID_NATIVEOM, iid, obj) = 0 Then 'S_OK
        Dim objApp As Excel.Application 
        Set GetExcelObjectFromHwnd = obj.Application 
    End If 
     
    Exit Function 
     
MyErrorHandler: 
    MsgBox "GetExcelObjectFromHwnd" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description 
End Function

then you just need a loop with findwindowex looking for XLMAIN windows then within each get the XLDESK window and finally one of the child EXCEL7 windows which you can pass to that function

easy as pie :-)

Really? Sigh.

And, when the fairy dust settles ...

if you're lazy you could also automate word and use its Tasks collection ;-P

like so-including proper attrib to stackoverflow. needs a reference to the scripting runtime as written but could easily be late bound

Private 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
Private Declare Function GetClassName Lib "user32" Alias "GetClassNameA" _
                                      (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
                                       (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
' based on code from StackOverflow here:
' http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel
Declare Function IIDFromString Lib "ole32" (ByVal lpsz As Long, ByRef lpiid As UUID) As Long
Declare Function AccessibleObjectFromWindow Lib "oleacc" (ByVal hWnd As Long, ByVal dwId As Long, ByRef riid As UUID, ByRef ppvObject As Object) As Long

Type UUID   'GUID
   Data1                 As Long
   Data2                 As Integer
   Data3                 As Integer
   Data4(7)              As Byte
End Type

Const IID_IDispatch      As String = "{00020400-0000-0000-C000-000000000046}"
Const OBJID_NATIVEOM     As Long = &HFFFFFFF0
Sub ListXLWorkbooks()
    Dim vWorkbooks
    vWorkbooks = GetXLWorkbooks
    For n = LBound(vWorkbooks) To UBound(vWorkbooks)
        MsgBox vWorkbooks(n).Name
    Next n
End Sub

Private Function GetXLWorkbooks()
   Dim hWndMain          As Long
   Dim hWndDesk          As Long
   Dim hWnd              As Long
   Dim lRet            As Long
   Dim y                 As Long
   Dim sText           As String
   Dim oWb            As Workbook
   Dim oDic        As scripting.Dictionary
   Dim aWorkbooks()

   hWndMain = FindWindowEx(0&, 0&, "XLMAIN", vbNullString)
   If hWndMain <> 0 Then
      Set oDic = CreateObject("Scripting.Dictionary")
      Do
         hWndDesk = FindWindowEx(hWndMain, 0&, "XLDESK", vbNullString)
         Do While hWndDesk <> 0
            hWnd = FindWindowEx(hWndDesk, 0, vbNullString, vbNullString)
            Do While hWnd <> 0
               sText = String$(100, Chr$(0))
               lRet = GetClassName(hWnd, sText, 100)
               If Left$(sText, lRet) = "EXCEL7" Then
                  sText = String$(100, Chr$(0))
                  lRet = GetWindowText(hWnd, sText, 100)
                  ' add to collection
                  If lRet > 0 Then oDic.Add CStr(hWnd), GetExcelObjectFromHwnd(hWnd)
               End If
               hWnd = FindWindowEx(hWndDesk, hWnd, vbNullString, vbNullString)
            Loop
            hWndDesk = FindWindowEx(hWndMain, hWndDesk, vbNullString, vbNullString)
         Loop
         hWndMain = FindWindowEx(0&, hWndMain, "XLMAIN", vbNullString)
      Loop While hWndMain <> 0
        If oDic.Count > 0 Then
            ReDim aWorkbooks(1 To oDic.Count)
            For y = 1 To oDic.Count
                Set aWorkbooks(y) = oDic.Items(y - 1)
            Next y
             GetXLWorkbooks = aWorkbooks
            
        End If
   End If

End Function
Public Function GetExcelObjectFromHwnd(ByVal hWnd As Long) As Excel.Workbook

' requires the handle to a workbook (i.e. EXCEL7 class) window

   On Error GoTo MyErrorHandler

   Dim iid               As UUID
   Call IIDFromString(StrPtr(IID_IDispatch), iid)

   Dim obj               As Object
   If AccessibleObjectFromWindow(hWnd, OBJID_NATIVEOM, iid, obj) = 0 Then   'S_OK
      Set GetExcelObjectFromHwnd = obj.Parent
   End If

   Exit Function

MyErrorHandler:
   MsgBox "GetExcelObjectFromHwnd" & vbCrLf & vbCrLf & "Err = " & Err.Number & vbCrLf & "Description: " & Err.Description
End Function

@JP: oh, now you're taking the p.... mickey

:-)
hey it wasn't me that decided only one instance of excel should get registered in the ROT