2

I need to run my excel VBA daily at a specific time. I google the ontime method, however I dont get a comprehensive understanding from it. I would bring up this to clean up my confusion of ontime method.

Does the containing excel workbook have to be open for ontime method before it runs. If yes, Is there a way to open the excel workbook automatically at a specific time.I know it mite be done by Timer or a Windows task Scheduler. Could someone work me through this. Is my current code properly constructed for automated task scheduler?

My current code looks like this:

Sub StartTimer()
Application.OnTime EarliestTime:=TimeValue("11:15:00"), Procedure:="rune", _
    Schedule:=True
End Sub

Sub rune()
  SourceOneUpdate
  SourceTwoUpdate
  SourceThreeUpdate
  GenerateReport
End Sub

Private Sub workbook_open()
  StartTimer
End Sub

This is based on the idea from this post: http://www.cpearson.com/excel/OnTime.aspx which mite be helpful. Even this workbook is open, its not running automatically. Could someone help me on this to see why this is not working properly.

Thanks in advance.

Community
  • 1
  • 1
YoYue
  • 41
  • 2
  • 7
  • 1
    Indeed, the macro will run only if the workbook is open. Either you keep the workbook open, or you can - as you suggest - use a scheduler program to run `excel.exe yourfile.xlsm`. Then bind your macro to the workbook "WorkBook_Open" event, with a check on current time. – d-stroyer Aug 21 '13 at 13:12
  • @d-stroyer that could have easily be a valid answer –  Aug 21 '13 at 13:18

1 Answers1

5

just to expand on d-stroyer's comment.

You first need to make sure your macro setting is enabled at all times because this will ensure that everytime your workbook opens, the macro runs without any notification or confirmation.

To do this,
Excel Options > Trust Center > Trust Center Settings (Button) > Macro Settings > Enable All macros > OK

Now that your macro is enabled, you need to ensure the macro will run as soon as the workbook opens. So, go to your VB editor and open the ThisWorkbook module > Create a workbook_open() event and copy and paste your "OnTime code" into workbook_open event > Save & Close

Now, everytime you open the workbook, the workbook should run the desired code at 8:47.

Go to Task Scheduler, on the right click on "Create Basic Task...". Give the task a name and click next. Select a trigger (Daily in the case you mentioned here) and click next. Set the time and recurrence period and click next. In the Action, select "Start a program" and click next. In the Program/script text box browse for your Excel file and click next (leave the other text boxes empty). Click on Finish. I just tried this on my PC now, and it works.

From Windows Help Forum

PS: Make sure you set the scheduler to open the excel file BEFORE 8:47 (so maybe 8:46).

Cheers,
kpark

EDIT: try running this to see if your OnTime is working..

Sub RunOnTime()
    Application.OnTime Now + TimeSerial(0, 0, 10), "theSub"
End Sub
kpark
  • 394
  • 2
  • 12