Localizar Excel y leer datos

Option Compare Database
Option Explicit

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetWindowText Lib "User32" Alias "GetWindowTextA" (ByVal Hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "User32" Alias "GetWindowTextLengthA" (ByVal Hwnd As Long) As Long

Private Const GW_HWNDNEXT = 2

Private Type udtHandle
    Hwnd        As Long
    WindowCaption   As String
End Type
Dim uHandle As udtHandle






Public Function TestFind(strQueBuscamos As String)

    Dim lhWndP As Long
    If GetHandleFromPartialCaption(lhWndP, strQueBuscamos) = True Then
        MsgBox uHandle.Hwnd & " - " & uHandle.WindowCaption
    End If

End Function

Private Function GetHandleFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As Boolean

    Dim lhWndP As Long
    Dim sStr As String
    GetHandleFromPartialCaption = False
    lhWndP = FindWindow(vbNullString, vbNullString) 'PARENT WINDOW
    Do While lhWndP <> 0
        sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))
        GetWindowText lhWndP, sStr, Len(sStr)
        sStr = Left$(sStr, Len(sStr) - 1)
        If InStr(1, sStr, sCaption) > 0 Then
            GetHandleFromPartialCaption = True
            lWnd = lhWndP
            Exit Do
        End If
        lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)
    Loop
    uHandle.Hwnd = lWnd
    uHandle.WindowCaption = sStr
End Function

' función para obtener un objeto COM desde un handle
Private Declare Function AccessibleObjectFromWindow Lib "OLEACC.DLL" _
                        (ByVal Hwnd As Long, _
                        ByVal dwId As Long, _
                        riid As GUID, _
                        ppvObject As Any) As Long
                   
' función extendida del API FindWindow
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
                        
' función para crear un GUID desde una "cadena" GUID
Private Declare Function IIDFromString Lib "ole32" _
                         (ByVal lpsz As Long, _
                         ByRef lpiid As GUID) As Long
                        
Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Private Const S_OK As Long = &H0
' GUID del objeto COM para Excel
Private Const IID_IDispatch As String = "{00020400-0000-0000-C000-000000000046}"
Private Const OBJID_NATIVEOM As Long = &HFFFFFFF0

Function GetXLapp(hWinXL As Long, xlApp As Object) As Boolean
Dim hWinDesk As Long, hWin7 As Long
Dim obj As Object
Dim iid As GUID
    
    ' obtenemos un GUID en variable iid según la cadena IID_IDispatch
    Call IIDFromString(StrPtr(IID_IDispatch), iid)
    ' obtenemos la ventana XLDESK de la ventana principal de Excel
    hWinDesk = FindWindowEx(hWinXL, 0&, "XLDESK", vbNullString)
    ' obtenemos la ventana contenedora del Workbook
    hWin7 = FindWindowEx(hWinDesk, 0&, "EXCEL7", vbNullString)
    ' obtenemos el objeto COM del Workbook en la variable obj
    If AccessibleObjectFromWindow(hWin7, OBJID_NATIVEOM, iid, obj) = S_OK Then
        ' devolvemos el objeto Application del Workbook
        Set xlApp = obj.Application
        GetXLapp = True
    End If
    
End Function