How can I activate an open workbook by macro in seperate instances of Excel

[Launchnet]

Thanks everyone . . . I'd really appreciate if we can take a new look at this request. I have never found an answer.

I have a program that has all Excel Workbooks in seperate instances of Excel. There is a very sound reason for doing this.

The user has maybe 3 to 10 workbooks open. There are times when a workbook is active and has a Macro Link to open one of the already open workbooks.

When the user clicks the link, they naturally get an error message stating that the workbook is already open. Then they have to close the error msg and click on the Macrosoft Tab and look thru the list of open workbooks and then click the one they are looking for. Additionally, in this Menu Program the user really doesn't even have to know the name of the various workbooks.

I hope everyone will believe me when I state that this program works berautifully. Right now I am simply cleaning up and making a few little things work better.

QUESTION When the user clicks on a macro link that is to open a workbook that is already open, how - On error - can I have the macro continue on and activate the requested workbook - - - Please remember they are all in separate instances of Excel.

P.S. Since the code I'm using can determine if the requested workbook is already open, I think there has to be a way to activate that workbook.

[Andy Pope]

Not sure of the exact code but you would need to iterate through the running processes. Determine which are Excel and then, if possible, create a reference to that instance and then iterate through the open workbooks.

On the face of it I think you may be better off writing your code so it can run in 1 instance of excel. You can create a function that returns a reference to a named workbook, either by referencing an open instance of the workbook or opening it.

[Launchnet]

Thanks for your input Andy . . .

I have evaluated this seriously and have come to the conclusion that seperate instances of Excel is far better. When you come to users that are using 2 or more monitors it really is much better.

If more people realized the benefits of 2 monitors, many would be out purchasing a second monitor today. I wouldn't even try to get along without my second monitor.

Thanks

Matt @ Launchnet
I experience God's peace of mind daily.

[Leith Ross]

Hello Matt,

I have written some macro that will let you select and instance of Excel and and activate it. I don't have multiple monitors to test the code. In theory it should work. I have attached a sample workbook with the macros installed. If you could test this out and let me know the results, it would be appreciated.

Macro to Find Instances of Excel

Please Login or Register  to view this content.

Macro to Activate the Application's Window

Please Login or Register  to view this content.

[Launchnet]

Hi Leith . . .

Wow ! For some reason I knew that you could do this.

Thanks beyond Thanks.

Hopefully, over the week-end I will load this and test it.

I will advise on my results just as soon as I can get to it.

[Launchnet]

Hi Leith
I don't know how to activate the macro. All I see are Functions and Privates and I don't think I can start with any of these.

I also see 1 sub . . .

Please Login or Register  to view this content.

and I can't start the procedure with this.

2 questions . . .
First, how do I start the procedure ?
Second, I don't see how I can specify how to activate a certain workbook that is currently open ?

Can you please give me a few tips.

[Launchnet]

Hi again
Just learned something new. I finally found your excel spreadsheet that you attached. Interesting. Now I know how to activate the procedure.

You have a new screen that pop's up and I think that you intend to have the operator enter the name of the workbook that is to be activated and have focus.

The operators do not know the name of the workbook they are opening, therefore, I need to hard code the workbook name. Each workbook has a sheet tab named Setup where I place info such as workbook names etc. Could I have the workbook name "MattsSheet.xls" example, typed into cell A1. Could you then get the sheet to be activated from this cell A1 ? I do this on another application to open specific workbooks or websites.

When the macro button is clicked, the routine automatically picks up the workbook name from cell A1 on sheet tab Setup and then continue on until the chosen workbook is displayed.

I hope this makes sense.

[Leith Ross]

Hello Matt,

The macro only activates workbooks that are already open. A workbook can be opened if the path and file name are in a cell. If no path is given, Excel will assume the file is in the current directory. I am not clear on what you want to do. Can you give me a short example?

[michelxld]

Hi,

if you know the full path of the workbook in the other instance, you can manage it with GetObject :

Please Login or Register  to view this content.

Regards
michel

[Launchnet]

Hi michelxld

Your suggestion is appreciated, but in checking it out, it does find the file in the path, but doesn't do what I need (as far as I can tell).

I will be posting a reply to Leith Ross very soon. I believe he is on the right path from what I can see.

I am going to give him a good description of exactly what should happen.

If you care to, please read my reply to him.

I was working on the reply when your suggestion came up.

[Launchnet]

Hi Leith

I'm going to do my best to describe what the procedure is to do, along with names of workbooks etc. Truly hope that I can explain it good.

WB = Workbook . . . shorter to type
All workbooks are in separate instances of Excel

Example:

I have 10 WB's open.
The Active WB name is "Matt10.xls" and it has a macro that when clicked, will Activate one of the other 9 WB's.

On my "Setup" Sheet Tab of the Active WB, I have the WB name, that I want to Activate, already typed into Cell A1 . . . Such as: "Matt9.xls". This WB needs to be activated, from the already open WB's, and have the focus.

2 Things I overlooked in my original description . . .
A . . . It would be nice that if for some reason a user would have closed "Matt9.xls", that this WB would automatically be opened. The path to this file could be stored in Cell A2 on the SetUp Tab.

B . . . After the WB "Matt9.xls" is activated, I need to have the WB "Matt10.xls" closed (no need to save).

I would also like the WB "Matt10.xls" shown on the Setup Tab in cell A3 in WB "Matt10.xls". This would give you the name of the WB that will be closed. This would help me if I have to make a change on which WB is to be activated or closed.

A1 Matt9.xls . . . WB To be activated
A2 C:\mymenu\Matt9.xls . . . Path to closed WB
A3 Matt10.xls . . . WB To be closed

ADDED INFO: Users do not need to know what files are being opened or what files are open.

NOTE: When the run of the Macro is completed, the WB "Matt9.xls" is Displayed and has the focus.

This WB also has the same macro and it knows from it's "Setup" Sheet Tab which WB is to be activated and which WB is to be closed when this WB's macro is clicked . . . Just the same as WB 10. This would be the same for all open WB's that are used in this application.

Naturally, the numer of WB's could increase or decrease as need. Generally it wouldn't change after the 1st demo construction has been completed. The increase or decrease in the number of WB's is if I need to construct a completely different demo, it could have different numbers of WB's.


This part of the program is a demonstration, completely unlike MS PowerPoint. It works in conjunction with my menu program, which has been completed, or could be used separately.

[Leith Ross]

Hello Matt,

To answer your question about the cell containing the name of the workbook to activate, yes the macro can be changed to do this. Having the workbooks path in another cell is fine.

I am still a little confused. If you have 10 workbooks open, each in a separate instance of Excel, how did all 10 get opened in the first place? How do determine when to close a workbook?

[Launchnet]

Hi again . . . Sorry, I have been very busy. I also sent you a private.

Answer to your question:
The 1st WB is opened automatically by clicking on a macro from my Menu program. This 1st WB is a list of various presentations that I have developed in Excel which work great. All WB's have a Back Button that closes the Active WB, using Application.Quit It also has a Next button, which opens the next WB and so on. This works OK, but has two problems.

A - To get completely out of the presentation the user has to click the Back button once for every WB open, as every WB that is opened, stays open.

B - If the user is playing around, they can Close 1 or more of the WB's and then it is a little confusing.

CURRENTLY

I have a second sheet Tab named "Setup" in each Workbook. On this "Setup" Worksheet, I have these values already entered in the following locations.

I will start with WB 2 being already open. I point out that all WB's have a Sheet Tab named "Setup".

On the "Setup" Sheet Tab, I have the following cells populated.

. . . Cells . . .
A1 Matt3.xls . . . . . Next WB to go to . . . . . . . Next Button
A2 Matt1.xls . . . . . Return to Previous Cell . . . Back Button
A3 Matt2.xls . . . . . Current Active WB . . . . . . Active WB
A4 C:\MyMenu\ . . . Path to Previous WB . . . . . Path to WB's


IDEALLY

Macro 1 . . . Back Button
I would have the Back Button open the WB shown in Cell A2, using the path in Cell A4, then close the active WB shown in Cell A3.

Macro 2 . . . Next Button
Then, I would have the Next Button open the WB shown in Cell A1 and close the WB in Cell A3

Macro 3 . . . Close Button
When the person making the demonstration - user - could click the Close Button and since that is the only open WB, it would only close that WB by using Application.Quit.

This would only require macros 1 & 2, as macro 3 is already done.

Sorry about the changes, but I guess that I hadn't sufficiently analysis'd it good enough before.

[Launchnet]

Bump No Response

[Leith Ross]

Hello Matt,

I haven't forgotten about you. I am still thinking about how to best approach this. I see what you want to do. If the user does everything as planned then there are no problems. However, we both know that won't be the case. I have an idea but need to do some testing before presenting it. It seems sound in theory. The road from theory to praxis is usually a bumpy one. I'll post back soon.