Excel reading everything as null.

[Tommyhat]

When i run my code below, it will do nothing because of the line: "If RS.Fields(0) <> Null Then". But if i take that if statement out, the msgbox will print out the data from the excel file. So i dont understand why it says its null one time but not another. i need to check for nulls for when it reaches the end of collumns or rows.

Dim filepath As String
Dim r As Integer, c As Integer
filepath = txtfile.Text
r = 0
c = 0
Dim CONN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CONN = New ADODB.Connection
Set RS = New ADODB.Recordset
CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & filepath & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""
RS.Open "SELECT * FROM [Sheet1$]", CONN
RS.MoveFirst
lstTable.Col = 0
lstTable.Row = 0
Do Until RS.EOF
lstTable.Col = c
lstTable.Row = r
If (c = c + 1) > lstTable.Cols Then
c = 0
r = r + 1
Else
c = c + 1
End If
If RS.Fields(0) <> Null Then
lstTable.Text = RS.Fields(0)
MsgBox RS.Fields(1)
Else
lstTable.Text = "Robot"
End If
RS.MoveNext
Loop

 

[ClulessChris]

It's a bit of a cheat but you could try:

"If len(RS.Fields(0)) <> 0 Then".

Everybody body is somebodys Nutter.

 

[Tommyhat]

Also, it will only read one row and then end itself. it wont move on to the next row. Im new with the excel reading thing. So im still trying to learn what the code does.

 

[Tommyhat]

ClulessChris, i did something along the same lines, but i checked if it was <> "" and that seems to work well, but it still wont move to the next collumn...

 

[ClulessChris]

Tommyhat,
I notice your refering to the collumns numericaly, I've always refered to their Alphabetical reference i.e. A1, B1, C1

Everybody body is somebodys Nutter.

 

[ClulessChris]

For further example this is 'ripped' straight out of a project that works just fine:

Set objWB = objXL.Workbooks.Add
With objWS
        .Range("B1:D1").Merge
        .Range("B1:D1") = "Visitor Details"
        .Columns("A").ColumnWidth = 23
        .Columns("A").RowHeight = 25
        .Range("A2") = "Name"
        .Range("F1:H1").Merge
        .Range("F1:H1") = "Contact Details"
End With

Everybody body is somebodys Nutter.

 

[bjd4jc]

The reason you won't move to the next row is your If statement

        If (c = c + 1) > lstTable.Cols Then
            c = 0
            r = r + 1
        Else
            c = c + 1
        End If

(c = c + 1) will always evaluate as False so you're really you're asking:

If False > lstTables.Cols Then

The only time you're incrementing the row is if the above statement is true... it will always be false so you will never switch rows...

 

[Tommyhat]

How Can i tell when the rs.fields has reached the end?
This is my revised code:

Do Until RS.EOF
n = 0
Do
lstTable.Cols = lstTable.Cols + 1
lstTable.Col = n
If RS.Fields <> "" Then
lstTable.Text = RS.Fields
End If
n = n + 1
If RS.Fields = Empty Then
Exit Do
End If
Loop
RS.MoveNext
lstTable.Rows = lstTable.Rows + 1
lstTable.Row = lstTable.Rows - 1
Loop

it'll get 2 or 3 rows then complain about "item cannot be found in collection corrosponding to requested name or ordinal"

 

[Tommyhat]

Solved it myself!

for future generations, isnull(rs.fields) to check for null and rs.fields.count for the amount of collumns in the excel sheet

 

[dbsquared]

you could also do a
While Not rs.EOF
code
wend

To go where no programmer has gone before.

 

[Tommyhat]

i did a not EOF, but thats end of file, not end of Row.

 

[dbsquared]

you could simplify alittle


Do Until RS.EOF
n = 0
Do Until RS.Fields = Empty
lstTable.Cols = lstTable.Cols + 1
lstTable.Col = n
If RS.Fields <> "" Then
lstTable.Text = RS.Fields
End If
n = n + 1
Loop
RS.MoveNext
lstTable.Rows = lstTable.Rows + 1
lstTable.Row = lstTable.Rows - 1
Loop


To go where no programmer has gone before.