Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Executing an Excel 97 Macro using task scheduler!

    What would be the syntax to execute a macro in EXCEL 97 from outside EXCEL? i am trying to run the same macro using task scheduler each morning.

    Thanks[img]/w3timages/icons/yikes.gif[/img]

  2. #2
    Star Lounger
    Join Date
    Dec 2000
    Posts
    70
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing an Excel 97 Macro using task scheduler!

    In other MS Office apps, you would put a command line switch to start the app and also run the macro, but I don't think you can do that in Excel. I think your strategy would go something like this:

    (In this example, let's assume you schedule the task to run in task scheduler at 8:00 AM.)

    1. Put the command line that starts Excel ("C:Program FilesMicrosoft OfficeOfficeExcel.exe") in task scheduler.

    2. Put the file that contains the macro you want to run in your XLStart directory.

    3. Put the following code in the file's Workbook_Open event procedure:

    Private Sub Workbook_Open()
    Application.OnTime EarliestTime:=TimeValue("8:01 AM"), Procedure:="YourMacroHere"
    End Sub

    Notice that the time I put in Excel's OnTime method is 1 minute later than the time you schedule in task scheduler. The reason the OnTime method is necessary here is because otherwise your macro would run *every* time you launched Excel, which I'm sure you don't want.

    Stephan Ip

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing an Excel 97 Macro using task scheduler!

    There are several approaches that you can take to accomplish this, and Stephan has given you one. However, it does have a possible problem of having the macro run when you don't want it to.

    Another method would be to create another workbook that contains an Auto_Open macro. You can then schedule Excel to run with this workbook as a parameter on the command line. The Auto_Open macro would then open workbook 2 that contains the data and the macro that you want to run, and it would then run the macro. This way, when you just open workbook 2, the macro would not run.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing an Excel 97 Macro using task scheduler!

    i like the auto open idea. what i have now is an excel spreadsheet that contains a macro that will read in my external data file, format it and then save it as an excel file and when done the macro contains command to exit excel. what i would like is to use the task scheduler to open excel using this excel document(with the macro) as a parameter so that it runs the macro executes automatically on open and then it will shut itself down.

    how do i define the macro within this excel document as an auto_open macro?

    thanks
    mike

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing an Excel 97 Macro using task scheduler!

    In the VBA Editor, in the project explorer, right click on the workbook and select view code from the pop up menu. At the top of the VB editor window in the left drop down list where is should say "General," drop the list down and select "Workbook." If the right drop down list does not now say "Open" then drop the list down and select "Open." The edit area should now contain:

    <pre>Private Sub Workbook_Open()

    End Sub
    </pre>


    Put your Auto_Open code between those two lines. It should open when the workbook is opened.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing an Excel 97 Macro using task scheduler!

    another concern dealt a cross check into the boards. thanks legare for you help and thanks stephan for your insight as well.

    have a great weekend and
    keep your sticks on the ice!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •