Activate different workbook

I have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3

Currently I have Wk3 showing in the screen.

Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?

This is part of a longer macro, so I only need to know how to do above.

Good Morning Leith . . . It's good to hear from you. By the way, what country do you live in ? (England or thereabouts?)

Here is the Exact Code that I have tried: Workbooks("LinkMenu.XLS").Activate

It's the same as you suggest.

It does nothing.

I think it is because it is in a different instance of Excel.

I can test if the workbook is open or not, in a different macro, and that test works, but I've had no luck in activating a open workbook in a different instance of Excel.

Any comments will be appreciated.

Matt try this version,

Please Login or Register  to view this content.

Matt, you don't generally need to activate a workbook in VBA to work with it.

Simon & Leith

Well, I've tried the following 3 lines and they did not work:

Windows("MyMenu.XLS").Activate 'Error Msg is: Subscript out of range
Windows(""MyMenu.XLS"").Activate 'Compile error: Syntax error
Windows("'MyMenu.XLS'").Activate 'Subscript out of range


royUK

The reason why I need to activate the workbook is because I have a Menu System that needs to display the Wk1 workbook from the several that may be open at the time this macro is run.

I truly appreciate everyone's efforts. Any new ideas would be appreicated.

Just a shot in the dark but are you sure that MyMenu.xls is open? subscript out of range error (Usually 9) normally indicates that the sheet or workbook doesnt exist or isnt open!

Thanks for the Question.

For sure . . . It is open.

What error code are you getting?

They are posted 3 posts back

Matt Subscript out of range will usually have an error number, you haven't given any numbers at all, it would just help if you could!

This worked perfect for me:

Please Login or Register  to view this content.

Simon, here are the error messages & #

Windows("MyMenu.XLS").Activate.............#9
Windows("'MyMenu.XLS'").Activate.............#9

Windows(""MyMenu.XLS"").Activate.............Compile error: Expected: list separator or)
The File name word MyMenu is highlited

Wow! way to go Leith, thats some determination and i would imagine a very happy Matt.

I work out of my home office and I've just returned a short time ago. I've taken time to skim through your code. I think that if I fully understood what you've done, I could fill the entire page with WOW's. Thank you Leith.

Let me state the application briefly so you know where I am coming from.

As an Example:
Let's say I have 4 different Instances of Excel Workbooks open.

I will call them Wb1, Wb2, Wb3 and Wb4.

Wb3 is displayed. I want to close Wb3 and make Wb1 (or any other choice) be displayed when Wb3 is closed.

Now, your code is so far beyond me, that I don't know where to start to use your code.

In my code, I try to do the following: But, it doesn't handle closing Wb3 and displaying Wb1.

My code checks:
To see if Wb1 is open or not.
If not, it opens Wb1
Then it closes Wb3

If Wb1 is open, then I want to close Wb3 and display Wb1.

As you know, my code can not display Wb1 whenever Mb3 is closed.


LEITH . . .

I am sure your code will work, but I don't know how to incorporate it into my code. It looks to me as though I would have to go to your code at 2 different places, all depending on the IF Statement. Problem being I don't understand your code at all.

If this is too much to ask, I truly will understand.
I promise you the very 1st copy of my Menu System. It probably won't do you much good as a programmer, but I've had some very good comments from individuals and business users.



Below is my code: . . . Followed by a Function

'THIS MACRO IS ACTIVATED FROM "Kb3.XLS" WHICH IS DISPLAYED.

Please Login or Register  to view this content.

'The following 3 lines of code are used to go to cell A1 with "Freeze Panes" turned on so that when Kb3 is closed & saved, the cursor will be at A1 when re-opened later. Should not have any bearing on your code.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

'This is needed to open a new instance of Excel.
'Without it, the file is only opened as a new Window

Please Login or Register  to view this content.

'First I need to check to see if "Kb1xls" is open or not

Please Login or Register  to view this content.

'If "Kb1.XLS" is not open, then I have to open it in a NEW INSTANCE of Excel.
'This line of code opens the NEW INSTANCE of Excel

Please Login or Register  to view this content.

'This line of code makes the NEW INSTANCE of Excel visible.

Please Login or Register  to view this content.

'This line of code opens "Kb1.xls"

Please Login or Register  to view this content.

'PROBLEM 1

'HERE I NEED TO ACTIVATE (DISPLAY) "Kb3.XLS" SO THAT IT CAN BE CLOSED.
'THE PROBLEM HERE IS THAT IT DOES NOT DISPLAY "Kb3.xls" and therefore,
'the following closing code closes the wrong workbook.

Please Login or Register  to view this content.

'END PROBLEM 1


'This closing code works fine

Please Login or Register  to view this content.

'PROBLEM 2

'MY PROBLEM HERE IS THAT THE MACRO IS CLOSED, DUE TO THE WORKBOOK BEING CLOSED.
'What I need here is some way of always displaying "Kb1.xls" which is
'impossible since the macro is terminated. 3 or 4 workbooks could be open.

'WITH CORRECTED CODE, THEN, WHEN "Kb1.xls" IS DISPLAYED, THE MACRO IS FINISHED.
'END PROBLEM 2

Please Login or Register  to view this content.

'HERE THE WORKBOOK "Kb1.xls" WAS OPEN, SO
'THE PROGRAM AUTOMATICALLY CLOSES "Kb3.xls"
'The following code works properly for closing the workbook.

Please Login or Register  to view this content.

'Here is one of the ideas I tried, but failed. IT DOESN'T WORK.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

'PROBLEM 3

'And again here, I need the the workbook "Kb1.xls" displayed
'after the workbook "Kb3.xls" has been closed.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Can this code be called from different sheets, as Kb1 will always be open and possibly the code could reside there ?

Leith, I think I taxed my brain in just trying to explain this procedure. I hope this has made sence.
Many thanks in advance if you choose to take this assignment. If you choose not to, this message will self destruct in 7 seconds.

A poor attempt to Quote: "Mission Impossible"

Yes, the workbook being closed needs to be saved.

Could this routine be called from any open workbook that is being closed in this program?

I would be nice to have only one code, which would be called by clicking the "Close Workbook" macro in that workbook. No problem though, as I can have the code on each workbook that will be closed.

I'm been a Laker nut since they were the Minneapolis Lakers. That tells you how old I am. They certainly are doing great.

Best Wishes.

Hi Leith

I just got home and I'm so tired I will have to write my question tomorrow afternoon after church.

I just wanted to thank you so you don't think I'm not interested.

Tomorrow . . .

Hi Leith . . .

Looks like it will work, providing I set it up correctly:

I'll explain how I see it and would appreciate your advise if I am approaching it correctly.

1st . . .
I don't see how I can place your macro in my Personal.xls wherever this may be stored. This program is going out to many different computers, both to individuals and business'. What happens if others are already using their personal.xls ? Wouldn't we be clearing or replacing their's ? Or . . . is this Personal.xls file somehow stored within my program ? I'm not sure how this works.

If I can not use Personal.xls, couldn't I add a module to my Wk1, which is always open and have the routine stored there ? That would place it in a standard VBA module I assume.

Question:
Isn't each module that I store my macro's in classified as a standard VBA module ?


2nd . . .
You say "Simply give SUPPLY the name of the workbook to close.

I don't understand the use of the word SUPPLY. Looking at CloseWorkbook "Book1.xls" I have to assume that you are saying that this calls your Sub . . . and it supplies "Book1.xls" or my "Wb3" name to your sub-routine.


3rd . . .
It will close the workbook automatically.
Question . . .
Can you add (give me) a line of code that would show me how to make it always save ? Then I could make my decision, for as of right now, all my workbooks Close & Save automatically.


4th . . .
You show . . . Use Macro Closeworkbook "Book1.xls" , which is the same as my "Wb3" I used as my example.

If I add this to my code, which maybe I can figure out where to put it, will this automatically call your module and close my "Wb3" ? If this is true, I think I can use some of my other code to display Wb1 (which I want displayed) and then call. . . Closeworkbook "Wb3".xls This in turn would then remotely close "Wb3" completing my original macro.

I need to use my macro to check and see if "Wb1" is open . . . and if not, open it before continuing on.

Last Question I Hope . . .
When my code calls your Sub Closeworkbook "Wb3".xls , where or how does the the two functions located before the Sub Closeworkbook "Wb3".xls get their information about my "Wb3" ? The only way I have seen functions used is where the function is called from the Sub routine.

As you can see, I'm not too much of a programmer, but at lease I learn each time. I do understand the functionality of Excel and Business Applications, I think quite good. If I were to come back for a second run at life, I know that I would have learned programming at a very early age.

Bless you Leith. You have already been a great help to me. Way beyond all expectations.

Thanks Leith . . .

Your answers are very clear.

Give me 2 or 3 days and I'll let you know if I have everything working.

I'm Back . . .
After 3 days of Time Warner Cable being down. No internet. Hope it stays up.

I installed the VBA module in a Class 1 module in my main Menu which is always open.

Then I inserted your code CloseWorkbook "Wb3.xls" into my closing module in Wb3

I had to change my macro name as I had used the same name for my macro.

When I tried it, I get the error message:

Please Login or Register  to view this content.

To me it looks like my macro that has the call CloseWorkbook "Wb3.xls" can not find the class 1 module in my Wb1.xls.

Can you give me any ideas ? Is there anything special I have to do in the class 1 module ?