Close current Workbook after calling macro in other

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

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" <desjanke@hotmail.com> wrote in message news:3f2f4a58$1@news.connect.usq.edu.au...
> 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
>
>
>
>

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" <deitbe@consumer.org> wrote in message
news:uLmgVs0WDHA.1640@TK2MSFTNGP10.phx.gbl...
> 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" <desjanke@hotmail.com> wrote in message
news:3f2f4a58$1@news.connect.usq.edu.au...
> > 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
> >
> >
> >
> >
>
>

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" <desjanke@hotmail.com> wrote in message news:3f309b34$1@news.connect.usq.edu.au...
> 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" <deitbe@consumer.org> wrote in message
> news:uLmgVs0WDHA.1640@TK2MSFTNGP10.phx.gbl...
> > 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" <desjanke@hotmail.com> wrote in message
> news:3f2f4a58$1@news.connect.usq.edu.au...
> > > 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
> > >
> > >
> > >
> > >
> >
> >
>
>

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

"Bernie Deitrick" <deitbe@consumer.org> wrote in message
news:#EksEpDXDHA.2524@TK2MSFTNGP09.phx.gbl...
> 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" <desjanke@hotmail.com> wrote in message
news:3f309b34$1@news.connect.usq.edu.au...
> > 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" <deitbe@consumer.org> wrote in message
> > news:uLmgVs0WDHA.1640@TK2MSFTNGP10.phx.gbl...
> > > 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" <desjanke@hotmail.com> wrote in message
> > news:3f2f4a58$1@news.connect.usq.edu.au...
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>