Access 2003 using Excel NetWorkdays function

  • Thread starter Thread starter Don W
  • Start date Start date
D

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
 
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
 
Back
Top