Need help in GetObject/CreateObject excelapp from Attachmate

[hellebore08]

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!).

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:

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

 

[hellebore08]

Bump

 

[Kenneth Hobson]

Use a method similar to what I did with MSWord in this thread. http://www.mrexcel.com/forum/showthread.php?t=333200

 

[xenou]

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*

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]

This should work properly:

  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]

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..

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

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]

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]

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]

Hi,

This is my new code that STILL DOESN'T work..

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.. .. thanks!

 

[xenou]

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.

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 (?).