Schedule Excel Macros with Parameters

I had this task assigned today and I thought about documenting where I found the pieces of the puzzle.

Of course we are talking about Windows OS here and so it makes sense to use just the Task Scheduler to set when the script should be run.

There is an excellent post that helped me start building the script. The only thing I added to it is the possibility to accept parameters that are injected into an Excel Subroutine that accepts parameters. As Excel will not show such subroutine as a valid macro if you want to call it from inside Excel you will need to use a non parametrized Sub that calls with test parameters the first one.

Below is the code for both of the Subroutines in Excel:

Option Explicit

' This is the routine that can accept parameters
Sub RunMacroFromParameters(param1 As String, param2 As String,  param3 As String)
     Range("E7").Value = param1
     Range("E8").Value = param2
     Range("J8").Value = param3
     MyMacroNeedingE7E8J8CellsAsInputParameters
End Sub

'Use this to test 
Sub ParametersTest()    
    RunMacroFromParameters "Miami", "1/1/2010", "3"
End Sub

Below is the code resulting from modifications on the original post

'''''''''''''''''''''''''''''''''''''''''''''''
'
' @Author: Modified from http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html#c4023873001264863808
' @Created: 10/01/2010
'
'
' @Description: Runs an Excel Macro and saves the result in a file adding the time stamp
'
' @Parameters (The first parameter if exists is added to the file name):
' 1. complete Path to the excel book
' 2. Name of the Macro
' 3. Extra parameters to be accepted by the Macro procedure. 
'  
'  
'
'
'
''''''''''''''''''''''''''''''''''''''''''''''''

'Create a WshShell to get the current directory

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

If (Wscript.Arguments.Count < 2) Then
Wscript.Echo "Runexcel.vbs - Required Parameter missing"
Wscript.Quit
End If


'retrieve the arguments

Dim strWorkerWB
strWorkerWB = Wscript.Arguments(0)


Dim strMacroName
strMacroName = Wscript.Arguments(1)

Dim firstExtraParameter
firstExtraParameter = Wscript.Arguments(2)

Dim strMacroParams
If (Wscript.Arguments.Count > 2) Then
    For i = 2 To Wscript.Arguments.Count - 1
        strMacroParams = strMacroParams & " ,""" & Wscript.Arguments(i)    & """"    
    Next    
End If


' 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


Dim StrPathNameNew

' Open the Workbook specified on the command-line
Dim oWorkBook

'it opens the file readonly
Set oWorkBook = myExcelWorker.Workbooks.Open (strWorkerWB,,True)

'on error resume next
' Run the calculation macro
strCommand = "myExcelWorker.Run " & """" & strMacroName & """" & strMacroParams
'Wscript.Echo "Runexcel.vbs - strCommand=" & strCommand 
Execute(strCommand )


if err.number <> 0 Then
' Error occurred - just close it down.
End If
err.clear
on error goto 0

Dim optionalToken
If IsNull(firstExtraParameter) or IsEmpty(firstExtraParameter) Then
    optionalToken = "_" & firstExtraParameter
End If

StrPathNameNew = replace(ucase(strWorkerWB),".XLS","") & optionalToken & "_" & year(Date()) & right("0" & month(date()),2) & right("0" & day(date()),2) & "_" & right("0" & hour(now()),2) & right("0" & minute(now()),2) & right("0" & second(now()),2) & ".XLS"
oWorkBook.SaveAs StrPathNameNew
oWorkBook.Close

' 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

Now you can run the command from the scheduler

C:\>"c:\RunExcel.vbs" "c:\myExcelWithMacro.xls" "RunMacroFromParameters" "Fort Lauderdale" "8/1/2010" "10"

Note that the script is generic enough to allow for automation of any Excel Macro whether it expects parameters or not. BTW the file is opened readonly as a new file is created everytime the script is run.