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

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:
http://www.iopus.com/guides/winscheduler.htm

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.

Technorati tags: Excel, VBA, VBS, Microsoft, Automated, Microsoft Task Scheduler