NULL-Values instead of integer-values while reading from excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan Lorenz - 19 Apr 2007 14:38 GMT

Hi,

i read a excel-file via ado. It works fine, when text is in the cells. When
there is a integer-value (f.i. 1234), then I always get NULL. When I write
'1234 in the cell, then it works also.

My code is:

Set CN = New ADODB.Connection
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path
& ";Extended Properties=Excel 8.0; HDR=No;"
CN.CursorLocation = adUseClient
CN.Open
Set RS = New ADODB.Recordset
sql = "SELECT * FROM [" + SheetName + "]"
RS.Open sql, CN, adOpenStatic, adLockOptimistic
a = RS.Fields(0).Value......

Any tip?

best regards
Jan

Reply to this Message

Paul Clement - 20 Apr 2007 15:07 GMT

¤ Hi,
¤
¤ i read a excel-file via ado. It works fine, when text is in the cells. When
¤ there is a integer-value (f.i. 1234), then I always get NULL. When I write
¤ '1234 in the cell, then it works also.
¤
¤ My code is:
¤
¤ Set CN = New ADODB.Connection
¤ CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path
¤ & ";Extended Properties=Excel 8.0; HDR=No;"
¤ CN.CursorLocation = adUseClient
¤ CN.Open
¤ Set RS = New ADODB.Recordset
¤ sql = "SELECT * FROM [" + SheetName + "]"
¤ RS.Open sql, CN, adOpenStatic, adLockOptimistic
¤ a = RS.Fields(0).Value......
¤
¤ Any tip?

This is because you have a mixed mode column (string and numeric data). Try adding the IMEX argument
to your connection string so that the data is imported properly:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""

Paul
~~~~
Microsoft MVP (Visual Basic)

Reply to this Message

Jan Lorenz - 20 Apr 2007 15:13 GMT

thanks a lot. It works.

> ¤ Hi,
> ¤
[quoted text clipped - 30 lines]
> ~~~~
> Microsoft MVP (Visual Basic)

Reply to this Message

VB Forum / Database Access / April 2007

NULL-Values instead of integer-values while reading from excel