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

 

[themick]

Thank you for that-
I do not save the excel book, right? I just save the VBE code as an excel add-in...?
when I do this, I get an error,
Compile error:
Only valid in object module

and line 2 from the top:

Option Explicit
Private WithEvents appEvents As Application

'WithEvents' is highlighted.

FOLLOWUP:
The first time I did this, your VBE code, don't know what I did but every new workbook opened in Excel was now named 'Book2'. when I ran the ROT program, both Book1 and Book2 appeared so I thought we were very close to getting this resolved. The only way I could get Excel to default back to Book1 was to delete your addin and my personal macro book. After I did that - yes everything now back to default name of 'Book1' but now the ROT no longer recognized any book being opened that was generated by the case management software.

 

[Jaafar Tribak]

The compile error you are getting means you are not adding the code to the correct module ... The code I gave you must be added to the ThisWorkbook Module of the Addin ie :

In the VBE, go to Project Explorer window and double-click on the module that reads ThisWorkbook - When you double-clcik a blank pane comes up on your screen - That's where you must put the code I gave you.

Also, do not forget to set the IsAddin Property to TRUE in the Properties window before saving the project as an AddIn.

Here is a step by step tutorial on how to create and install an addin

 

[themick]

Good evening,
I have followed the instructions on your link. I even recreated the add-in as outlined on this link:
https://trumpexcel.com/excel-add-in/
I watched several videos on youtube as to maybe I was missing something but I still continue to get the error out at this line:

Private ​

WithEvents

appEvents As Application​

I successfully created other add-ins following the same step by step process so I believe I am doing this correctly but continue to get the same result when attempting same with the code provided.
I have created the module as outlined and changed the IsAddin= to TRUE. I have saved it as both a 'Excel Addin' and an Excel 97-2003 AddIn (2 options offered in Excel 2016) but still no joy. I apologize if I have missed something here...
M

​

 

[Jaafar Tribak]

I am almost certain that you are not placing the code in the correct module otherwise you woudn't get that compile error.

Hold on a moment and I'll post an image of where the code should be placed

 

[Jaafar Tribak]

Should be placed on the ThisWorkbook module where the red arrow is pointing:

 

[themick]

Thank you. I was putting all of them in to Module1.
Completed the process, changed IsAddin=True, saved it as AddIn and activated it via the add-in Options under the File menu. Closed it, recreated a new file from the case management software and ran it. No Joy/Book1 still does not register in the IROTVIEW software.

if I open a new spreadsheet, it defaults to Book1 and the IROTVIEW software sees that. It is only in this environment, Book1 via the case management software, Windows 10 Pro/Office 2016 I see this result. I tested it on 3 other machines that have the same release and no joy there. I tested it on my home computer, that has a different version, 1709, and it does work. if there was a way to get you a file generated by this software I would. Thanks for all the help.

 

[Jaafar Tribak]

Ok-

Try this and tell me if you get 0 in Debug Window :

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_NewWorkbook(ByVal Wb As Workbook)
        Call AddToROT(Wb, "Excel.Sheet")
End Sub

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

 

[themick]

How should this be created - as an AddIn as well?
If as an Add-In, am I looking for the '0' in the IROTVIEW window or when I execute the macro that errors when it cannot find Book1?

 

[Jaafar Tribak]

How should this be created - as an AddIn as well?
If as an Add-In, am I looking for the '0' in the IROTVIEW window or when I execute the macro that errors when it cannot find Book1?

The same Add-In... Just replace the code you already added eralier on to the ThisWorkbook module of the Add-in with the following one, and tell me what value you get in the Msgbox when book1 is opened.

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_NewWorkbook(ByVal Wb As Workbook)
        Call AddToROT(Wb, "Excel.Sheet")
End Sub

Private Sub AddToROT(ByRef TargetObject As Object, ByRef ProgID As String)
    Dim tGuid As GUID
        If CLSIDFromProgID(StrPtr(ProgID), tGuid) = 0 Then
          [COLOR=#0000ff][B] MsgBox RegisterActiveObject(TargetObject, tGuid, ACTIVEOBJECT_WEAK, OLEInstance)[/B][/COLOR]
        End If
End Sub

 

[themick]

This is what I get:

Messagebox:
Run-time error ‘-247221010 (800401e4)’:
Automation error
Invalid syntax

Line highlighted in the VBE window (in red font)

Set GetRemoteBook = GetObject(WorkbookName)