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:

Run-time error '-2147221020(800401e4)':

Automation error

Invalid Syntax

<tbody>
</tbody>

------------
Would appreciate any help/insight on this as we import a lot of data like this from the case management system, thanks
M

 

[Jaafar Tribak]

As I said, it looks as if the second excel instance doesn't register itself in the ROT.

I have done some research and it's been reported that by switching to the second instance, the latter registers itself.

See if this alternative works for you :

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    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 SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    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
#End If

Private Const WM_ACTIVATEAPP = &H1C

  
Public Function GetRemoteBook(ByVal WorkbookName As String) As Workbook
    #If VBA7 Then
        Dim hwnd As LongPtr
    #Else
        Dim hwnd As Long
    #End If
    
    Dim t As Single
    
    hwnd = FindWindowEx(0, hwnd, "XLMAIN", vbNullString)
    Do While hwnd
        hwnd = FindWindowEx(0, hwnd, "XLMAIN", vbNullString)
        If hwnd <> Application.hwnd Then
            Call SendMessage(hwnd, WM_ACTIVATEAPP, 1, ByVal 0)
        End If
        DoEvents
    Loop
    
    t = Timer
    Do
        DoEvents
    Loop Until Timer - t >= 1
    
    Set GetRemoteBook = GetObject(WorkbookName)
End Function

-Usage in your code :

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


End Sub

 

[themick]

Stops at this line:

Set GetRemoteBook = GetObject(WorkbookName)
End Function

With this error message:

Run-time error '-2147221020 (800401e4)':
Automation error
Invalid syntax

Thank you -

 

[Jaafar Tribak]

Ok- Did you download the ROT Viewer exe from the link I posted ?

Try running the ROT exe and see if Book1 shows up on the list of registered objects .

 

[themick]

Ran it, only the audit_billing.xlsm file appears.

 

[themick]

Ran it, only the audit_billing.xlsm file appears.

Also - I did the same, ran the ROT exe file on same file but on the Windows 7 machine with Access 2010 and the 'Book1' does show up .​

 

[Jaafar Tribak]

Ran it, only the audit_billing.xlsm file appears.

So it is now confirmed that the other instance doesn't register itself in the ROT that's why GetObject doesn't work.

We have tried sending the WM_ACTIVATEAPP and that didn't work to force the other instance to register.

The only thing that I can think of is to use an addin which would be loaded in the second instance .

The addin would have some API code that will register book1 as soon as it is open .

Obviously, this is a more involved and roundabout approach but I think it should work.

ps-
What happens if you switch from the current excel instance to that of book1 with the mouse or with ALT+TAB and then look at the ROT Viewer again after clciking the Update menu in the rot viewer ? Does that cause the second instance to register ? please, give that a try and let me know.

 

[themick]

So I did several things here:

First, on this Windows 10 machine, I have both Office 2010 and Office 2016 installed. 2016 is the registered default set of apps so any Excel file will cause the 2016 version to open.
I had the software create a new Book1 in 2010 and used the ROT viewer - it is there and registered.

Then I closed everything, ran a new Book1 and tried it with Excel 2016; the ROT viewer does not see it.

I then went to the Control Panel/Uninstall and did a 'Change' on the 2010 setup and had it completely remove Excel 2010 as an option so now no longer exists.
Rebooted the machine, tried just opening a blank 2016 spreadsheet and it hung.
Did a repair in the control panel of the 2016 version and after that, Excel will now open without any issues.
So now I went back and reran the case management software and created a new Book1.
Ran the ROT viewer and still - it does not see it. I even tried just entering data in it to force a change of the spreadsheet but still no joy.

So this is what we know:
Your code works as does mine but not if the file is not viewable (ROT viewer results).
This works intermittently with Excel 2010 with Windows 10 and not at all with Excel 2016 and Windows 10.
This works with both Excel 2010 and 2016 with Windows 7.

So the issue is, Windows 10 is not registering it when created so not viewable thus neither of our code works....

Any suggestions or modifications to Windows 10 am willing to make if you have any.
Thank you for the time and input you have provided - have learned a lot.

 

[Jaafar Tribak]

Hi and thanks for the explanation.

I use Windows 10 64 bits Office 2010 64bits and I have no issue using GetObject.

I did suspect you may have different versions of office installed and that could be the issue but you have uninstalled office 2010 and still no joy.

Maybe there is a registry setting somewhere that needs to be changed which we don't know about.

I'll post later the code for the Addin approach I mentioned earlier on.

 

[themick]

Okay, thank you for your time. Very much appreciated.

 

[Jaafar Tribak]

Okay, thank you for your time. Very much appreciated.

1- Open a new workbook and place the following code in its Workbook Module :

Option Explicit

Private WithEvents appEvents As Application

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(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 CLSIDFromProgID Lib "ole32.dll" (ByVal ProgID As LongPtr, rclsid As GUID) As Long
    Private Declare PtrSafe Function RegisterActiveObject Lib "oleaut32.dll" (ByVal pUnk As IUnknown, rclsid As GUID, ByVal dwFlags As Long, pdwRegister As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function CLSIDFromProgID Lib "ole32.dll" (ByVal ProgID As Long, rclsid As GUID) As Long
    Private Declare Function RegisterActiveObject Lib "oleaut32.dll" (ByVal pUnk As IUnknown, rclsid As GUID, ByVal dwFlags As Long, pdwRegister As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private Const ACTIVEOBJECT_WEAK = 1
Private OLEInstance As Long


Private Sub Workbook_Open()
    Set appEvents = Application
End Sub

Private Sub appEvents_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set appEvents = Application
End Sub

Private Sub appEvents_NewWorkbook(ByVal Wb As Workbook)
    If UCase(Wb.Name) = "BOOK1" Then
        Call AddToROT(Wb, "Excel.Sheet")
    End If
End Sub

Private Sub AddToROT(ByRef TargetObject As Object, ByRef ProgID As String)
    Dim tGuid As GUID
    If Not Nothing Is TargetObject Then
        If CLSIDFromProgID(StrPtr(ProgID), tGuid) = 0 Then
           Debug.Print RegisterActiveObject(TargetObject, tGuid, ACTIVEOBJECT_WEAK, OLEInstance)
        End If
    End If
End Sub

2- While on the VBE, goto the Properties window and set the IsAddin Property to TRUE.
3- Save the workbook as an addin.. Something like (AddToROT.xlam).
4- Install the Addin via Addins Dialog (Developper Tab > Addins Menu).
5- Close Excel.

Now every time excel is launched, if a new workbook with the default name of Book1 is opened, the addin code should pick it up and register it in the Running Object Table so you can later use the VBA GetObject function to retrieve a reference to it.

I hope this method works.