How to set recurring schedule for xlsm file using Windows Task Scheduler
Better to use a vbs as you indicated
- Create a simple
vbs
, which is a text file with a .vbs extension (see sample code below) - Use the Task Scheduler to run the
vbs
- Use the
vbs
to open theworkbook
at the scheduled time and then either:- use the
Private Sub Workbook_Open()
event in theThisWorkbook
module to run code when the file is opened - more robustly (as macros may be disabled on open), use
Application.Run
in thevbs
to run the macro
- use the
See this example of the later approach at Running Excel on Windows Task Scheduler
sample vbs
Dim ObjExcel, ObjWBSet ObjExcel = CreateObject("excel.application")'vbs opens a file specified by the path belowSet ObjWB = ObjExcel.Workbooks.Open("C:\temp\rod.xlsm")'either use the Workbook Open event (if macros are enabled), or Application.RunObjWB.Close FalseObjExcel.QuitSet ObjExcel = Nothing
Three important steps - How to Task Schedule an excel.xls(m) file
simply:
- make sure the .vbs file is correct
- set the Action tab correctly in Task Scheduler
- don't turn on "Run whether user is logged on or not"
IN MORE DETAIL...
- 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 herestrPath = "C:\RodsData.xlsm" 'Write the macro name - could try including module namestrMacro = "Update" ' "Sheet1.Macro2" 'Create an Excel instance and set visibility of the instanceSet objApp = CreateObject("Excel.Application") objApp.Visible = True ' or False 'Open workbook; Run Macro; Save Workbook with changes; Close; Quit ExcelSet 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 '
`
- In the Action tab (Task Scheduler):
set Program/script: = C:\Windows\System32\cscript.exe
set Add arguments (optional): = C:\MyVbsFile.vbs
- Finally, don't turn on "Run whether user is logged on or not".
That should work.
Let me know!
Rod Bowen
I referred a blog by Kim for doing this and its working fine for me. See the blog
The automated execution of macro can be accomplished with the help of a VB Script file which is being invoked by Windows Task Scheduler at specified times.
Remember to replace 'YourWorkbook' with the name of the workbook you want to open and replace 'YourMacro' with the name of the macro you want to run.
See the VB Script File (just named it RunExcel.VBS):
' Create a WshShell to get the current directoryDim WshShellSet WshShell = CreateObject("WScript.Shell")' Create an Excel instanceDim myExcelWorkerSet myExcelWorker = CreateObject("Excel.Application") ' Disable Excel UI elementsmyExcelWorker.DisplayAlerts = FalsemyExcelWorker.AskToUpdateLinks = FalsemyExcelWorker.AlertBeforeOverwriting = FalsemyExcelWorker.FeatureInstall = msoFeatureInstallNone' Tell Excel what the current working directory is ' (otherwise it can't find the files)Dim strSaveDefaultPathDim strPathstrSaveDefaultPath = myExcelWorker.DefaultFilePathstrPath = WshShell.CurrentDirectorymyExcelWorker.DefaultFilePath = strPath' Open the Workbook specified on the command-line Dim oWorkBookDim strWorkerWBstrWorkerWB = strPath & "\YourWorkbook.xls"Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)' Build the macro name with the full path to the workbookDim strMacroNamestrMacroName = "'" & strPath & "\YourWorkbook" & "!Sheet1.YourMacro"on error resume next ' Run the calculation macro myExcelWorker.Run strMacroName if err.number <> 0 Then ' Error occurred - just close it down. End If err.clearon error goto 0 oWorkBook.Save myExcelWorker.DefaultFilePath = strSaveDefaultPath' Clean up and shut downSet oWorkBook = Nothing' Don’t Quit() Excel if there are other Excel instances ' running, Quit() will shut those down alsoif myExcelWorker.Workbooks.Count = 0 Then myExcelWorker.QuitEnd IfSet myExcelWorker = NothingSet WshShell = Nothing
You can test this VB Script from command prompt:
>> cscript.exe RunExcel.VBS
Once you have the VB Script file and workbook tested so that it does what you want, you can then use Microsoft Task Scheduler (Control Panel-> Administrative Tools--> Task Scheduler) to execute ‘cscript.exe RunExcel.vbs’ automatically for you.
Please note the path of the macro should be in correct format and inside single quotes like:
strMacroName = "'" & strPath & "\YourWorkBook.xlsm'" & "!ModuleName.MacroName"