Running a Macro automatically

[davemel]

Is there anyway of running a Macro in a spreadsheet without having to open
the spreadsheet i.e i want to run a macro on a spreadsheet overnight
automatically to update it before the users come in to use it.



Thanks

David

[Bob Phillips]

No, but you could get that macro to close it down as well, so it wouldn't be
left around.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"davemel" <davemel@discussions.microsoft.com> wrote in message
news:1F88AE34-3A1C-4EEE-8804-4507067BA763@microsoft.com...
> Is there anyway of running a Macro in a spreadsheet without having to open
> the spreadsheet i.e i want to run a macro on a spreadsheet overnight
> automatically to update it before the users come in to use it.
>
>
>
> Thanks
>
> David

[Kim Greenlee]

Actually...yes.

But you have to do some stuff to set it up.

1) You need what I call 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

myExcelWorker.DefaultFilePath = strSaveDefaultPath

' No need to save the Worker because the results have all been extracted
oWorkBook.Save

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

That should do it for you.

Good luck,

Kim Greenlee

--
digipede - Many legs make light work.
Grid computing for the real world.
http://www.digipede.net
http://krgreenlee.blogspot.net