The IMEX works as designed, and unfortunately I have to live with the
TypeGuessRows=8, just warn users ahead of time so they watch for it. If the
first 8 rows are numbers for a column IMEX=1 has no affect... nothing can be
done about that. Now, I reproduce the error with the following:
1. Take a clean cell in Excel and change its format to number
2. type in a number
3. change its format to text.

Now i read it into the recordset and when ever i see a E- or E+ I print out
the following:
'rqCol is a column index...
msgbox XLrs.fields.item(rqCol).type
msgbox typename(XLrs.fields.item(rqCol).value)

I see 202 (or adVarWChar) and "String" in the msgboxes.
There is NO special formatting that is applied to the cells. I think I'm
just going to catch it and warn the user of the E- or E+ and let them choose
to continue if it is a legit string.

"Jamie Collins" wrote in message
om...
Let's see if we can clarify things.

The registry values for a default Jet installation a

ImportMixedTypes=Text
TypeGuessRows=8

Not many users will know of their existence, even MS Access users
being the primary users of Jet. The prevailing advice from MS Access
MVPs is to work with the registry settings rather than amend them to
suit. So I think the above default values may be pretty much assumed.

Now let's talk about the values Jet uses at run-time. The run-time
value of ImportMixedTypes defaults to 'Majority Type' i.e. the
registry key is read and validated but not actually used. If IMEX=1 is
explicitly specified, the registry key is used and, as stated above,
it is relatively safe to assume ImportMixedTypes will be 'Text'.

Let's use an example. The Excel column contains the following data:

1
2
Hello
World
9911220193

Assume the registry values for a default Jet installation apply and no
extended properties specified in the connection string (except 'Excel
8.0' <g). All rows are scanned, a mixed types situation is detected,
the registry key is not used and instead Majority Type will prevail,
the majority type is determined as being adDouble, the text values
cannot be coerced as adDouble so will be returned as null, the
resulting recordset will be:

1
2
<<null
<<null
9911220193

This time used IMEX=1 in the connection string. All rows are scanned,
a mixed types situation is detected, the registry key is honored and
'Text' will be used, the field data type will be adVarWChar, all
values present can be coerced, the resulting recordset will be:

1
2
Hello
World
9911220193

Where all values are strings. Looking more closely at that last value
in the Immediate Window:

m_rsMain.MoveLast

? m_rsMain(0).Type = adVarWChar
True

? TypeName(m_rsMain(0).Value)
String

? m_rsMain(0).Value
9911220193

So I'm not getting the same behavior as the OP i.e. no scientific
notation for me, as I would expect unless there was more than 15
significant figures or I'd formatted the cell/column as scientific.
When I do format the cell with Excel's default scientific format, the
resulting string is 9.91E+09 and I'd guess this is the result *if*
Excel was doing some implicit coercion. I can reproduce by formatting
the cell with the custom 0.00000E+000 format. I'm now wondering if the
OP's cell has a similar custom format.

I knew that Jet used both the cell's value and format to determine
data type; I think it is more correct to say Jet always uses a cell's
formatted value (in Excel VBA terms, the Range's Text property rather
than its Value property).

Jamie.

--