关闭工作簿时的 VBA Excel 操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27923460/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel action when closing workbook
提问by RiveR
Is it possible automatically let Excel do an action when someone closes the file?
当有人关闭文件时，是否可以自动让 Excel 执行操作？
Situation: I have an Excel file which is also used by several other people. This file publishes a mhtml file whens saving. This mhtml file will be saved with the date of yesterday like "Dashboard 2015-01-12". The seen data in the mhtml file has to contain the date related to the file name. The data seen depends on a single cell in this excel file, G2.
情况：我有一个 Excel 文件，它也被其他几个人使用。此文件在保存时发布一个 mhtml 文件。此 mhtml 文件将保存为昨天的日期，如“仪表板 2015-01-12”。mhtml 文件中的可见数据必须包含与文件名相关的日期。看到的数据取决于此 Excel 文件 G2 中的单个单元格。
I want the Excel file to do this: change a single cell (G2) into the date of yesterday. Then save it. Then close it.
我希望 Excel 文件执行此操作：将单个单元格 (G2) 更改为昨天的日期。然后保存。然后关闭它。
I want this action to be done: when someone is closing the file.
我希望完成此操作：当有人关闭文件时。
Code I have so far:
我到目前为止的代码：
Sub sbWriteCellWhenClosing()
Workbooks("BOOK1.XLS").Close SaveChanges:=True
Range("G2") = Format(Now - 2, dd - mm - yy)
End Sub
Edit:
编辑：
Will this do the job?
这能完成这项工作吗？
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Range("G2") = Format(Now - 1, dd - mm - yy)
ActiveWorkbook.Close SaveChanges:=True
End Sub
回答by peege
This is using the Workbook code:
这是使用工作簿代码：
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call sbWriteCellWhenClosing
End Sub
In a separate Module:
在单独的模块中：
Sub sbWriteCellWhenClosing()
ActiveSheet.Range("G2") = Format(Now - 1, "dd-mm-yy") '-1 for yesterday
ActiveWorkbook.Save
End Sub
回答by Qbik
You should use Workbook.BeforeCloseevent :
http://msdn.microsoft.com/en-us/library/office/ff194765%28v=office.15%29.aspx
您应该使用Workbook.BeforeClose事件：http:
//msdn.microsoft.com/en-us/library/office/ff194765%28v=office.15%29.aspx
to do that in project window in VBA editor you have to use ThisWorkbook and place your code there. Your code will work only for workbooks modified in this way.
要在 VBA 编辑器的项目窗口中执行此操作，您必须使用 ThisWorkbook 并将代码放在那里。您的代码仅适用于以这种方式修改的工作簿。
