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

[TK]

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


"Dale" wrote:

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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[Dale]

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


"TK" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Dale" wrote:
>
>> 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
>>
>>
>>
>>

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

"Robin Hammond" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>
>

[TK]

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

> >> I want to pass an array to Excel to calculate the 90th percentile. Excel
> >> (XL2003) can.....................

TK


"Dale" wrote:

> Thanks TK..I really wanted to run this from within Access rather than
> transferring to excel.
>
>
> "TK" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> > "Dale" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
>
>
>