call excel functions in access

[dcx693]

Call an Excel function from Access

As long as you've got Access and Excel on the same machine, you can make a simple automation call to Excel and use Excel's built-in functions. Here's a simple example using the Excel Small function:

Function GetNthNumber(intPosition As Integer, _
    ParamArray dblNums() As Variant) As Double
Dim obj As Object

    Set obj = CreateObject("Excel.Application")
        GetNthNumber = obj.Small(Array(dblNums()), intPosition)
    obj.Quit
    Set obj = Nothing

End Function

This function sends Excel an array of numbers and asks it to locate the nth smallest one in the array. Just call the function from Access like:

dblAnswer=GetNthNumber(2,1,2,4,2,5,7)

Just be aware that each time you call the function, that you're making a separate automation call to Excel. You'll have to test it in your specific case to see if the speed is acceptable.

 

[Richardio]

call an Excel function in Access

Hello dcx693,

Could you please explain in detail how I would call an Excel function in Access. Please provide me with the steps as I am not very good with codes.

There is a Networkdays function in excel: networkdays(start date, end date, holidays). Please tell me specificially how to call this particular function in Access. Do I have to have an excel workbook open? Where do I put the code in Access e.t.c.

Thanks for taking time out to help. Thanks a lot.


Richardio.

 

[Richardio]

Hello all:

I need your urgent help with the following. Could you please explain in detail how I would call an Excel function in Access. Please provide me with the steps as I am not very good with codes.

There is a Networkdays function in excel: networkdays(start date, end date, holidays). Please tell me specificially how to call this particular function in Access. Do I have to have an excel workbook open? Where do I put the code in Access e.t.c.

Thanks for taking time out to help. Thanks a lot.


Richardio.

 

[namliam]

Search the forum, i think Mile created a simular function for Access (counting workdays between 2 dates)

Regards

 

[Richardio]

Thanks, but the function Mile suggested just calculates days between the 2 dates. The networkdays in Excel calculates Workdays between 2 dates and also considers holidays in its calculation. I use this function in Excel, but I want to call it in Access. Does anyone know how to call the Networkdays function exactly from Excel to Access?

Thanks a lot for all your help in advance.


Richardio

Search the forum, i think Mile created a simular function for Access (counting workdays between 2 dates)

Regards

 

[Richardio]

Using Excel functions from within Access

Hello all:

I have the following code to call an Excel Function (Networkdays) from within Access but I am getting "Object doesn't support this property or method". I set the reference in the Module by pressing Ctrl G and then picked Microsoft Excel 10.0. I am using Access 2000 and Excel 2000. Do you have any idea why I am having this error.
Thanks a lot.


Richardio


Function fXLNETWORKDAYS(start_date As Date, end_date As Date, Optional holidays As Date) As Long
On Error GoTo E_Handle
Dim objXL As Excel.Application
Set objXL = CreateObject("Excel.Application")
If IsMissing(holidays) Then holidays = False
fXLNETWORKDAYS = objXL.WorksheetFunction.NETWORKDAYS(start_date, end_date, holidays)
fExit:
objXL.Quit
Set objXL = Nothing
Exit Function
E_Handle:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
Resume fExit
End Function

 

[Mile-O]

Thanks, but the function Mile suggested just calculates days between the 2 dates.

You obvoulsy haven't found the correct one. And stop posting to the Code Repository - it's not for questions but for people to share code snippets which you would have understood if you'd read the Please Read Before Posting thread stuck to the top of that forum.