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.
2 comments:
One thing tricky with scheduling macros is the Excel.exe process may not exit. It is a good idea to put an
On Error Resume Next
and
If Not (workbook Is Nothing) Then workbook.close False
and also release memory
set workbook = Nothing
in case there is an error for whatever reason, that way you don't log into the server running the schedules one day and see 15 Excel processes running!
I thinkthe code is already accounting to close the book:
' Clean up and shut down
Set oWorkBook = Nothing
It is a bad idea to close Excel as the code also says:
' 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
Post a Comment