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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With