Statement works in one scenario, not in other
 

I have this code:

Dim MonthNum as Integer
Dim CurrentMonth as Integer
Dim MemorialDate as Date

'The variables get set to:
CurrentMonth = 4
MemorialDate = 9/6/2004

MonthNum = Evaluate("=NETWORKDAYS(DATEVALUE(""" &
CurrentMonth & "-1-" & Year(Date) & """),DATEVALUE(""" &
CurrentMonth & "-" & i & "-" & Year(Date) & """), {""1/1/"
& Year(Date) & """,""7/4/" & Year(Date) & """,""" &
MemorialDate & """})")

If I run the code it works. This is what the statement
looks like with all of the variables filled in:

=NETWORKDAYS(DATEVALUE("4-1-2004"),DATEVALUE("4-9-2004"),
{"1/1/2004","7/4/2004","9/6/2004"})

So it wants to know how many workdays there are between
April 1st, 2004 and April 9th, 2004. For holidays I have
New Years Day, Independance Day and Memorial Day (which is
determined by a previous procedure).

The result is 7, which is correct. Okay, so far so good.

Here's the problem. I have a vbscript that opens the
workbook in memory and runs the procedure, then closes the
workbook. The script looks like this:

Dim xl, CurrentBook,

Set xl = CreateObject("Excel.Application")
Set CurrentBook = xl.Workbooks.Open
("C:\Path\ReportName.xls")
xl.Run "Main"
CurrentBook.Save
CurrentBook.Close

xl.Quit

set CurrentBook = nothing
set xl = nothing

So nothing fancy. Create an instance of Excel, open the
workbook, run the procedure called Main, save the results,
close the workbook, close Excel, destroy objects.

All of the code in the workbook runs fine except when it
gets to the Evaluate statement, there is a Type Mismatch
error. The only thing I can think of is that the Analysis
ToolPak add-ins don't load when the script opens the
workbook. But....

Sorry so wordy, but what might be going wrong?

tod

Statement works in one scenario, not in other
 

Perfect idea. Thanx.

-----Original Message-----
Another idea:

Put a reference to the analysis toolpak (atpvbaen.xls)
(tools|references in the
VBE).

Then use the networkdays directly (just like a built in
function). You won't
need evaluate() at all.

xl will open that referenced workbook.

Tod wrote:

I have this code:

Dim MonthNum as Integer
Dim CurrentMonth as Integer
Dim MemorialDate as Date

'The variables get set to:
CurrentMonth = 4
MemorialDate = 9/6/2004

MonthNum = Evaluate("=NETWORKDAYS(DATEVALUE(""" &
CurrentMonth & "-1-" & Year(Date) & """),DATEVALUE(""" &
CurrentMonth & "-" & i & "-" & Year(Date) & """),
{""1/1/"
& Year(Date) & """,""7/4/" & Year(Date) & """,""" &
MemorialDate & """})")

If I run the code it works. This is what the statement
looks like with all of the variables filled in:

=NETWORKDAYS(DATEVALUE("4-1-2004"),DATEVALUE("4-9-
2004"),
{"1/1/2004","7/4/2004","9/6/2004"})

So it wants to know how many workdays there are between
April 1st, 2004 and April 9th, 2004. For holidays I have
New Years Day, Independance Day and Memorial Day (which
is
determined by a previous procedure).

The result is 7, which is correct. Okay, so far so good.

Here's the problem. I have a vbscript that opens the
workbook in memory and runs the procedure, then closes
the
workbook. The script looks like this:

Dim xl, CurrentBook,

Set xl = CreateObject("Excel.Application")
Set CurrentBook = xl.Workbooks.Open
("C:\Path\ReportName.xls")
xl.Run "Main"
CurrentBook.Save
CurrentBook.Close

xl.Quit

set CurrentBook = nothing
set xl = nothing

So nothing fancy. Create an instance of Excel, open the
workbook, run the procedure called Main, save the
results,
close the workbook, close Excel, destroy objects.

All of the code in the workbook runs fine except when it
gets to the Evaluate statement, there is a Type Mismatch
error. The only thing I can think of is that the
Analysis
ToolPak add-ins don't load when the script opens the
workbook. But....

Sorry so wordy, but what might be going wrong?

tod

--

Dave Peterson

.