Executing an Excel 97 Macro using task scheduler!

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

Private Sub Workbook_Open()

End Sub

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

Reply | Quote

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!

Reply | Quote