VBA: Create Public Function for Access by mapping to Excel Object Library- Networkday

[mark6109]

I am trying to create a public function in VBA so I may use the Excel Networkdays function in Access, I am a rookie in VBA. I have already mapped to the Excel Object Library in VBA by going to references. I can see the function but do not know how to link to it to be able to use it in a query.
My end goal is to be able create an Access query and use the networkdays function, for example, take a [QC date] and subtract the [repair_date] but deduct the weekend days.

NETWORKDAYS([REPAIR DATE],[QC DATE],OPTIONAL OR [HOLIDAYS])

I have made several attempts to copy others code from researching this and tried creating my own public function without success.

Here is what I have that I can access in a query but it does not calculate correctly, or I should say at all Any help is much appreciated.

Public Function XLNETWORKDAYS(start_date As Date, end_date As Date, Optional Holidays As Date)
Dim objXL As Excel.Application
Set objXL = CreateObject("Excel.Application")
If IsMissing(Holidays) Then Holidays = False
XLNETWORKDAYS = objXL.WorksheetFunction.NetworkDays(start_date, end_date, Holidays)
Exit:
Exit Function

Thanks

 

[hgus393]

Hi,
don't know if ot helps you but check out this link:

http://www.access-programmers.co.uk/forums/showthread.php?t=37245

Cheers

Rob

 

[mark6109]

Thanks but I have tried this code before without success. I believe it is due to the link you provided was referencing Access 2000, in the first paragraph. I am using Access 2010.

Mark