Posts by jwhitwell

    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, _
    End Sub

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

    I think?

    *scratches head*

    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?


    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.




    I cut that section out of the bds macro, put it directly into the 'this workbook' section, set the time for 2 minutes in the future, closed Excel, saving personal.xls.

    I re-opened Excel and waited... the macro did execute at the right time (yeah!), but only ran the first line of the macro (opened the first of many files it's suppposed to open).

    If I run the macro directly, it does run fully as intended, so am not sure why it's stopping after the first line of the macro when it runs using the OnTime function...

    Any thoughts?

    Ok, here's the top of the macro I have in PERSONAL.XLS, and it's not working... ;)

    Sub BDSUpdate()
    ' Macro2 Macro
    ' Macro recorded 2/28/2003 by JWhitwell
    ' Keyboard Shortcut: Ctrl+l
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("16:07:00"), "BDSUpdate"

    I'm sure there's an obvious problem here, but I can't see it (yes, I'm a newb).

    Any suggestions would be greatly appreciated - thanks!

    Derk, that's somewhat my question, actually...

    It may be (probably) that I just don't know enough about how it works, but if I add the "On Time" section to the top of a macro in my personal.xls - will it simply run at that time by virtue of the fact that Excel is open, without any other initiating event?

    Hm, ok...

    Do I just add this to the top of the macro and it will work - it won't require something to initiate the macro (ctrl-k for example) in order for it to work?:)

    Sorry for the dumb questions...

    And... the example given is...

    Application.OnTime TimeValue("17:00:00"), "my_Procedure"

    ... where "my_procedure" is (I presume) the macro I want to run - do I just substitute the name of my macro in there?

    Will check that, thanks!

    I have a task under Task Scheduler that will open Excel at the right time, and a macro in my Personal Workbook that opens all of the appropriate worksheets with the appropriate actions, then closes them all down with saves - I just don't know how to kick-start that macro once Task Scheduler gets Excel open! ;)

    Will check VBA help for "On Time" - thanks!!:)


    I don't know how to do that, frankly. :)

    I just learned how to add "On Time" to close and save the worksheet after the macro runs, but I don't know how to use "On Time" to initiate the macro itself.

    Could you help with the code to add that to the front? Thx greatly!


    Have a macro that I'd like to run nightly but not sure how to make that happen.

    Have setup a scheduled task to open Excel, thinking it would allow me to run a macro automatically when open, but can't quite bridge the gap between opening Excel and initiating the macro to run unattended once open.

    Suggestions? Thanks!