Task Schedule for Macro

M

mike0021

Hi all,,

Here is a quick question- trying to run my macro every morning with th
windows 2000 task scheduler.

in the "run" box, I put this:

"G:\Mike\Allmort Composite SWP.xls" /x mailselection

and do "start in"

G:\Mike


It loads up my excel file, but the macro does not run. What am I doin
wrong?

Thanks!
Mik
 
M

Michel Pierron

HI mike0021,
To start by writing with NotePad a small script such as:

' Here, the full path name of the workbook to open
Const xlName = "G:\Mike\Allmort Composite SWP.xls"
Dim xl, Wbk
Set xl = WScript.CreateObject("Excel.Application")
Set Wbk = xl.Workbooks.Open(xlName)
' Here, the workbook macro name to execute (in a standard module)
xl.Run "MyMacro"
Wbk.Save
Wbk.Close False
xl.Quit
Set xl = Nothing

save this file in the same directory "G:\Mike\" with the .vbs extension (for
example: MyMacroToRun.vbs).

In the task scheduler, put in the "run" box:
"G:\Mike\MyMacroToRun.vbs"

Regards,
MP
 
M

mike0021

Thank you so much! It worked! One little followup though- part of m
excel spreadsheet uses the WORKDAY function, and this function i
giving me #NAME? when running the macro through this script, however i
works if I load excel and run interactively.

This WORKDAY function needs the Analysis Tool-pak, an addin. Is i
possible that this addin doesn't load without actually opening u
excel. Is there any work around if this is the issE?

Thanks again!
 
M

Michel Pierron

Hi mike0021,
In the vbs script, you can add (*):
Set xl = WScript.CreateObject("Excel.Application")
xl.AddIns("Analysis Tool-pak").Installed = True ' (*)

Regards,
MP
 
Top