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

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Thu, 19 Apr 2007 15:38:54 +0200, "Jan Lorenz" <knarzer77@xxxxxx> wrote:

¤ 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)
.

Quantcast