| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Close current Workbook after calling macro in other
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Close current Workbook after calling macro in other
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Close current Workbook after calling macro in other
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Close current Workbook after calling macro in other
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to Close a specific workbook. | Excel Discussion (Misc queries) | |||
| Macro to close workbook with prompt | Excel Worksheet Functions | |||
| How to add formula calling up current workbook when sheet copied | Excel Discussion (Misc queries) | |||
| Macro to close workbook and re-open new copy | Excel Discussion (Misc queries) | |||
| run macro on workbook close | Excel Discussion (Misc queries) | |||
Linear Mode
