ADO will make a best-guess at the data type, and it must be thinking these
are strings.

Why not just continue with the first way that surprised you and worked for
you?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Chris Short" wrote in message
...
I'm extracting external numeric data from tables where the field type is
defined as a fixed length string (ie there are extra spaces in the field
if
the length of the number is less than the field size).

Using ADO and the getRows method of a recordset to pass the results to an
array before populating the range in the worksheet results in the strings
being automatically converted to a number (except for entries that are
definitely strings, such as all spaces of na etc).

That was a feature of the .value method of a range I was pleasantly
surprised by.

However, if I use the .copyFromRecordset to pass the results directly to a
range, the string for each number is written to each cell (eg "4.5 "
instead of 4.5 that happens when using .value)

My knowledge of SQL is limited, and I can't see anything in the recordset
property that would allow for converting the type of the retrieved data
from strings to numerics (really - just trimming the data of whitespace).

my current workarround is to write the data with .copyFromRecordset, read
the data back into an array and write it out immediately.

Is it possible to avoid that last step either with some SQL or something
in
ADO that I've missed?

cheers,
Christophe