T
Tod
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
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