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

User · Accepted Answer

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 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

User · Answer

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 directory Dim WshShell Set WshShell   CreateObject  WScript Shell      Create an Excel instance Dim myExcelWorker Set myExcelWorker   CreateObject  Excel Application       Disable Excel UI elements myExcelWorker DisplayAlerts   False myExcelWorker AskToUpdateLinks   False myExcelWorker AlertBeforeOverwriting   False myExcelWorker FeatureInstall   msoFeatureInstallNone    Tell Excel what the current working directory is     otherwise it can t find the files  Dim strSaveDefaultPath Dim strPath strSaveDefaultPath   myExcelWorker DefaultFilePath strPath   WshShell CurrentDirectory myExcelWorker DefaultFilePath   strPath    Open the Workbook specified on the command-line  Dim oWorkBook Dim strWorkerWB strWorkerWB   strPath  amp    YourWorkbook xls   Set oWorkBook   myExcelWorker Workbooks Open strWorkerWB     Build the macro name with the full path to the workbook Dim strMacroName strMacroName        amp  strPath  amp    YourWorkbook   amp    Sheet1 YourMacro  on error resume next       Run the calculation macro    myExcelWorker Run strMacroName    if err number  lt  gt  0 Then         Error occurred - just close it down     End If    err clear on error goto 0   oWorkBook Save   myExcelWorker DefaultFilePath   strSaveDefaultPath    Clean up and shut down Set oWorkBook   Nothing    Don   t Quit   Excel if there are other Excel instances    running  Quit   will shut those down also if myExcelWorker Workbooks Count   0 Then    myExcelWorker Quit End If  Set myExcelWorker   Nothing Set WshShell   Nothing   You can test this VB Script from command prompt    gt  gt  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        amp  strPath  amp    YourWorkBook xlsm    amp     ModuleName MacroName

User · Answer

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 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  amp       amp  strMacro  wbToRun Save  wbToRun Close  objApp Quit    Leaves an onscreen message  MsgBox strPath  amp       amp  strMacro  amp    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

User · Answer

Code below copied from -  Here  First off  you must save your work book as a macro enabled work book  So it would need to be xlsm not an xlsx  Otherwise  excel will disable the macro s due to not being macro enabled    Set your vbscript  C  excel tester vbs   The example sub  test    must be located in your modules on the excel document    dim eApp set eApp   GetObject  C  excel tester xlsm   eApp Application Run  tester xlsm test  set eApp   nothing   Then set your Schedule  give it a name  and a username password for offline access    Then you have to set your actions and triggers     Set your schedule trigger     Action  set your vbscript to open with Cscript exe so that it will be executed in the background and not get hung up by any error handling that vbcript has enabled

User · Answer

I found a much easier way and I hope it works for you   using Windows 10 and Excel 2016   Create a new module and enter the following code       Sub auto open         Macro to be run  doesn t have to be in this module  just in this workbook      End Sub  Set up a task through the Task Scheduler and set the  program to be run as  Excel  found mine at C  Program Files  x86  Microsoft Office root Office16   Then set the  Add arguments  optional   as the file path to the macro-enabled workbook  Remember that both the path to Excel and the path to the workbook should be in double quotes    See example from Rich  edited by Community  for an image of the windows scheduler screen

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

Examples related to excel

Examples related to vbscript

Examples related to vba