Solved: windows scheduler and application ontime

[ajm]

I have read that windows scheduler can be used in conjunction with the application ontime method to essentially run a macro in a "closed" workbook at a certain/specified time each day. Now, I have been able to figure out windows scheduler but not where to put the instruction to fire the macro I have in my workbook (name Updated Data.xls).

can anyone help me with the missing steps?

[Simon Lloyd]

Open the workbook with windows scheduler and put the ontime in the Thisworkbook code module in the Workbook_open event

[ajm]

i must have missed something with windows scheduler as i could only see where to put Excel in as a program. i couldn't see where to put the actual filename. will have another look when i get in to work tomorrow. thanks simon.

[ZVI]

Hi,

Copy the code below to Notepad and save it as file VbaTask.VBS
After that you can use VbaTask.VBS as the program for windows scheduler.

With CreateObject("Excel.Application")
  .Visible = True
  .Workbooks.Open "C:\MyFolder\UpdatedData.xls"  ' <-- change to suit
  .Run "MyMacro"    ' <-- change to suit
End With

Regards,
Vladimir

[ajm]

thanks you one and all (well simon and vladimir anyway). works like a treat. I added the VbaTask.VBS to the same folder as my spreadsheet, although, it probably didn't need to be there, and got it to open my file (by browsing for it in windows scheduler), which then runs an open event to update the data.

[ZVI]

I'm glad that our suggestions have helped
But only caution: such VBScript files (with VBS extension) load Excel workbook with allowed macros irrespective of Excel macro security level, so be careful with its scripts.

Regards,
Vladimir