Save original workbook name, referenced by macros in other books?

[danwPlanet]

I haven't yet figured out a good strategy for keeping track of the name
of the original workbook after the user has switched to another one.

For instance, in Book1.xls a macro might open Book2.xls, and if so, I
want Book2's macro to re-activate Book1. But since Book1's original
macro was in a menu, it could have been called when who-knows-what
workbook was the active one.

I've currently got the Book1's name hard-coded in Book2's macro, but
that's a time bomb for anyone who later renames Book1. Using
Personal.xls is not feasible with various users on a LAN.

It's easy to use

myOriginalBook = ActiveWorkbook.Name

or whatever, to grab the name of Book1 when the Book1 is opened, but
how can I store this string where it will still be available to Book2's
macro?

I was thinking of putting it in a Static variable in a subroutine that
I call from both workbooks, but I can't call the subroutine from
another workbook unless I spell out which workbook it's in, which means
I have to know the workbook name I'm trying to find out. Same problem
with storing it in a defined Name object in the original workbook, and
probably same problem with using custom document properties (or using
the registry(?) which seems too advanced for me).

Is this as Catch-22 as I'm imagining it to be?

 

[Tom Ogilvy]

Why have a macro in Book2 do this. Why not handle it all in one macro
assigned to the menu:

Sub Button_Click()
Dim bk as Workbook, bk1 as Workbook
set bk = Activeworkbook
set bk1 = workbooks.Open(FileName:="C:\Myfolder\Book2.xls")
bk.Activate
msbox bk1.Name & " has been opened"
End Sub

 

[danwPlanet]

Book1 is in an In-Production LAN directory I can't edit except through
someone else, and to test the new macro I want the new code to be in
Book2, which is in a folder where I can freely edit and test it. And I
want it to be initiated from Book1, because the user has only Read-Only
access to my directory.

(I guess this is really just an admission that we don't have a
Test-vs-Production staging system for Excel where I work, but fixing
that would be a long-term solution, and I'm of course looking for a
strategy I can use today.)

* * * *

Even if that were not the case, isn't it still true that the menus mess
it up? If the user happens to have several Excel windows open, they
all show the same set of menus. If they select a menu item while some
Book3 is open, then Activeworkbook will be Book3, the wrong one, not
so? When a menu runs a macro, is there some way to find out the name
of the workbook that contains the macro?

If the menu problem is solved, I think I can take advantage of the fact
that opening Book2 and then immediately HIDING it will re-activate the
previous workbook...

* * * *

Another thought is to have the opening of Book1 automatically open
Book2 and store the info in Book2. (I'm willing to hard-code the name
of Book2.) But the new macro will rarely be used, so I'd hate to
resort to opening a seldom-used workbook every time. Then again, after
this is all thoroughly tested and in use for a while, I do plan to move
it all into Book1.

 

[Tom Ogilvy]

Sounds like you are playing 3 Card Monty.

If you figure out how to describe your situation possibly someone can make a
meaningful solution.