VBA 每天运行一次宏

VBA Run Macro Once a Day

提问by themolestones

I need some code to run a macro just once a day, it doesn't matter how many times you open the file where is the macro.

我需要一些代码来每天只运行一次宏，无论您打开宏在哪里的文件多少次都没有关系。

If the file is not opened one day it doesn't have to run the macro, just do it when it is opened.

如果某天没有打开文件，则不必运行宏，只需在打开时执行即可。

It has to have a ¨internal" variable or something like that, I guess, that keeps the info whether the macro has alredy run or not this day.

它必须有一个“内部”变量或类似的东西，我猜，它可以保存宏今天是否已经运行的信息。

Besides, to make it more difficult, I suppose, the macro open a different Workbook each day.

此外，为了让它变得更加困难，我想，宏每天打开一个不同的工作簿。

Any thoughts.

有什么想法吗。

I am novice, so forgive me if that is so clear. Thanks in advance.

我是新手，如果说的这么清楚，请原谅我。提前致谢。

EDITED: I found some code here:

编辑：我在这里找到了一些代码：

that seems to do it but you have to create an extra sheet, I would like to dont do that.

似乎可以，但您必须创建一个额外的工作表，我不想这样做。

Here is the code:

这是代码：

Private Sub Workbook_Open()
Dim rngFindTodaysDate As Range
    With ThisWorkbook.Worksheets("Status")

        On Error GoTo X
        Set rngFindTodaysDate = .Range("A1").End(xlDown).Find(Date)
        If rngFindTodaysDate Is Nothing Then
            .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1) = Date

            '''''  your Code  Here

        End If
    End With
    X:
End Sub

回答by Jamie Bull

You can use Windows Task Scheduler to automatically open the file once a day. There's a really good step-by-step tutorial herewith the required VB Script code included.

您可以使用 Windows 任务计划程序每天自动打开一次文件。这里有一个非常好的分步教程，其中包含所需的 VB 脚本代码。

If the user may also be opening the file manually, you will want a state variable which records whether the macro has already run that day. The best bet is probably to have a sheet dedicated to recording this. Perhaps call it RunTimes. Then you can add the following line to your Workbook_Openevent:

如果用户也可能手动打开文件，您将需要一个状态变量来记录当天宏是否已经运行。最好的办法可能是有一张专用于记录这一点的工作表。也许叫它RunTimes。然后您可以将以下行添加到您的Workbook_Open事件中：

If Date > Application.Max(Sheets("RunRecords").Range("A:A")) Then
    Call YourMacroName
    Sheets("RunRecords").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Date
End If

回答by K_B

Use a (named) range, a single cell, in your workbook where the last time & date the macro is run is stored by the macro itself:

在您的工作簿中使用（命名）范围，即单个单元格，宏运行的最后时间和日期由宏本身存储：

Sheetx.Range("rLastRun").Value2 = Now()

Add that to the end of your macro or at least afterthe following check, where your macro checks if the last time run cell value is before today. Then the total would look like:

将其添加到您的宏的末尾或至少在以下检查之后，您的宏检查上次运行单元格值是否在今天之前。那么总数将如下所示：

If Sheetx.Range("rLastRun").Value2 < Date Then

    <your macro>

    Sheetx.Range("rLastRun").Value2 = Now()

End If

---

---

For the opening a different file each time you have to be more specific as with the information provided so far we cannot help there. Ask youself the following:

对于每次打开不同的文件，您必须更具体，因为到目前为止提供的信息我们无法提供帮助。问自己以下问题：

1. Is there any logic in the files properties?
2. Does it have the same name every time except for a timestamp (eg. Input20121128.xlsand Input20121127.xls?
3. Is the document name in a limited pool of possible names?
4. Is it always in the same folder?
5. Does it have a specific creator, date, time, ...?

1. 文件属性中是否有任何逻辑？
2. 除了时间戳之外，它是否每次都具有相同的名称（例如Input20121128.xls和Input20121127.xls？
3. 文档名称是否在有限的可能名称池中？
4. 它总是在同一个文件夹中吗？
5. 它是否有特定的创建者、日期、时间……？

With information provided your file lookup would be:

提供信息后，您的文件查找将是：

Dim strInputfile As String

<other code>

strInputfile = "<standardfolderstring>" & Format(Date, "dd/mm/yyyy") & " Test.xlsx"

回答by Chris

Personally I prefer the ideas that others have suggested to solve this... possibly using a single cell, filled with the current date and colour the date white to hide it... If not give this a try:

就我个人而言，我更喜欢其他人建议解决这个问题的想法......可能使用单个单元格，填充当前日期并将日期着色为白色以隐藏它......如果不试试这个：

If you do not want to have a work sheet, you could use an external text file, in the same dir for example. When the XLS opens it will read the textfile to see the current date, then if it doesn't match today, run your once a day code and update the text file to today's date else do nothing.

如果您不想拥有工作表，则可以使用外部文本文件，例如在同一目录中。当 XLS 打开时，它将读取文本文件以查看当前日期，然后如果它与今天不匹配，则每天运行一次代码并将文本文件更新为今天的日期，否则什么都不做。

Public txt_file_location As String
Public txt_file_name As String
Private Sub Workbook_Open()

    txt_file_location = "C:\Documents and Settings\Chris\Desktop"
    txt_file_name = "test.txt"
    Dim dateToday As Date
    Dim dateInFile As Date
    dateToday = Date ' will be used for both comparison and for writing to txt file if need be
    dateInFile = txtfile_read(txt_file_location, txt_file_name)    ' On open - read the text file to check what the current date is.

    If (dateToday <> dateInFile) Then

        ' ok the date in the text file is different to today's date, so your script needs to be called here

        Call do_some_work ' a function that runs once a day...

        ' Now we need to update the textfile to todays date to prevent rerunning
        Call save_to_text_file(txt_file_location, txt_file_name, dateToday)
    Else
        MsgBox ("The script has already ran today")
    End If

End Sub
Sub do_some_work()

    ' here could be one of the functions that needs to run once a day
    MsgBox ("Some work was done!")

End Sub
Function txtfile_read(txt_file_dir, file_name)
    Dim iFileNumber As Long
    Dim strFilename As String
    strFilename = txt_file_dir & "\" + file_name
    iFileNumber = FreeFile()
    Open strFilename For Input As #iFileNumber
    Dim txt As Variant
    Do While Not EOF(iFileNumber)
        Line Input #iFileNumber, myLine
        txtfile_read = myLine
    Loop
    Close #iFileNumber
End Function
Function save_to_text_file(txt_file_dir, file_name, content_to_be_written)
    Dim iFileNumber As Long
    Dim strData As String
    Dim strFilename As String
    strFilename = txt_file_dir & "\" + file_name
    iFileNumber = FreeFile()
    Open strFilename For Output As #iFileNumber
    Print #iFileNumber, content_to_be_written
    Close #iFileNumber
End Function

回答by bonCodigo

Here is the logic, please look into it.

这是逻辑，请仔细研究。

Store a value : e.g. 0 in a cell in the sheet target to run the macro. Then when macro is triggered, change that value to : e.g. 1. Then no matter how many times the sheet opens and macro gets invoked, sicne the cell valus is 1, the macro will exit and not complete the full process

在工作表目标的单元格中存储一个值：例如 0 以运行宏。然后当宏被触发时，将该值更改为：例如 1。那么无论工作表打开多少次并调用宏，由于单元格值为 1，宏将退出并且不会完成整个过程