Passing array from Access to Excel function

[Dale]

Wasn't sure if this was an access or excel question but here it goes:

Situation:
In an Access database (A2k) I have a recordset containing 85,000 records of
which
I want to pass an array to Excel to calculate the 90th percentile. Excel
(XL2003) can
only tolerate 65536 rows of data.

Question:
Can I split the data into two columns and pass both columns as an array to
Excel?
I'm not sure how would I reference the arguments for the function without
getting an invalid
arguments error if there are two columns?

Here's what I have for one column:
Result = appXL.Percentile((varArray()), 0.9)

Thanks for taking the time to help me out...
dale

 

[Guest]

Dale:

The following is not exactly correct:

.... Excel
(XL2003) can
only tolerate 65536 rows of data.

I have not tested an array but if you wish to import a recordset
with more than 65536 records, Excel looks for the EOF, you do
however need to allow for the additional records over 65+K and
consider the amount of fields you wish to inport.

For example the following code will import 65k + records
with 3 field in col a b and c and the balance starting at d10

Worksheets("Sheet3").Range("A:A,C:C").CopyFromRecordset rs
'/If more than 65,536 records you must add a second line
'/Starts at D10
Worksheets("Sheet3").Range("D10:f10000").CopyFromRecordset rs

You may want to compare bringing in the records with
CopyFromRecordset vs an array. In my test Excel seems to
perfer the former.

Good Luck
TK

 

[Robin Hammond]

Dale,

I'm just wondering why this has to be done in Excel. Figuring this out in
SQL is not that tough. e.g. here's a query that returns the lowest value in
the top 10%:

SELECT MIN(FieldName) FROM ViewOrTableName WHERE
FieldName in
(select top 10 percent FieldName FROM ViewORTableName
ORDER BY FieldName DESC)

As with all things in SQL I'm sure there are better ways of doing this but
for 85000 rows it's a good enough solution.

In answer to your question, yes you can by copying individual values from
the recordset into cells spanning several columns, setting a range that
covers these columns as the parameter for the percentile function, then
using the function. Seems a long way around though.

Robin Hammond
www.enhanceddatasystems.com

 

[Dale]

Thanks TK..I really wanted to run this from within Access rather than
transferring to excel.

 

[Dale]

Thanks Robin, I don't disagree with you, I used this function to illustrate
what my "need"
was. Once the code is working I will be using other functions. Basically
I'm too cheap to buy a Statistical software package for Access...this is my
workaround.

I'm still not quite sure how to pass two columns of data when the function
is expecting only one column.

 

[Guest]

Dale:

Thanks TK..I really wanted to run this from within Access rather than
transferring to excel.

Doing this in Access as Robin points out is pretty strighrforward.

Maybe I misread your question

TK