Excel: Running Excel on Windows Task Scheduler
One thing that comes up from time to time on the Excel message boards is “How do I run Excel without opening it?” Basically the developer wants to run Excel programmatically as a COM server without any human intervention. I’ve seen folks recommend “Excel.exe workbook.xls” and making sure that the macro you want to execute is called from the Workbook_Open() event. The problem with this technique is control. The Excel developer would have to use the state machine methodology I mentioned previously, which is just a pain in the %$$.
I think the best way to run Excel without user interaction is using an Excel Controller. An Excel Controller is a script, DLL, or executable that uses Excel’s COM interface. This technique also answers the question of “How do I run Excel from a scheduler?”
So I’m going to answer the more complicated question of how to run Excel from Microsoft Task Scheduler.
1) You need an Excel Controller. If you’re comfortable with VBA then you’ll probably be comfortable with VBS. (Visual Basic Script).
Here is some sample code I use to control and automate an Excel workbook:
' Create a WshShell to get the current directory
Set WshShell = CreateObject("WScript.Shell")
' Create an Excel instance
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)
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = WshShell.CurrentDirectory
myExcelWorker.DefaultFilePath = strPath
' Open the Workbook specified on the command-line
strWorkerWB = strPath & "\YourWorkbook.xls"
Set oWorkBook = myExcelWorker.Workbooks.Open(strWorkerWB)
' Build the macro name with the full path to the workbook
strMacroName = "'" & strPath & "\YourWorkbook" &
on error resume next
' Run the calculation macro
if err.number <> 0 Then
' Error occurred - just close it down.
on error goto 0
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
Set myExcelWorker = Nothing
Set WshShell = Nothing
2) In the Excel Controller code 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.
3) Launch the VBS Excel Controller using cscript.exe NOT wscript.exe. So let’s say you named the script RunExcel.vbs, then you would execute it using ‘cscript.exe RunExcel.vbs’. (If you make 'YourWorkbook' and 'YourMacro' input
parameters then the RunExcel.vbs script can run any macro in any workbook because you can just pass that information in on the command-line.)
4) Once you have the Excel Controller and workbook tested so that it does what you want, you can then use Microsoft Task Scheduler to execute ‘cscript.exe RunExcel.vbs’ automatically for you. I found a tutorial here:
If the Excel Controller is a script or executable, the developer has the option of passing parameters in to the Controller which can be used to initialize cells in the workbook before the macro is executed. Obviously, using the Excel COM APIs the developer can really do some neat things by expanding on the basic structure I have provided. I hope this helps someone and if you have neat additions to this, please feel free to add them in the comment section.