That worked a treat - everything is running fine! Thanks.

"Bernie Deitrick" wrote in message
...
Des,

Try this:

In the first workbook:

Sub StartUpBook2()
Dim mybook As Workbook
Set mybook = Workbooks.Open(ThisWorkbook.Path & "\Book 2.xls")
Application.Run "'Book 2.xls'!CloseBook1Macro", ThisWorkbook
End Sub

'In the second workbook, named "Book 2"
Sub CloseBook1Macro(inBook As Workbook)
Application.OnTime Now + TimeValue("00:00:05"), "NextMacro"
inBook.Close False
End Sub

Sub NextMacro()
MsgBox "Hello from book two"
End Sub

HTH,
Bernie
Excel MVP


"Des Janke" wrote in message
...
Thanks Bernie
Tried your suggestions (spent some hours on it) - but still have an
issue
with the 'calling' workbook not closing. When I call the switch macro
and
try to close the 'source' workbook, the switch macro starts from the
beginning again:

In c:\subdir\menu.xlm, a button runs this macro -

Sub GoToOtherMenu()
Dim Pathfile As String
Pathfile = ThisWorkbook.Path
Workbooks.Open Filename:=Pathfile & "\Switch.xls"
Application.Run "Switch.xls!SwitchtoDept" 'executing the macro
SwitchtoDept
'in Switch.xls
End Sub

When executing the macro in Switch.xls I close the menu.xls document
with
Workbooks("menu.xlm").Close SaveChanges:=False
and continue with the macro in Switch.xls, but it restarts from the
beginning.

Another idea I had - might need suggestions for code :-))
1) In c:\subdir\menu.xlm user pushes button that loads a Global
Variable
(with say name of this workbook) and closes itself
2) in c:\subdir\menu.xlm have auto_close that starts switch.xls
3) switch.xls has an auto_open macro that looks for the variable (if no
variable - do nothing, so normal open does nil) and then opens the
c:\menu.xlm file based on what's in variable.....

Des








"Bernie Deitrick" wrote in message
...
Des,

If your file name and path is always the same, then the first line of
your
switch macro could close the file. Otherwise, you could
pass the file's full name to the switch macro as a string and use that
parameter in a close statement.

HTH,
Bernie
Excel MVP


"Des Janke" wrote in message
...
Hi
Searched Google for this question but didn't find what I needed.
Have two workbooks with same name in different directories:
eg c:\menu.xlm
c:\subdir\menu.xlm
(this is a PeopleSoft nVision related issue, so code is in an XLM)
I have c:\subdir\menu.xlm active in Excel and click a button to call
a
'switch' macro in another workbook c:\subdir\switch.xls. This macro
is
supposed to open the c:\menu.xlm.
I use switch.xls to avoid 'file already open' errors when going
from
c:\subdir\menu.xlm to c:\menu.xlm (the files have to be the same
name).
All this works fine - but I'd like the c:\subdir\menu.xlm to close
after
calling the 'switch' macro in switch.xls. Can't work out how to get
this
to
work. Can anyone help?
At present I'm using this code. It runs the SwitchtoDept macro just
fine
but
I'd like to close 'ThisWorkBook' as well

Sub SwitchFinToDept()

Dim Pathfile As String
Pathfile = ThisWorkbook.Path

Workbooks.Open Filename:=Pathfile & "\Switch.xls"
Application.Run "Switch.xls!SwitchtoDept"
ThisWorkbook.Close

End Sub



TIA
Des