Reading Excel data with ADO ignores numerics

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

redrobbo666 - 30 May 2005 20:41 GMT

Hi,

I've set up a connection and recordset to access an Excel 2000 spreadsheet
from VB6 (pretty much as described in this article
http://support.microsoft.com/default.aspx?scid=kb;en-us;278973 ).
The recordset is returned OK, and I can read through the rows of data, but
ALL the cells containing numeric values have come through as NULL. I can't
see any options etc that I could change to affeect this. I've tried messing
around with the format of the cells in the spreadsheet, but with no luck.

Anyone ever come across this before?

Many thanks

Reply to this Message

Paul Clement - 31 May 2005 16:04 GMT

¤ Hi,
¤
¤ I've set up a connection and recordset to access an Excel 2000 spreadsheet
¤ from VB6 (pretty much as described in this article
¤ http://support.microsoft.com/default.aspx?scid=kb;en-us;278973 ).
¤ The recordset is returned OK, and I can read through the rows of data, but
¤ ALL the cells containing numeric values have come through as NULL. I can't
¤ see any options etc that I could change to affeect this. I've tried messing
¤ around with the format of the cells in the spreadsheet, but with no luck.
¤
¤ Anyone ever come across this before?

Include the IMEX parameter in your connection string:

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\Book20.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;"""

http://support.microsoft.com/default.aspx?scid=kb;en-us;194124

Paul
~~~~
Microsoft MVP (Visual Basic)

Reply to this Message

VB Forum / Database Access / May 2005

Reading Excel data with ADO ignores numerics