Wanting to call excel function for quartile in acc
Hello,
I thank you in advance for your time and expertise. I am working on a project for a non-profit and am hoping to get some help in getting the quartile function that is in Excel and use it in Access. I have played with getting other functions working from excel, but when I try to use any formula that requires an array, I can't get it working.Unfortunately they have Access 2000 and I am not a very experienced programmer. I have one data_table, the field is data. I found this code on the web, but can't get it working. I keep getting an error "cannot find worksheet function". I added the reference to excel through tools, but still nothing.
Thank you for any help, advice or good thoughts.
Public Function Percentile(strTbl As String, strFld As String, k As Double)
As Double
Dim rst As ADODB.Recordset
Dim dblData( ) As Double
Figure 7-11. Calling the FV function from a form
Figure 7-12. Message box displayed from the cmdFV_Click event
200 | Chapter 7, External Programs and Data
#61 Use Excel Functions Inside Access
HACK
Dim xl As Object
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set rst = New ADODB.Recordset
rst.Open "Select * from " & strTbl, CurrentProject.Connection,
adOpenStatic
ReDim dblData(rst.RecordCount - 1)
For x = 0 To (rst.RecordCount - 1)
dblData(x) = rst(strFld)
rst.MoveNext
Next x
Percentile = xl.WorksheetFunction.Percentile(dblData, k)
rst.Close
Set rst = Nothing
Set xl = Nothing
End Function
Private Sub cmdPercentile_Click( )
Dim dblPercentile As Double
dblPercentile = Percentile("tblData", "SampleData", txtK)
MsgBox "Percentile = " & dblPercentile, vbInformation, "Percentile"
End Sub
|
