Misread cells from excel

[Tommyhat]

When i read into vb from excel it takes some cells as null when they're clearly not. There's NO pattern at all some are numbers some a headers, they're all over the place. Anyone know what might cause this?

 

[cbsm]

Is it possible you are not reading the correct cells ?
(I know, sounds stupid, but it happen to me before, inverting rows and columns)
In any case, if you would share your code, it could help finding what's hapening.

 

[Tommyhat]

Heh, no thats the first thing i check. I'm a firm believer in Ockam's Razor

i found the problem is that excel auto formats collumns and if most of the cells below are dates or times, it formats the headers as dates and times when it reads them. since "tech id" isn't a valid date, it nulls it.

Is there some way to avoid the auto formatting or change the format? i tried rs.field.type = 202 but it says i can't change that while it's open. Frankly i dont see how you could change such a thing UNLESS it's open.

 

[SkipVought]

Isn't tech id a field heading? Surely its not data in a date column.

Post your code segment where this is happening.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Tommyhat]

Well there's some headers in the sam collum as dates. Example:
supervisor
[date]
[date]
[date]
supervisor2
[date]
[date]
(etc...)

This is the format im given in. because we get reports from all over i have to be prepared for everything.

here's some of my code

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=no;imex=1"""

RS.Open "SELECT * FROM [" & sheetname & "]", CONN
RS.MoveFirst
...
For n = 0 To RS.Fields.Count - 1
lstTable.Cols = lstTable.Cols + 1
Next
Do Until RS.EOF
n = 0
l = l + 1
For n = 0 To RS.Fields.Count - 1
lstTable.Col = n
If IsNull(RS.Fields) Then
lstTable.Text = ""
Else
lstTable.Text = RS.Fields
End If
Next
RS.MoveNext
lstTable.Rows = lstTable.Rows + 1
lstTable.Row = lstTable.Rows - 1
per = (l / ts) * 100
Call percentbar(per)
Loop

 

[Tommyhat]

my new problem is when imex=1 is added it doesnt format at all leaving the dates in incomprehencable decimal form.

it seems to be one or the other here :\

is there a way to set the format for each cell as they're read?

 

[SkipVought]

Then you don't have a table in the sense that each column is ONE data type.

I'd test each

   With RS
      .MoveFirst
      r = 1
      c = 1
      Do While Not .EOF
         If IsNumeric(RS("supervisor")) Then
            MsgBox "number" 'houston, we have a date!
         Else
            MsgBox "text"
         End If
         .MoveNext
      Loop
   End With

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[Tommyhat]

Testing is easy enough, it's setting the format for each cell thats the problem because rs.feilds.type = 202 (varchar) cannot be done "while open".

therein lies the challenge

 

[ArtieChoke]

Unfortunately, the excel ODBC driver is not very smart. It looks ahead a max of 16 columns (I think the default is 8) to decide what type the column should have. Then it makes everything in that column the same type. If you have dates for 10 columns, then chars, then more dates and it decided on a date field, then anything not matching that gets nulls.

And the expectation is there won't be mixed info, like you've shown above, in the column.

There is a way to format an entire column and have excel use that format, but in your case, even that won't work properly because of the multiple sections.

How many spreadsheets do you have to read per day, week, etc? We ran into the same problem and I used the clipboard, which returns all text to solve it. It means you'll have to paste each section from the spreadsheet into the program one at a time and know what the fields are.

Another possibility is to use named ranges for each section and have the program open each section.

Have you tried using the Excel object instead of ADO? It might be a bit smarter (but then again, maybe not... )

"I think we're all Bozos on this bus!" - Firesign Theatre