Excel Getobject error

[Jaya]

Hi,

i have the following code in VB.
Set loXLS = GetObject(, "Excel.Application")

Dim loXLS As Excel.Application
For i = 1 To loXLS.Workbooks.Count
If loXLS.Workbooks(i).FullName = psFile Then
Exit For
End If
Next i

When a excel file is already open, this fails. And the
loxls.workbooks.count is 0. Can you tell me what could be
the reason?
-Jaya

 

[Bill Lunney]

This should fail when Excel is NOT open. That's because the GetObject line
will generate an error if it can't find an instance of Excel. You're also
defining the loXLS object after calling GetObject. Try this:

Dim loXLS As Excel.Application

On Error Resume Next

Set loXLS = GetObject(, "Excel.Application")
If Err.Number > 0 Then
MsgBox "Excel is not running"
End
End If

For i = 1 To loXLS.Workbooks.Count
If loXLS.Workbooks(i).FullName = psFile Then
Exit For
End If
Next i


--

Regards,


Bill Lunney
www.billlunney.com

 

[Bill Lunney]

I remember using GetObject a long time ago and came up against problems when
multiple version of Excel are running. GetObject will return the first
instance it finds. If that isn't the one you're interested in there's
nothing you can do about it (as far as I know).

I used all sorts of wacky things like getting a handle to a specific
instance by way of the window caption (using the API) but ran into other
problems best not discussed here.

Bottom line is if you have only one instance of Excel open and the GetObject
method does not return it I don't know why. If there are multiple instances
open and your getting a handle to one you don't like you'll need another
approach. I can point you in the right direction if this is the case.




--

Regards,


Bill Lunney
www.billlunney.com

 

[jaf]

Jaya,
Move the dim statement before the set statement.

Dim loXLS As Excel.Application
Set loXLS = GetObject("c:\path\filename.xls") ' works if filename is open.
<<<<<<<<<<<<<<<<<<<<

For i = 1 To loXLS.Workbooks.Count
If loXLS.Workbooks(i).FullName = psFile Then
Exit For
End If
Next i

 

[jaya]

I solved the problem... There were two instances running.
For some reason it was not getting both the instances. I
killed one of the instance and it worked fine.
Thanks,
Jaya