I can call mathematical / statistical excel spreadsheet functions directly from access but date functions are not recognised

e.g. the CHI or Sinh functions gives the right answers:
MsgBox Excel.Application.ChiInv(0.05, 10)
MsgBox Excel.Application.Sinh(1)

Date functions such as Today or Networkdays work within Excel but gets runtime error 438 when called, as below, from Access {Object does not support this property or method}
MsgBox Excel.Application.NETWORKDAYS("11/11/2003", "23/11/2003")
MsgBox Excel.Application.TODAY()

Even the older style setup Excel.Application.WorkSheetfunction.NETWORKDAYS..........etc doesn't work!!!

The library references are ticked. So whats going on here???
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try it like this:

  Dim xl As Object
  Set xl = CreateObject("Excel.Application")
  MsgBox xl.evaluate("today()")

For NETWORKDAYS make sure the Analysis addin is activated in Excel.
jjjtuohyAuthor Commented:
date1, date2 are input date values

I get a #error in the target textbox when I use:

Dim xl As Object
Set xl = CreateObject("Excel.Application")
Text6.Value = xl.Evaluate("NETWORKDAYS("date1, date2)")

or even when I try
Text6.Value = xl.Evaluate("NETWORKDAYS(" & date1 & "," & date2 & ")")

The Today() function works so maybe the problem is exposing the date variables (???!!)

jjjtuohyAuthor Commented:
Important points,
Networkdays is an excel worksheet function. It is not visible in EXCEL VBA even when I use application.worksheetfunction.netw...  in excel vba. How can it be visible in access VBA

There must be an answer here
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

jjjtuohyAuthor Commented:
Just for your interest, I've constructed the excel networkdays function as a single line access function

GetNumberOfWorkDays=Abs(5 - (Abs(Weekday(sStartDate, 7) - 2) + (Weekday(sStartDate, 7) - 2)) / 2 + (DateDiff("ww", sStartDate, sEndDate, 7) - 1) * 5 + (Abs(Weekday(sEndDate, 7) - 2) + (Weekday(sEndDate, 7) - 2)) / 2)

The evaluate function still does not work.
jjjtuohyAuthor Commented:
Evaluate does not work for addin functions. For anybody who may find this entry of interest, I have included a method to use the analysis toolpak addin in excel from access

Dim objExcel As Excel.Application
   Set objExcel = CreateObject("Excel.Application")

   ' Opens the Analysis ToolPak folder
   objExcel.Workbooks.Open (objExcel.Application.LibraryPath & "\Analysis\atpvbaen.xla")

   objExcel.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)

TOTAL_NETWORK.Value = objExcel.Application.Run("atpvbaen.xla!NETWORKDAYS", START_DATE.Value, END_DATE.Value)

   Set objExcel = Nothing

This function is inclusive of start and end dates. My previous function is exclusive of the start date. To make the Networkdays function exclusive of start date, simply add start date to the holiday list ie replace the fifth line with:
TOTAL_NETWORK.Value = objExcel.Application.Run("atpvbaen.xla!NETWORKDAYS", START_DATE.Value, END_DATE.Value,START_DATE.Value)
In this case it doesn't seem to need encapsulation in NameValue()

Heer,thank you for your time. I'm sorry I could not in honesty give you the points because you've been brilliant help over the months. I hope (optimistically) that I may have added a new item to your knowledge. I will ask the moderator to PAQ this question.
John, no problem.

It was getting late yesterday so I had no time to investigate this any further. However I did have a look at it today and came to an almost identical solution which I will post for reference:

'--------- Start code

Sub testNetworkdays()
  Dim xl As Object
  Set xl = CreateObject("Excel.Application")

  intRet = fAddInLoaded("Analysis ToolPak - VBA", xl)

  MsgBox xl.Run("Networkdays", DateSerial(2003, 11, 10), DateSerial(2003, 10, 8))
End Sub

Function fAddInLoaded(strAddInTitle As String, xl As Object) As Integer
   ' Finds if an add-in is loaded and attempts to
   ' load it if it is not. Returns zero if the
   ' add-in is loaded, 1 if the function had to
   ' load the add-in, -1 if the add-in could
   ' not be located and -2 if an error occurred.

Dim adnAddIn As AddIn

On Error GoTo fAddInLoadedError

' Search through add-ins collection for
' our add-in...
For Each adnAddIn In xl.AddIns
    If adnAddIn.Title = strAddInTitle Then
        ' Found it! Is it installed?
        ' Return success value
        fAddInLoaded = 0
        If Not adnAddIn.Installed Then
            ' If it's not installed then
            ' install it...
            AddIns(strAddInTitle).Installed = True
            ' Return value indicating that we
            ' had to load the add-in
            fAddInLoaded = 1
        End If
        ' Get out now rather continuing
        ' to loop unnecessarily
        Exit Function
    End If
' Add-In was not in the add-ins collection...
fAddInLoaded = -1
Exit Function
' unknown error occurred
fAddInLoaded = -2
End Function
jjjtuohyAuthor Commented:
There's an excellent site on Analysis ToolPak Functions in VBA with that code etc.

I thought it only fair to give you 100 points. Jump to:

LOL that is exactly the site where I found the missing information.
jjjtuohyAuthor Commented:
I know :)
You must be a Google fan too!
Yep, and a knowledge freak :) main reason for me to post on this forum is to increase my knowledge, your question has added some.
jjjtuohyAuthor Commented:
Add to your knowledge? This site TAUGHT me Access VBA! I hope you've copied the one-line function I constructed to do the same thing. I'm quite proud of it. There's a kind of purity in such challenges.
PAQed, with points refunded (250)

E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.