AUTOMATION REFERENCES

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.

heer,
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 (???!!)

John

heer
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
John

heer,
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.
John

Heer,
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)

   objExcel.Quit
   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.
Regards,
John

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
Next
' Add-In was not in the add-ins collection...
fAddInLoaded = -1
Exit Function
fAddInLoadedError:
' unknown error occurred
fAddInLoaded = -2
End Function

heer,
There's an excellent site on Analysis ToolPak Functions in VBA with that code etc.
http://www.rb-ad.dircon.co.uk/rob/excelvba/tips/index.htm


I thought it only fair to give you 100 points. Jump to:
http://oldlook.experts-exchange.com/questions/20810008/POINTS-FOR-HEER2351.html

MODERATOR, YOU CAN CLOSE THIS QUESTION NOW.
John

LOL that is exactly the site where I found the missing information.

I know :)
You must be a Google fan too!
John

Yep, and a knowledge freak :) main reason for me to post on this forum is to increase my knowledge, your question has added some.

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.
John