Need help in GetObject/CreateObject excelapp from Attachmate

hellebore08

Board Regular
Joined
May 31, 2011
Messages
77
Hi,

I have a macro from attachmate that transfers data to an excel file. My problem is that i can't get these lines to work correctly (code that I use from Attachmate Extra!).

Code:
Set obj = Getobject("H:\ultimate.xls")
 
if obj is nothing then
set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="H:\ultimate.xls"
end if
 
obj.visible = true
What I need is to attach the macro to an existing excel file - if the excel is already open. And if it is not open, i want the macro to create a new instance of excel and then attach to the newly opened file.

What it does so far is the getobject only but is not creating a new instance of excel when it is not opened. Hope you can help me.. thanks! ;)

I've tried adding an errorhandler but found no luck to make it work. Below is my code with errorhandler:

Code:
On Error GoTo openerrorh
 
Set obj = Getobject("H:\ultimate.xls")
 
obj.visible = true
 
exit sub
 
openerrorh:
Set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="H:\ultimate.xls"
 
end sub
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,584
Office Version
2013
Platform
Windows
Code:
Set obj = Getobject("H:\ultimate.xls")
 
if obj is nothing then
set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="H:\ultimate.xls"
end if
 
obj.visible = true
One thought is (if I remember correctly) that GetObject is usually like this (with the first empty argument):
Set obj = Getobject(, "H:\ultimate.xls")
Don't know what that does though.

Another is that if you haven't declared your variables and the object isn't set it might be "empty" rather than nothing.

So you could *try*
Code:
Dim obj As Object
Set obj = Getobject(, "H:\ultimate.xls")
 
if obj is nothing then
set obj = CreateObject("Excel.Application")
obj.Workbooks.Open FileName:="H:\ultimate.xls"
end if
 
obj.visible = true
My personal preference is to always create objects. I don't care if its a new instance or not ... if I'm automating Excel in code I usually run it in the background.
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
This should work properly:
Rich (BB code):

  Dim obj As Object
  On Error Resume Next
  Set obj = GetObject(, "Excel.Application")
  If Err Then Set obj = CreateObject("Excel.Application")
  On Error GoTo 0
  obj.Workbooks.Open Filename:="H:\ultimate.xls"
  obj.Visible = True
 

hellebore08

Board Regular
Joined
May 31, 2011
Messages
77
Thanks everyone!..

I will try that ZVI when I get back to work..

hmm, just noticed in your code.. will this attach to an existing "H:\ultimate.xls" file? in a line of your code it says..
Code:
Set obj = GetObject(, "Excel.Application")
I hope it will not attach to other opened excel file (i guess the code is not generally attaching to any opened excel.application).. or i guess it will work because of the path though this line is below the createobject
Code:
obj.Workbooks.Open Filename:="H:\ultimate.xls"
*im using a code for Attachmate Extra!.. i hope "Err" code is readable.. I went to mrexcel because i see that most VB code of Attachmate Extra is same with those from Excel.

about my code, im not sure where i got it wrong..

@xenou, i think i got that getobject from the help file of Attachmate Extra! and also the site, they're the same..
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
GetObject(, "Excel.Application") tries to find & return already activated Excel application object.
If active Excel application is not found then new one is created by CreateObject.
An extra time (few seconds) is required for creating of new Excel application relative to getting of the active one.
If this extra time is not critical then as Xenoe has indicated you can just skip GetObject part and use only CreateObject.

After attaching of Excel application object to obj variable the workbook is loaded by the aid of obj.Workbooks.Open

Instead of Err you can use Err.Number if required. It’s the same because the default property of Err object is .Number
 

hellebore08

Board Regular
Joined
May 31, 2011
Messages
77
Hi everyone,

Have tried the code above and it worked nice. However, I realized that this is not what I really need. I apologize that I have not correctly stated my problem.

The above code does open an instance of the file when its not present. But I would like the macro to open up the file in a NEW INSTANCE of Excel when it is not present, not to open the file in an existing excel application. I need it to be opened in a new window because I'm afraid the macros there will stack with the macros of other opened excel application. Hope you could still help me, thanks everyone.

I need the macro to search and attach to an opened "H:\ultimate.xls" file. If it is not opened, it must open up a new excel window and open there the file.
 

hellebore08

Board Regular
Joined
May 31, 2011
Messages
77
Hi,

This is my new code that STILL DOESN'T work.. :(
Code:
Dim obj As Object
Const EXCELPATH As String = "H:\ultimate.xls"
 
On Error Resume Next
 
Set obj = GetObject(EXCELPATH, "Excel.Application")
If Err Then
Set obj = CreateObject("Excel.Application")
On Error GoTo 0
obj.Workbooks.Open Filename:="H:\ultimate.xls"
end if
 
obj.Visible = True
Need help.. :biggrin:.. thanks!
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,584
Office Version
2013
Platform
Windows
But I would like the macro to open up the file in a NEW INSTANCE of Excel when it is not present, not to open the file in an existing excel application.
Do not use GetObject if you always want a new instance.
Code:
Dim obj As Object
Set obj = CreateObject("Excel.Application")
obj.Workbooks.Open Filename:="H:\ultimate.xls"
obj.Visible = True
If this still isn't working post back with a description of what the error is. I'm afraid I don't know what attachmate is - sounds like a slightly modified version of VBA (?).
 

Forum statistics

Threads
1,085,715
Messages
5,385,419
Members
401,945
Latest member
Paul82

Some videos you may like

This Week's Hot Topics

Top