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