excel - How to set recurring schedule for xlsm file using Windows Task Scheduler

Question

I have a xlsx macro enabled file . How can I set it in the task manager so that everyday at 9 AM task manager would open the workbook, fire the macro and close the workbook.

So far i am using

Application.OnTime . . .

But i realize that keeping the xlsm file open is inconvenient

Answer 1

Better to use a vbs as you indicated

1. Create a simple vbs, which is a text file with a .vbs extension (see sample code below)
2. Use the Task Scheduler to run the vbs
3. Use the vbs to open the workbook at the scheduled time and then either:
   • use the Private Sub Workbook_Open() event in the ThisWorkbook module to run code when the file is opened
   • more robustly (as macros may be disabled on open), use Application.Run in the vbs to run the macro

See this example of the later approach at Running Excel on Windows Task Scheduler

sample vbs

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("excel.application")
'vbs opens a file specified by the path below
Set ObjWB = ObjExcel.Workbooks.Open("C:emp
od.xlsm")
'either use the Workbook Open event (if macros are enabled), or Application.Run

ObjWB.Close False
ObjExcel.Quit
Set ObjExcel = Nothing