VBA

[VCDHunter]

I am trying to switch between two different instances of excel (they show up as seperate spreadsheets on the taskbar) but the code I have been given to try doesn't work -

Dim OtherSheet As Object ' Variable to hold reference

On Error Resume Next
Set OtherSheet = GetObject(, "Excel.Application")
Set OtherSheet = GetObject("U:\Decision Support\VB Testing\File swap2.xls").Activate ' <<<< FULL FILE PATH HERE
OtherSheet.Application.Visible = True
Set OtherSheet = Nothing ' Release reference to the application and spreadsheet

It just throws up an error 424 Object Required at the second get object line. Can anyone point me in the right direction ?

[mats.hogberg]

It seems a littel odd to me, even if I'm no VBA guru:
First you create an excel application and assign it to othersheet
Then you try to assign the result from .activate to othersheet
Doesn't make sense.
Disregarding first line, I'd try
Set OtherSheet = GetObject("U:\Decision Support\VB Testing\File swap2.xls")
OtherSheet.Activate
...and the rest.
The first line is wasted, as the second immediately changes the first assignment.

/Mats

[tekkieman]

The problem is that no object has been created. GetObject is used when you already have an existing object. Instead of the first line using GetObject, set it to CreateObject. Then the object will exist when the second line is executed. Another way would be to trap the error 429. (Psuedo code) If 429 then Set blah - CreateObject(....

This way, if the code gets executed several times in the app, it will work if the object already exists, or create it if it doesn't.

EDIT: Sorry, here's a better example.

    Dim olApp As Object
   
    On Error Resume Next
    Set olApp = GetObject(, "Excel.Application")

    If Err.Number = 429 Then
        Set olApp = CreateObject("Excel.application")
    End If

[VCDHunter]

Cheers guys !

[tekkieman]

Anytime. Did you get it squared away?

[VCDHunter]

Anytime. Did you get it squared away?

Kind of - I went away and looked at a different way of doing things and it now works.