HiTek Software Home
 
Title Automatically save and exit Excel after running a macro
Date Updated 11/22/2005
Versions affected 4.0 and later
OS affected All
Description User needs to run Excel Macro.  Then save and exit the Excel Workbook.
Causes -
Solution The desired macro needs to be edited in the Excel Visual Basic Editor using the following steps:
0.  Backup your excel worksheet just in case...
1.  Use Tools->Macro->Macros->Edit  to edit your macro in the visual basic editor
2.  Before the 'End Sub' line in your macro, add the following line:
Application.OnTime Now + TimeValue("00:00:05"), "Save_Exit"
3. Add the function Save_Exit() from below, just after the 'End Sub' line of your macro.

Example 1:  The following example will save & exit Excel 5 seconds after the macro finishes running.

Sub Macro1()
  ' next line is the actual macro
  ActiveCell.FormulaR1C1 = "This example worked!"
  ' next line is the call to Save_ Exit macro
  Application.OnTime Now + TimeValue("00:00:05"), "Save_Exit"
End Sub

Sub Save_Exit()
  Application.Quit
  ThisWorkbook.Close SaveChanges:=True
End Sub

Example 2:  The following example will save & exit Excel 5 minutes after the macro starts running.  This way, the excel file will always close even if the macro hangs

Sub Macro1()
  ' next line is the call to Save_ Exit macro
  Application.OnTime Now + TimeValue("00:05:00"), "Save_Exit"
  'next line is the actual macro
  ActiveCell.FormulaR1C1 = "This example worked!"
 End Sub

Sub Save_Exit()
  Application.Quit
  ThisWorkbook.Close SaveChanges:=True
End Sub

 

Output Log none
Debug Log

-