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

T

themick

New Member
Joined
May 26, 2018
Messages
48
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:
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
 
Last edited by a moderator:
Sort by date Sort by votes
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.
 
Last edited by a moderator:
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
​
 
Last edited by a moderator:
Upvote 0
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
 
Last edited by a moderator:
Upvote 0
Should be placed on the ThisWorkbook module where the red arrow is pointing:

 
Upvote 0
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.
 
Last edited:
Upvote 0
Ok-

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

Code: 
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
 
Last edited by a moderator:
Upvote 0
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?
 
Upvote 0
themick said:
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?
Click to expand...

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.

Code: 
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
 
Last edited:
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Button to import data from another workbook
Replies
5
Views
554
Lelewiwi
L
L
  • Solved
Importing specific cell data from one workbook to another
Replies
1
Views
400
rlv01
R
D
  • Question
Import and format txt file into open workbook.
Replies
4
Views
658
djdbg1
D
H
  • Solved
Excel VBA file importing data from several files located in same folder -> challenge to copy data only (rest is working perfectly)
Replies
3
Views
181
NdNoviceHlp
NdNoviceHlp
L
  • Solved
Importing data from one excel workbook to another
Replies
2
Views
526
londa_vba
L

Forum statistics

Threads
1,215,647
Messages
6,126,005
Members
449,280
Latest member
Miahr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top