Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What if scenario | Excel Discussion (Misc queries) | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) |