vba 退出excel时忽略“是否要保存”框

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

提问by Cassandra McCarthy

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?

我有一个脚本可以打开一个 excel 文件并运行一个宏，然后退出该文件。由于文件处于只读模式，并且脚本对文件进行了临时更改，因此当脚本调用myExcelWorker.Quit()excel 时会询问我是否要保存更改，我必须单击“否”。有没有办法退出程序并跳过此框？

' 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

回答by Isaac G Sivaa

ActiveWorkbook.Close False(to close the workbook)

ActiveWorkbook.Close False（关闭工作簿）

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

Application.Quit（退出 Excel - 不提示保存更改）

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

从 Microsoft 支持的如何在 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 DisplayAlertsproperty of the program can be used for the same purpose. For example, the following macro turns DisplayAlertsoff, closes the active workbook without saving changes, and then turns DisplayAlertson again.

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

You can also use the SaveChangesargument of the Closemethod.

The following macro closes the workbook without saving changes:

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

若要强制关闭工作簿而不保存任何更改，请在该工作簿的 Visual Basic 模块中键入以下代码：

Sub Auto_Close()
    ThisWorkbook.Saved = True
End Sub

由于 Saved 属性设置为 True，Excel 的响应就像工作簿已保存并且自上次保存后未发生任何更改一样。

程序的DisplayAlerts属性可用于相同目的。例如，以下宏关闭DisplayAlerts，关闭活动工作簿而不保存更改，然后再次打开 DisplayAlerts。

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

您还可以使用Close方法的SaveChanges参数。

以下宏关闭工作簿而不保存更改：

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

回答by brettdj

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

您上面的答案是VBA- 您可以VBS直接使用

oWorkBook.Close False
Set oWorkBook = Nothing

in place of

代替

Set oWorkBook = Nothing