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:\temp\rod.xlsm")
'either use the Workbook Open event (if macros are enabled), or Application.Run

ObjWB.Close False
ObjExcel.Quit
Set ObjExcel = Nothing

Answer 2

Three important steps - How to Task Schedule an excel.xls(m) file

simply:

1. make sure the .vbs file is correct
2. set the Action tab correctly in Task Scheduler
3. don't turn on "Run whether user is logged on or not"

IN MORE DETAIL...

1. Here is an example .vbs file:

`

'   a .vbs file is just a text file containing visual basic code that has the extension renamed from .txt  to .vbs

'Write Excel.xls  Sheet's full path here
strPath = "C:\RodsData.xlsm" 

'Write the macro name - could try including module name
strMacro = "Update" '    "Sheet1.Macro2" 

'Create an Excel instance and set visibility of the instance
Set objApp = CreateObject("Excel.Application") 
objApp.Visible = True   '   or False 

'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath) 
objApp.Run strMacro     '   wbToRun.Name & "!" & strMacro 
wbToRun.Save 
wbToRun.Close 
objApp.Quit 

'Leaves an onscreen message!
MsgBox strPath & " " & strMacro & " macro and .vbs successfully completed!",         vbInformation 
'

`

2. In the Action tab (Task Scheduler):

set Program/script: = C:\Windows\System32\cscript.exe

set Add arguments (optional): = C:\MyVbsFile.vbs

3. Finally, don't turn on "Run whether user is logged on or not".

That should work.

Let me know!

Rod Bowen