"Shane King" wrote ...

I have a problem with converting a recordset to an array. I am using the
following code to get the array together but my problem is defining the
array dimensions.
I am oversizing the array to contain the data but i need to have exact
sizes. My seond problem is when you bring a recordset in that has various
datatypes how can you get an array to handle the various data types or am i
missing the point with the migration from queries to excel.

Some points you may be missing:

You can use the GetRows to return a zero-based variant array based on
the recorset, for which the dimensions will be dynamically sized for
you.
You can use Excel's Application.Transpose to manipulate the GetRows
results array into Excel's (rows,columns) format.
You can set an appropriately sized Range object's Value property to
equal the array transposed as above (Excel takes care of the base 0 to
base 1 conversion).
As an alternative to using an array, you can use Excel's
CopyFromRecordset to write the data to a Range.

For details, see:

http://support.microsoft.com/default...b;en-us;246335

BTW if you are changing values while looping through the recordset
which would prevent you from using the above approaches, you may be
achieve the same while fetching the data (i.e. rather than post
processing the data) by changing your SQL code and/or using data
shaping.

Jamie.

--