Ignore "Do you wish to save" box on exit of excel

12,984

Solution 1

ActiveWorkbook.Close False (to close the workbook)

Application.Quit (to quit Excel - doesn't prompt to save changes)

From Microsoft Support's How to suppress "Save Changes" prompt when you close a workbook in Excel:

To force a workbook to close without saving any changes, type the following code in a Visual Basic module of that workbook:

Sub Auto_Close()
    ThisWorkbook.Saved = True
End Sub

Because the Saved property is set to True, Excel responds as though the workbook has already been saved and no changes have occurred since that last save.

The DisplayAlerts property of the program can be used for the same purpose. For example, the following macro turns DisplayAlerts off, closes the active workbook without saving changes, and then turns DisplayAlerts on again.

Sub CloseBook()
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

You can also use the SaveChanges argument of the Close method.

The following macro closes the workbook without saving changes:

Sub CloseBook2()
    ActiveWorkbook.Close savechanges:=False
End Sub

Solution 2

The answer you have above is for VBA - you can address this in your VBS directly by using

oWorkBook.Close False
Set oWorkBook = Nothing

in place of

Set oWorkBook = Nothing
Share:
12,984
Cassandra McCarthy
Author by

Cassandra McCarthy

she/her

Updated on August 17, 2022

Comments

  • Cassandra McCarthy
    Cassandra McCarthy over 1 year

    I have a script that opens an excel file and runs a macro, then quits the file. Since the file is in read only mode, and the script makes temporary changes to the file, when the script calls myExcelWorker.Quit() excel asks if I want to save my changes and I must click 'no'. Is there any way to exit the program and skip this box?

    ' Create a WshShell to get the current directory
    Dim WshShell
    Set WshShell = CreateObject("WScript.Shell")
    
    ' Create an Excel instance
    Dim myExcelWorker
    Set myExcelWorker = CreateObject("Excel.Application") 
    
    myExcelWorker.Visible = True
    
    ' Tell Excel what the current working directory is 
    ' (otherwise it can't find the files)
    Dim strSaveDefaultPath
    Dim strPath
    strSaveDefaultPath = myExcelWorker.DefaultFilePath
    strPath = WshShell.CurrentDirectory
    myExcelWorker.DefaultFilePath = strPath
    
    ' Open the Workbook specified on the command-line 
    Dim oWorkBook
    Dim strWorkerWB
    strWorkerWB = strPath & "\BugHistogram_v2.xlsm"
    
    Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)
    
    ' Build the macro name with the full path to the workbook
    Dim strMacroName
    strMacroName = "CreateImagesButton_Click"
    on error resume next 
       ' Run the calculation macro
       myExcelWorker.Run strMacroName
       if err.number <> 0 Then
          ' Error occurred - just close it down.
       End If
       err.clear
    on error goto 0 
    
    ' oWorkBook.Save ' this is ignored because it's read only 
    
    myExcelWorker.DefaultFilePath = strSaveDefaultPath
    
    ' Clean up and shut down
    Set oWorkBook = Nothing
    
    ' Don’t Quit() Excel if there are other Excel instances 
    ' running, Quit() will 
    ' shut those down also
    if myExcelWorker.Workbooks.Count = 0 Then
       myExcelWorker.Quit
    End If
    
    myExcelWorker.Quit()
    
    Set myExcelWorker = Nothing
    Set WshShell = Nothing
    
  • Cassandra McCarthy
    Cassandra McCarthy over 9 years
    Thank you! I used the CloseBook() method.