Software: VB.NET 2.0 and Excel 2003

I have an Excel sheet with 140 records. 135 of the records have alphanumeric
data in fields.item(2) (see code below). 5 of the 140 cells in this coloumn
is however numeric.

When I read the records, the alphanumeric fileds is read OK, but the 5
records with numeric data is read as *Null*!!! Why????

How can I get my VB.NET program read the numeric value 5 as 5 or "5" and NOT
as Null????

cnnExcel.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; _
data source='C:\MySeet';Extended Properties=Excel 8.0;"
cnnExcel.Open()

Dim RS As New ADODB.Recordset()
sSQL = "SELECT * FROM [Sheet1$];"
RS.Open(sSQL, cnnExcel, ADODB.CursorTypeEnum.adOpenStatic, _
ADODB.LockTypeEnum.adLockOptimistic, 1)

RS.MoveFirst()
For I = 1 To RS.RecordCount
Debug.Print("" & RS.Fields.Item(2).Value)
RS.MoveNext()
Next

Kjell Weding, Norway
kjell@weding.no

Hi,

I would start using ado.net you might have better results

Here is an example with a datareader to loop though the records

Imports System.Data.OleDb

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\kwlog.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim conn As New OleDbConnection(strConn)
Dim cmd As New OleDbCommand("Select * From [Sheet1$]", conn)
Dim dr As OleDbDataReader

conn.Open()
dr = cmd.ExecuteReader
Do While dr.Read
Trace.WriteLine(dr("KW").ToString)
Loop
dr.Close()
conn.Close()
End Sub
End Class

You can also load the records into a datatable the data is available in memory

Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\kwlog.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
Dim conn As New OleDbConnection(strConn)
Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", conn)
Dim dt As New DataTable

da.Fill(dt)

For Each dr As DataRow In dt.Rows
Trace.WriteLine(dr("kw").ToString)
Next


Ken
----------------------------

[quoted text, click to view]