Daily timer to automatically run a macro

Question

I am trying to run a macro each day at 9am. I am trying to use the OnTime application and have tested the macro to launch at a specific time but it does not work automatically. I have to manually run the macro to get the message box to pop-up. Any advice?

Sub FinancialAlert()

    Application.OnTime TimeValue("15:33:00"), "Summary of Covered Companies"


    Dim i As Long
    i = 5

    While Workbooks("Montreal Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 5) <> ""

    With Workbooks("Montreal Issuers.xlsm")

        If .Sheets("Summary of Covered Companies").Cells(i, 5).Value = 1 Then

            MsgBox Workbooks("Montreal Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 3).Value & " is issuing their next financial statement tomorrow (" & _
            Format(Workbooks("Montreal Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 4).Value, "[$-409]mmmm d, yyyy;@") & ")."


        End If

        If .Sheets("Summary of Covered Companies").Cells(i, 5).Value = 0 Then

            MsgBox Workbooks("Montreal Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 3).Value & " is issuing their next financial statement today (" & _
            Format(Workbooks("Montreal Issuers.xlsm").Sheets("Summary of Covered Companies").Cells(i, 4).Value, "[$-409]mmmm d, yyyy;@") & ")."


        End If


    End With

    i = i + 1

    Wend

End Sub

score 2 · Accepted Answer · edited Mar 01 '20 at 02:34

2

This can be done, however, you need to run the on time in the module ThisWorkbook in the Workbook_Open routine:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("15:33:00"), "Summary of Covered Companies"
End Sub

A macro won't run standalone. When the workbook is opened this command will schedule it. If the command was only called in the macro itself, Excel will never know until something activates it.

edited Mar 01 '20 at 02:34

feetwet

3,248
7
46
84

answered Jun 17 '16 at 20:59

Sgdva

2,800
3
17
28

Can I create a cell with the time and when it hits 9:00am each day the macro will run? – Jeff Jun 17 '16 at 21:05
Yes you can, just make sure the cell is properly formatted as hour time and try Application.OnTime TimeValue(Range("A1").Text), "Summary of Covered Companies" Don't forget, if this answer lead you to the solution, check the green mark :) – Sgdva Jun 17 '16 at 21:10

Abe Gold · Answer 2 · 2016-06-17T21:46:49.630

You can schedule windows to open the file with task scheduler. Then have the macro run on Workbook Open and the close the file.

Another idea I've been using for years is to schedule Gmail to send an email to a specific account at a given time each day with a code word in the subject. I then have Outlook run a macro if an email comes in with that code word. This gives me the ability to stop the macro remotely by stopping the email from being sent. It's worked flawlessly.

Daily timer to automatically run a macro

2 Answers2