How do you refer to a workbook in a different instance 4

[Mack2]

I have one macro that needs to refer to a different workbook. The workbook is in a different instance. So if I use Workbooks("book1.xls").Activate, I get an "out of scope" error message.
THANKS!!!

 

[TonyJollans]

You must refer explicitly to the other instance ...

[tt][Excel_Instance_Ref].[Workbook_Ref].[Whatever][/tt]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Mack2]

Hi Tony,
What does the [Excel_Instance_Ref] represent? Because the file is already open. So I do not need to add the path. Does each instance have an index number or something to refer to? THANKS!!!

 

[PHV]

the file is already open
Have a look at the GetObject function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mack2]

I believe that function opens files. I currently have two workbooks open, in different instances. I need to close one workbook from the other workbook. Both workbooks are in different instances.

 

[TonyJollans]

Are you saying you have two instances of Excel open and you want to run some code in one instance which references the other but the only thing you know about the second instance is that it is open? This is a very strange situation and not one to which there is an easy solution. Can you give some more details please?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Mack2]

Everything you said is correct, except I do know the name of the workbook that I am trying to reference.

The NT Scheduler will open one workbook(A). At that time there will be another workbook open(B). Workbook (A) needs to reference Workbook (b). When I say reference the purpose of (A) is to run code that will close workbook (b).
And this is to make a long story short(I know..to late)

 

[Mack2]

Thanks for your help Tony....
I did try the GetObject function, and it did not close the workbook that is open. Below is some of my code;

Set MyXL = GetObject("HYTEKFINISHES.xls")

MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

'Save and close the file
Workbooks("HYTEKFINISHES.xls").Activate
Workbooks("HYTEKFINISHES.xls").Save
Workbooks("HYTEKFINISHES.xls").Close

 

[HughLerwill]

Surely;

With MyXL
.Workbooks("HYTEKFINISHES.xls").Activate
.Workbooks("HYTEKFINISHES.xls").Save
.Workbooks("HYTEKFINISHES.xls").Close
End With

 

[SkipVought]

   Set MyXL = GetObject("HYTEKFINISHES.xls")

    MyXL.Application.Visible = True
    MyXL.Parent.Windows(1).Visible = True
    
   'Save and close the file[b]
   with MyXL
      .Save
      .Close
   end with[/b]

Skip,

_[red][/red]

 

[Mack2]

IT WORKS! I am able to control one instance of excel from a different instance. It was the GetObject function I was missing. Thank you to everyone!!!!!!!!!!!