Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copyFromRecordset - vs - getRows
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CopyFromRecordset | Excel Programming | |||
Can't assign to Array .GetRows in Excel 97 | Excel Programming | |||
Can't assign to Array .GetRows in Excel 97 | Excel Programming | |||
ADO slow recordset getrows | Excel Programming | |||
Copyfromrecordset Bug ? | Excel Programming |