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 · 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 & "\YourWorkbook.xls"

Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "'" & 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.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:

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

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

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

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