关闭工作簿时的VBA Excel操作 [英] VBA Excel action when closing workbook

问题描述

有人关闭文件时，是否可以让Excel自动执行操作?

Is it possible automatically let Excel do an action when someone closes the file?

情况: 我有一个Excel文件，其他几个人也使用过.保存时，该文件将发布一个mhtml文件.该mhtml文件将与"Dashboard 2015-01-12"一样保存昨天的日期.在mhtml文件中看到的数据必须包含与文件名相关的日期.看到的数据取决于此excel文件G2中的单个单元格.

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文件执行此操作: 将单个单元格(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.

我希望执行此操作: 当有人关闭文件时.

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

这会做这项工作吗?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Range("G2") = Format(Now - 1, dd - mm - yy)
   ActiveWorkbook.Close SaveChanges:=True
End Sub

推荐答案

这是使用工作簿代码的:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call sbWriteCellWhenClosing
End Sub

在单独的模块中:

Sub sbWriteCellWhenClosing()

    ActiveSheet.Range("G2") = Format(Now - 1, "dd-mm-yy")   '-1 for yesterday
    ActiveWorkbook.Save

End Sub

这篇关于关闭工作簿时的VBA Excel操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！