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

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

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

Cheer

--
shaw
-----------------------------------------------------------------------
shawb's Profile: http://www.excelforum.com/member.php...fo&userid=1420
View this thread: http://www.excelforum.com/showthread.php?threadid=39089

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

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

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

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

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