Hi Cindy,
As you know we are in "apparently unpredictable" territory here
I have been experimenting recently but haven't collated all the info yet.
I'll post somewhere when I have. However, applying text format /alone/ is
definitely not enough It is perhaps significant that when you convert to
text using the method you specified, Excel does actually convert values such
as "5" to "'5", and what I suspect is happening is that as long as you have
at least one cel in the first 25 that Excel presents in its formula bar
starting with a single quote, OLEDB will see the column as being mixed/text.
If you have counterexamples, please let me know. Of late, I haven't ben
focussing on sheets with fewer than 25 rows, for example
Peter Jamieson
Personally I think it would be ever so much easier if only
a. the OLEDB provider was able to present Word with /whatever the user had
put in their Excel cells/. Unfortunately, daftness in "the industry" are so
obsessed with strong typing that they probably regard people who mix text
and numbers as some kind of ignorant deviant
b.
The OLEDB provider definitely looks at the first 25 data rows (i.e. not the
header row, as far as I know) to try to determine the column format. If it
thinks the data is all numeric, it will process non-numeric values
incorrectly (fortunately, or perhaps not, it does not actually reject them).
Even the 25-row factor is a bit of a moveable feast.
The trouble is that if you don't take account of that 25-row factor, the
results of other experiments may have limited value.
As far as I can see, wh
So even there, there are at least 3 experimental possibilities as far as
mixed text/numeric colmns are concerned:
a. there are 25 data (i.e. non-header) rows or fewer.
b. there are more than 25 rows fewer than 25 rows an 25 rows
The question then boils down to "what is it about an Excel cell that makes
the provider think it is numeric?" (and of course other things that are
defined outside a single cell may come into play here).
It definitely isn't /just/ the Format/Cells format. If you select the column
and change all the cell formats to text, Excel obligingly shifts all the
numbers to the left, accepts that they are numbers (i.e. doesn't display any
"warning triangles"), but the OLEDB provider still decides that the column
is numeric.
However, nor is it some weirdo thing like "if the cell is text, but there's
a number, and Excel has flagged a warning/error, and the user hasn't told
Excel to ignore the error"
My own recent experiments suggest that the OLEDB provider does not really
care what the cell format is: if you change all the cells in the column to
"Text" format, Excel obligingly shifts all the numbers over to the left and
does not display one of its warning traiangles in the upper left corner of
the cells, suggesting that it now sees them as being text values. But the
provider still treats them as numbers, and if it thinks the first 25 data
rows (i.e. forgetting about the heading row) are numbers, it treats the
remainder of the column as numeric.
However, if you had (say) the number '5' in a cell and you change it to '5,
Excel displays the 5, displays its warning triangle,
Fortunately it doesn't go the whole hog and complain when there's text in
the cell.
seems to think they are numbers.
In contrast, if I change one of the numbers to be text using e.g. '5 instead
of 5, Excel flags a warning
