Error on reading number not string from Excel

[martinm]

My App happily reads strings from a spreadsheet. These values may now be numbers (i.e. have no aplha chars in them).

The individual cells have to be formatted as text, otherwise I get a runtime error 'Numeric field overflow' on accessing this value. I thought that the data was retrieved as a variant, so shouldn't make any different what type it is?

Even

Nz(rsImport(1), "")

Causes the error!

I'm connecting thus, and retrieving with 'select * from'

Set ltb = db.CreateTableDef(strLink_name)
ltb.Connect = xConnection_string
ltb.SourceTableName = xTable



Any one explain this?

Ta.

 

[SkipVought]

Hi,

In Excel, changing the formatting changes ABSOLUTELY NOTHING, except what you see. The underlying value remains UNCHANGED!

So, if you have a column that contains BOTH alpha-numeric (TEXT) and numeric (NUMBERS), after you format the column as TEXT, you STILL have BOTH alpha-numeric (TEXT) and numeric (NUMBERS). This condition alone, will cause problems when you attempt to query.

The solution is to fix the data in Excel by prepending an APOSTROPHY (Excel's identifier for a LABEL or TEXT) which will convert the number to a string.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[martinm]

Thanks, trouble is I can't see why there is a problem, as everything is returned as a variant, so is not typed?

 

[SkipVought]

Not so! The Query Manager, analyzes the data in the columm and ASSUMES a type, usually based on the first 8 rows.

If it assumes TEXT, your numeric values will be 'majorly honked' to put it is 'teknikl' terms. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

"Numeric Field Overflow" error message occurs when you query a table that is linked to Excel spreadsheet

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[martinm]

Ah, interesting. So they're variants, but of a single type. Bl**dy Microsoft.

Thanks, I'll let the user know they need to use the ' .