Passing Control when workbooks have the same name

[shawb]

Basically I have several workbooks with the same name in different directories for consistency reasons in my organization.

Is it possible from within one of these workbooks to close itself, and open up another workbook (with the same name but in a different directory) using a macro.

Or alternatively is it possible to execute a macro in another workbook from the macros in a workbook? (Ie to temporarily pass control to a third workbook that closes the current workbook and opens the desired workbook).

Cheers

[FSt1]

hi,
1.The workbook that contains the running macro must remain open.
2.You can run another macro in another workbook but the 1st workbook must
remain open because basicly the first macro called the second macro and
paused while the second macro ran. techniquely the first macro is still
running because it hasn't got to end sub yet. the call basicly inserted more
code into the running macro.
3.you can open another workbook and run a macro in the first workbook that
manipulated data in the second workbook but the first workbook cannot be
closed.

If you are thinking of manipulated several workbooks at a time, it would be
best to do what i did. create a special workbook that contains only macro.
open the macro workbook and run the macros that manipulates all the files.you
can write a macro that calls all the other macros.

my macro workbook fires at 5 in the morning(while i'm still at home asleep)
then runs 11 defferent macros that downloads data from the mainframe then
manipulates this data in 11 different files. Reports are on the printer when
i get to work. the 11 different files contain no macros because they are not
needed.

a word of caution. if you do have workbook with the same name.... you may
run into problems. i don't know what because i have never tried to open two
workbooks with the same name but that sounds risky.

good luck

FSt1



"shawb" wrote:

>
> Basically I have several workbooks with the same name in different
> directories for consistency reasons in my organization.
>
> Is it possible from within one of these workbooks to close itself, and
> open up another workbook (with the same name but in a different
> directory) using a macro.
>
> Or alternatively is it possible to execute a macro in another workbook
> from the macros in a workbook? (Ie to temporarily pass control to a
> third workbook that closes the current workbook and opens the desired
> workbook).
>
> Cheers
>
>
> --
> shawb
> ------------------------------------------------------------------------
> shawb's Profile: http://www.excelforum.com/member.php...o&userid=14204
> View this thread: http://www.excelforum.com/showthread...hreadid=390893
>
>

[keepITcool]

yes.

in the caller you use application.ontime
to call an "opener" in a control workbook.
after that the caller closes itself.

control workbook must be open to avoid "enable" macro windows..
ontime must be used to "break the thread"

the scheduled "opener" then opens the next workbook.

BUT how do you know which is the NEXT?
(you could pass an (array) argument or a collection between calls..

you must have a control loop somewhere?



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


shawb wrote :

>
> Basically I have several workbooks with the same name in different
> directories for consistency reasons in my organization.
>
> Is it possible from within one of these workbooks to close itself, and
> open up another workbook (with the same name but in a different
> directory) using a macro.
>
> Or alternatively is it possible to execute a macro in another workbook
> from the macros in a workbook? (Ie to temporarily pass control to a
> third workbook that closes the current workbook and opens the desired
> workbook).
>
> Cheers

[keepITcool]

Further:

maybe corporate policy s/b adapted.

anyone using excel knows you cant open 2 books with same name.

for excel files it's much wizer to save multiple copies
of the same file in 1 directory, with date and time appended
to filename for "versioning"

a simple SaveCopyAs procedure can take care of it.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


shawb wrote :

>
> Basically I have several workbooks with the same name in different
> directories for consistency reasons in my organization.
>
> Is it possible from within one of these workbooks to close itself, and
> open up another workbook (with the same name but in a different
> directory) using a macro.
>
> Or alternatively is it possible to execute a macro in another workbook
> from the macros in a workbook? (Ie to temporarily pass control to a
> third workbook that closes the current workbook and opens the desired
> workbook).
>
> Cheers

[Gareth]

The third party workbook solution is your best bet rather having a
workbook running code closing itself. Maybe place your code in an AddIn
or just personal.xls (if it's just you using it).

The below code loops through as many workbooks as you need - closing
each before opening the next.

Sub WorkWithManyWorkbooks()

Dim wb as workbook
Dim myWbs as variant
Dim i as integer

myWbs = Array("c:\temp\book1.xls", _
"c:\temp1\book1.xls", _
"c:\temp2\book1.xls", _)

For i = lbound(mywbs) to ubound(mywbs)
set wb = workbooks.open (mywbs(i))
'perform required operations on wb

wb.save
wb.close
Next i

set wb = nothing 'it shoudl be anyway

End sub

shawb wrote:
> Basically I have several workbooks with the same name in different
> directories for consistency reasons in my organization.
>
> Is it possible from within one of these workbooks to close itself, and
> open up another workbook (with the same name but in a different
> directory) using a macro.
>
> Or alternatively is it possible to execute a macro in another workbook
> from the macros in a workbook? (Ie to temporarily pass control to a
> third workbook that closes the current workbook and opens the desired
> workbook).
>
> Cheers
>
>

[Vacation's Over]

FSt1 -

For 5am "firing" do you use anything fancy or just leave the file open with
an ontime = 5am macro?

"FSt1" wrote:

> hi,
> 1.The workbook that contains the running macro must remain open.
> 2.You can run another macro in another workbook but the 1st workbook must
> remain open because basicly the first macro called the second macro and
> paused while the second macro ran. techniquely the first macro is still
> running because it hasn't got to end sub yet. the call basicly inserted more
> code into the running macro.
> 3.you can open another workbook and run a macro in the first workbook that
> manipulated data in the second workbook but the first workbook cannot be
> closed.
>
> If you are thinking of manipulated several workbooks at a time, it would be
> best to do what i did. create a special workbook that contains only macro.
> open the macro workbook and run the macros that manipulates all the files.you
> can write a macro that calls all the other macros.
>
> my macro workbook fires at 5 in the morning(while i'm still at home asleep)
> then runs 11 defferent macros that downloads data from the mainframe then
> manipulates this data in 11 different files. Reports are on the printer when
> i get to work. the 11 different files contain no macros because they are not
> needed.
>
> a word of caution. if you do have workbook with the same name.... you may
> run into problems. i don't know what because i have never tried to open two
> workbooks with the same name but that sounds risky.
>
> good luck
>
> FSt1
>
>
>
> "shawb" wrote:
>
> >
> > Basically I have several workbooks with the same name in different
> > directories for consistency reasons in my organization.
> >
> > Is it possible from within one of these workbooks to close itself, and
> > open up another workbook (with the same name but in a different
> > directory) using a macro.
> >
> > Or alternatively is it possible to execute a macro in another workbook
> > from the macros in a workbook? (Ie to temporarily pass control to a
> > third workbook that closes the current workbook and opens the desired
> > workbook).
> >
> > Cheers
> >
> >
> > --
> > shawb
> > ------------------------------------------------------------------------
> > shawb's Profile: http://www.excelforum.com/member.php...o&userid=14204
> > View this thread: http://www.excelforum.com/showthread...hreadid=390893
> >
> >