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

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

This question is related to excel vbscript vba

The answer is


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.


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"

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 
'

`

  1. In the Action tab (Task Scheduler):

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

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

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

That should work.

Let me know!

Rod Bowen


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)

Set your 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.

Action Properties


Examples related to excel

Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support Converting unix time into date-time via excel How to increment a letter N times per iteration and store in an array? 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) How to import an Excel file into SQL Server? Copy filtered data to another sheet using VBA Better way to find last used row Could pandas use column as index? Check if a value is in an array or not with Excel VBA How to sort dates from Oldest to Newest in Excel?

Examples related to vbscript

How to run VBScript from command line without Cscript/Wscript How to set recurring schedule for xlsm file using Windows Task Scheduler How to prevent 'query timeout expired'? (SQLNCLI11 error '80040e31') How do I rename a file using VBScript? How to get two or more commands together into a batch file How to run vbs as administrator from vbs? Find specific string in a text file with VBS script Getting current directory in VBScript Run Command Line & Command From VBS Permission denied on CopyFile in VBS

Examples related to vba

Copy filtered data to another sheet using VBA Better way to find last used row Check if a value is in an array or not with Excel VBA Creating an Array from a Range in VBA Excel: macro to export worksheet as CSV file without leaving my current Excel sheet VBA: Convert Text to Number What's the difference between "end" and "exit sub" in VBA? Rename Excel Sheet with VBA Macro Extract Data from PDF and Add to Worksheet Quicker way to get all unique values of a column in VBA?