vba 在excel中保存单元格的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21760440/
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
saving value of a cell in excel
提问by user3306967
I am not a programmer. I need help with excel VBA. I am trying to save the value of a cell which changes at the end of every day. I want to save the value of the cell in another cell at the end of the day before it changes to another value the next day since the cell is receiving real time data. I want to save the old data and use it for calculations. I want to do it automatically for me everyday. How do I do that using excel? Your help would be greatly appreciated.
我不是程序员。我需要有关 Excel VBA 的帮助。我正在尝试保存在每天结束时更改的单元格的值。我想在一天结束时将单元格的值保存在另一个单元格中，然后在第二天更改为另一个值之前，因为该单元格正在接收实时数据。我想保存旧数据并将其用于计算。我想每天自动为我做这件事。我如何使用excel做到这一点？您的帮助将不胜感激。
回答by Brandon R. Gates
I'm going to assume that you want to save and close your file at or after 5 pm every day. If those assumptions are correct, modify the following code to suit the structure of your workbook:
我将假设您想在每天下午 5 点或之后保存并关闭文件。如果这些假设是正确的，请修改以下代码以适合您的工作簿的结构：
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Dim celLive As Range
Dim celSave As Range
If Hour(Now()) >= 17 Then
' ### change the below values to match your workbook structure ###
Set sht = Sheets("MySheet")
Set celLive = sht.Range("A1")
Set celSave = sht.Range("B1")
celSave.Value = celLive.Value
ActiveWorkbook.Save
End If
End Sub
Alternatively, you may wish to review the results of the executed code before closing the workbook by using the following code instead:
或者，您可能希望在关闭工作簿之前通过使用以下代码来查看已执行代码的结果：
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sht As Worksheet
Dim celLive As Range
Dim celSave As Range
If Hour(Now()) >= 17 Then
' ### change the below values to match your structure ###
Set sht = Sheets("MySheet")
Set celLive = sht.Range("A1")
Set celSave = sht.Range("B1")
celSave.Value = celLive.Value
With Application
.EnableEvents = False
ActiveWorkbook.Save
.EnableEvents = True
End With
End If
End Sub
Put the code for either method in the Workbook module or it will not execute.
将任一方法的代码放在 Workbook 模块中，否则它不会执行。
