Schedule an Excel macro?

    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!

    No.


    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!


    Jim

    Just check the VBA help for OnTime. You would write a separate macro that just schedules the one you want to run a few minutes after you will be opening Excel. I'm assuming whatever is opening Excel is also opening the appropriate workbook (I don't know if ontime is smart enough to open the workbook that has the scheduled macro). Of course you could also just leave Excel running until the designated time.

    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!!:)

    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?

    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?

    Quote

    Originally posted by jwhitwell
    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!!:)


    j:-


    From above it sounds like your scheduler launches Excel. So opening Excel happen automatically ?


    If so the answer is simple and you can still use the OnTime event to trigger your code.


    All you should need to do is use the code in


    Private Sub Workbook_Open()
    Application.OnTime TimeValue("17:00:00"), "my_Procedure"
    End Sub


    This goes in VBAProject (Personal.xls) > Microsoft Excel Objects > This Workbook


    What should happen when Excel launches your Personal.xls file is loaded. When this file is loaded the code triggers due to the Workbook_Open event. The OnTime then initiates the "My Procedure" at the specified time.


    The Ontime event will not work if Excel is not open, it does not set a flag in the OS to relaunch Excel and run your macro at a given time. However if you can get your scheduler to launch Excel then you can get the Macro to run.


    Hope this helps.


    AJW

    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!

    The part of your code that starts

    Code
    Private Sub Workbook_Open() 
Application.OnTime TimeValue("16:07:00"), "BDSUpdate"


    should not be in your BDSUpdate macro. It should be a separate macro in the Thisworkbook section of your Microsoft Excel Objects. One way to see if it's working correctly is to put a MsgBox into the macro and see if it comes up automatically when personal.xls is opened.

    thx.


    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?

    How would I go about triggering the macro right from the Scheduler?


    For Access it's simple enough, For example ...
    "C: Program FilesMicrosoft OfficeOfficeMsaccess.exe" "C:My DocumentsWorkAutomation2Operator.mdb" /x Clear D_PDT Tables


    The switch /x calls the macro perfectly.


    Not so much in Excel.


    Is it even possible?


    Tos

Participate now!

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

Register Yourself Login