Access 2003 using Excel NetWorkdays function

[Don W]

The following code works in Office 12.0, but I'm using Office 11.0. Is there
some difference in the way that function must be called between the two
functions? The MsgBox does display the median (from
http://support.microsoft.com/?id=153748), but errors on the next line with
"Run-time error '438': Object doesn't support this property or method"

Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
Dim obj As Excel.Application, WorkDays As Long
Set obj = CreateObject("Excel.Application")
WorkDays = obj.Application.NetWorkdays(StartDate, EndDate, Holidays())
MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
obj.Quit
Set obj = Nothing
NetWorkdays = WorkDays
End Function

Function Holidays() As Date()
Dim rs As Recordset, counter As Long
Set rs = CurrentDb.OpenRecordset("Holidays")
Dim hd(100) As Date
Do While rs.EOF = False
hd(counter) = rs("Holidate")
rs.MoveNext
counter = counter + 1
Loop
Holidays = hd
End Function

 

[Don W]

Ooops, sorry for any confusion -- the code should be this (the median
function works and the error occurs on the following NetWorkdays function):

Public Function NetWorkdays(StartDate As Date, EndDate As Date) As Long
Dim obj As Excel.Application, WorkDays As Long
Set obj = CreateObject("Excel.Application")
MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13)
WorkDays = obj.Application.NetWorkdays(StartDate, EndDate, Holidays())
obj.Quit
Set obj = Nothing
NetWorkdays = WorkDays
End Function

Function Holidays() As Date()
Dim rs As Recordset, counter As Long
Set rs = CurrentDb.OpenRecordset("Holidays")
Dim hd(100) As Date
Do While rs.EOF = False
hd(counter) = rs("Holidate")
rs.MoveNext
counter = counter + 1
Loop
Holidays = hd
End Function

 

[Douglas J. Steele]

Don't bother going through Excel.

For the equivalent of the NetWorkdays function, see my September, 2004
"Access Answers" column in Pinnacle Publcation's "Smart Access" You can
download the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html