"OnTime" works - but only once per 'session&#

  • Hi, and thanks for the help before on this topic.


    Using "OnTime" in my personal.xls worksheet to run a different macro at a specific time works great - but it only works one time - the first time that specified time occurs after opening Excel.


    If I leave Excel open overnight during the next night, "OnTime" doesn't run - apparently because it already ran 24 hours previously and Excel wasn't 're-opened' in the interim.


    Is there a way to make OnTime run every night without closing Excel and re-opening it at least once every 24 hour period? As it works now, the update doesn't occur over the weekend (not in the office to close and re-open Excel) or on weeknights where I forget to close and re-open it.


    Thanks!


    Jim

  • Ok, I like to think I'm not completely dense, but I have no idea how to do what you just suggested. ;)


    Are you saying it's just a matter of adding more "OnTime" lines to the macro - i.e. it'll run one, then 24 hours run the next line, etc. - or something more specific that resets it to run 'from the top' again?


    thx

  • Ok, thaniks!


    Much of that was over my head, but here's what I think I saw...


    In my PERSONAL.XLS "ThisWorkbook" section, my current code is this:
    :
    =====
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("05:05:00"), "BDSUpdate"
    End Sub
    =====


    If I read that site correctly, it suggests I do something like the following instead:


    =====
    Private RunWhen As Double
    Private Const cRunIntervalHours = 24
    Private Const cRunWhat = "BDSUpdate"


    Sub StartTimer()


    RunWhen = TimeValue("05:05:00") + TimeSerial(0, 0, cRunIntervalHours)
    Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
    schedule:=True
    End Sub
    =====


    ... and then add the "StartTimer" line to the bottom of the actual "BDSUpdate" macro...


    I think?


    *scratches head*

  • All you really need is to put at the bootom of your BDSUpdate macro the one line
    Application.OnTime TimeValue("05:05:00"), "BDSUpdate"
    That will have it schedule itself to run when 05:05 rolls around, which will be the next day.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!