Retrieve data from unsaved Excel workbook: GetObject("Book1")

[themick]

The office has a case management system that will export the filtered data to Excel. By default, it will create a workbook called 'Book1' and place all data filtered to 'Sheet1".
Up until recently, I have used the code below on a Windows 7 machine using Excel 2010. We are starting the upgrade process to prepare for end of life Windows 7 so am testing code in the Windows 10 environment.

The VBA macro that did work will error now in Windows 10. I tried running same code in Windows 10 using Excel 2016 but same problem. Here is the code:

SUB IMPORT_DATA ()

    Dim oApp As Application
    Dim oWb As Workbook
    
    
    Set oWb = GetObject("Book1")
    Set oApp = oWb.Parent
    

    Windows("Audit_Billing.xlsm").Activate
    
'
    With Workbooks("AUDIT_BILLING.xlsm").Worksheets("DATA") '<--| sheet name
    Intersect(.Range(.Rows(1), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("A:I")).ClearContents 
    End With
'RESTOF CODE....
END SUB

The macro, excecuted from workbook AUDIT_BILLING.xlsm, pulls the data from the unsaved book and copies it to a sheet called 'DATA' in the AUDIT_BILLNG workbook.
This now errors out at the line:
Set oWb = GetObject("Book1")

With this error message:

[TABLE="width: 158"]
<tbody>[TR]
[TD="width: 211, bgcolor: transparent"]Run-time error '-2147221020(800401e4)':
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Automation error
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Invalid Syntax
[/TD]
[/TR]
</tbody>[/TABLE]
------------
Would appreciate any help/insight on this as we import a lot of data like this from the case management system, thanks
M

 

[Norie]

Is 'Book1' open in a separate instance of Excel?

 

[themick]

Is 'Book1' open in a separate instance of Excel?

Yes, the case management software opens a new instance of Excel for "Book1"

Here is the complete set of code:

Sub PREP_DATA()
  
    Application.CutCopyMode = False
    Dim oApp As Application
    Dim oWb As Workbook
    
    
    Set oWb = GetObject("Book1")
    Set oApp = oWb.Parent
    
    Windows("Audit_Billing.xlsm").Activate
    
'
    With Workbooks("AUDIT_BILLING.xlsm").Worksheets("DATA") '<--| sheet name
    Intersect(.Range(.Rows(1), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("A:I")).ClearContents 
    End With

    GetObject("Book1").ActiveSheet.Range("A1:I1500").Copy
    Workbooks("AUDIT_BILLING.xlsm").Worksheets("DATA").Range("A1:A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
    
    Sheets("DATA").Select
    Range("J1").Select
    Call timeStamp  'PUTS TEXT VERSION OF TIMESTAMP IN CELL


    With oWb.ActiveSheet.Range("A1:I1500")
        .Offset(0, Range("A1:H1500").Columns.Count).Clear
    End With
    
        
   oWb.Close False
   oApp.Quit
    
End Sub

 

[Jaafar Tribak]

Can you double-check the name of the workbook (Book1) and see if it really is Book1 ?

 

[themick]

Can you double-check the name of the workbook (Book1) and see if it really is Book1 ?

I had the case management software create a new set of data and yes, the actual name is 'Book1'. If I do a File/Save As, it defaults to the name "Book1.xlsx".
I ran the macro 3 times after that, twice I go the same error and then the third time it worked, it imported the data. So ran it again an got mixed results of fail versus success.
I am new to VBA/Excel coding so am wondering if I am missing something that should be added that did work with the combination of Windows 7Pro/Office2010 versus now using Windows 10Pro/Office2016..?

 

[Jaafar Tribak]

I had the case management software create a new set of data and yes, the actual name is 'Book1'. If I do a File/Save As, it defaults to the name "Book1.xlsx".
I ran the macro 3 times after that, twice I go the same error and then the third time it worked, it imported the data. So ran it again an got mixed results of fail versus success.
I am new to VBA/Excel coding so am wondering if I am missing something that should be added that did work with the combination of Windows 7Pro/Office2010 versus now using Windows 10Pro/Office2016..?

Strange ! Not sure why you are getting mixed results .

Can you try the following API-based alternative function (GetRemoteBook) and see if it works for you :

1- In a standard Module :

Option Explicit 

Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type

#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
    Private Declare PtrSafe Sub AccessibleObjectFromWindow Lib "OLEACC.DLL" (ByVal hwnd As LongPtr, ByVal dwId As Long, riid As GUID, ppvObject As Any)
#Else
    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 Sub AccessibleObjectFromWindow Lib "OLEACC.DLL" (ByVal hwnd As Long, ByVal dwId As Long, riid As GUID, ppvObject As Any)
#End If
 
Private Const OBJID_NATIVEOM = &HFFFFFFF0
 
 
Public Function GetRemoteBook(ByVal WorkbookName As String) As Workbook

    #If VBA7 Then
        Dim lXLhwnd As LongPtr, lWBhwnd As LongPtr
    #Else
        Dim lXLhwnd As Long, lWBhwnd As Long
    #End If
    
    Dim IDispatch As GUID
    Dim oWb As Object
   
    With IDispatch
        .lData1 = &H20400
        .iData2 = &H0
        .iData3 = &H0
        .aBData4(0) = &HC0
        .aBData4(1) = &H0
        .aBData4(2) = &H0
        .aBData4(3) = &H0
        .aBData4(4) = &H0
        .aBData4(5) = &H0
        .aBData4(6) = &H0
        .aBData4(7) = &H46
    End With

    Do
        lXLhwnd = FindWindowEx(0, lXLhwnd, "XLMAIN", vbNullString)
        If lXLhwnd = 0 Then
            Exit Do
        ElseIf lXLhwnd <> Application.hwnd Then
            lWBhwnd = FindWindowEx(FindWindowEx(lXLhwnd, 0&, "XLDESK", vbNullString), 0&, "EXCEL7", vbNullString)
            If lWBhwnd Then
                Call AccessibleObjectFromWindow(lWBhwnd, OBJID_NATIVEOM, IDispatch, oWb)
                Set GetRemoteBook = oWb.Application.Workbooks(WorkbookName)
                Exit Do
            End If
        End If
    Loop
    Set oWb = Nothing
 
End Function

2- Code usage :

Sub PREP_DATA()
  
    Application.CutCopyMode = False
    Dim oApp As Application
    Dim oWb As Workbook
        
    Set oWb = [COLOR=#0000ff][B]GetRemoteBook[/B][/COLOR]("Book1")
    Set oApp = oWb.Parent
    
    Windows("Audit_Billing.xlsm").Activate
    
[COLOR=#008000]    ' Rest of your code etc .......

[/COLOR]
End Sub

Late Note:

The above assumes there are only two excel instances running ie: The instance you are running the code from and the instance where Book1 is opened.

If there more than two instances the code will need some tweaking.

 

[themick]

Thank you for your response. At this set of lines of code:

If lWBhwnd Then
Call AccessibleObjectFromWindow(lWBhwnd, OBJID_NATIVEOM, IDispatch, oWb)
Set GetRemoteBook = oWb.Application.Workbooks(WorkbookName)
Exit Do
End If

it stops at the line: Set GetRemoteBook = oWb.Application.Workbooks(WorkbookName)
with the error:
Run-time error '91':
Object variable or With block variable not set

M

 

[Jaafar Tribak]

Try this and tell us the exact output you get in the Debug window : (Changes are in blue)

Option Explicit

Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  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
    Private Declare PtrSafe Sub AccessibleObjectFromWindow Lib "OLEACC.DLL" (ByVal hwnd As LongPtr, ByVal dwId As Long, riid As GUID, ppvObject As Any)
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    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 Sub AccessibleObjectFromWindow Lib "OLEACC.DLL" (ByVal hwnd As Long, ByVal dwId As Long, riid As GUID, ppvObject As Any)
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
 
Private Const OBJID_NATIVEOM = &HFFFFFFF0
 
 
Public Function GetRemoteBook(ByVal WorkbookName As String) As Workbook

    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
        Dim lXLhwnd As LongPtr, lWBhwnd As LongPtr
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
        Dim lXLhwnd As Long, lWBhwnd As Long
    [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If
    
    Dim IDispatch As GUID
    Dim owb As Object
   
    With IDispatch
        .lData1 = &H20400
        .iData2 = &H0
        .iData3 = &H0
        .aBData4(0) = &HC0
        .aBData4(1) = &H0
        .aBData4(2) = &H0
        .aBData4(3) = &H0
        .aBData4(4) = &H0
        .aBData4(5) = &H0
        .aBData4(6) = &H0
        .aBData4(7) = &H46
    End With

    Do
        lXLhwnd = FindWindowEx(0, lXLhwnd, "XLMAIN", vbNullString)
        If lXLhwnd = 0 Then
            Exit Do
        ElseIf lXLhwnd <> Application.hwnd Then
            lWBhwnd = FindWindowEx(FindWindowEx(lXLhwnd, 0&, "XLDESK", vbNullString), 0&, "EXCEL7", vbNullString)
            If lWBhwnd Then
                Call AccessibleObjectFromWindow(lWBhwnd, OBJID_NATIVEOM, IDispatch, owb)
                    [COLOR=#0000ff][B]Dim i As Long
                    Debug.Print "Remote Application Name : "; owb.Application.Name
                    Debug.Print "************************************"
                    Debug.Print "Workbooks count in remote instance : "; owb.Application.Workbooks.Count
                    Debug.Print "************************************"
                    For i = 1 To owb.Application.Workbooks.Count
                        Debug.Print "Workbook(" & i & "): "; owb.Application.Workbooks(i).Name
                    Next i[/B][/COLOR]
                Set GetRemoteBook = owb.Application.Workbooks(WorkbookName)
                Exit Do
            End If
        End If
    Loop
    Set owb = Nothing
 
End Function

 

[themick]

Thank you, the error message is:
Runtime Error 91:
Object variable or With variable not set

The line highlighted by debug in red font below :

Dim i As Long
Debug.Print "Remote Application Name : "; owb.Application.Name
Debug.Print "************************************"
Debug.Print "Workbooks count in remote instance : "; owb.Application.Workbooks.Count
Debug.Print "************************************"
For i = 1 To owb.Application.Workbooks.Count
Debug.Print "Workbook(" & i & "): "; owb.Application.Workbooks(i).Name
Next i

 

[Jaafar Tribak]

Thank you, the error message is:
Runtime Error 91:
Object variable or With variable not set

The line highlighted by debug in red font below :

Dim i As Long
Debug.Print "Remote Application Name : "; owb.Application.Name
Debug.Print "************************************"
Debug.Print "Workbooks count in remote instance : "; owb.Application.Workbooks.Count
Debug.Print "************************************"
For i = 1 To owb.Application.Workbooks.Count
Debug.Print "Workbook(" & i & "): "; owb.Application.Workbooks(i).Name
Next i

That seems to indicate that AccessibleObjectFromWindow fails hence the oWb variable is Nothing.

To be honest, I am not sure why this doesn't work for you .

It looks as if the other workbook is never registered in the Running Object Table.

Do you have a ROT viewer to see all the registered objects on your machine ?

If not try downloading this exe and see if Book1 shows up on the ROT viewer list.