Close current Workbook after calling macro in other

  • Thread starter Thread starter Des Janke
  • Start date Start date
D

Des Janke

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
 
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 said:
Des,

If your file name and path is always the same, then the first line of your
Click to expand...
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
Click to expand...
parameter in a close statement.
 
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
 
Back
Top