GetOleDbSchemaT able bug (!) not all columns

[Radu]

Hi. I'm trying to read the structure of some Excel 2000 file that the
user is expected to upload. The file must be an excel file (solved)
and it must contain a column named "PIN". That's all. Now about the
column name.

I have the code:

cnExcel.Connect ionString = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=" & strTableName & ";Extended Properties=""Ex cel
8.0;HDR=Yes;IME X=1"";"
cnExcel.Open()

'Retrieve schema information about the excel file (i.e the name of the
first sheet in the worksheet).
schemaTable = cnExcel.GetOleD bSchemaTable(Ol eDbSchemaGuid.T ables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})
strSheetName = schemaTable.Row s(0).Item("Tabl e_Name").ToStri ng

'Retrieve the collection of columns in this excel file...
schemaTable = cnExcel.GetOleD bSchemaTable(Ol eDbSchemaGuid.C olumns, New
Object() {Nothing, Nothing, strSheetName, Nothing})

'Check if we have at least the "PIN" column:
If schemaTable.Sel ect("COLUMN_NAM E='PIN'").GetUp perBound(0) < 0 Then
.....

I have tried with multiple files. Sometimes it
- returns schemaTable.Row s.Count=0 (no columns !!!)
- returns all columns, and I can see them nicely in the immediate
window
- returns only some columns - say the file has 5 columns, but I can
only see 4 - schemaTable.Row s.Count = 4 and ?schemaTable.Ro ws(4)!
COLUMN_NAME.ToS tring tells me that this index does not exist

I have tried moving the PIN column back and forth in the excel file,
and to my astonishment... . sometimes it works.

Example:

Col1 PIN Col2 Status
1 1 63 AAA
2 1 27 AAA
3 1 82 AAA
3 1 40 AAA
4 1 52 AAA
1 1 5 AAA
2 1 11 AAA
3 1 11 AAA
3 1 17 AAA

gives
?schemaTable.Ro ws(0)!COLUMN_NA ME.ToString
"Col1"
?schemaTable.Ro ws(1)!COLUMN_NA ME.ToString
"Col2"
?schemaTable.Ro ws(2)!COLUMN_NA ME.ToString
"PIN"
?schemaTable.Ro ws(3)!COLUMN_NA ME.ToString
Run-time exception thrown : System.IndexOut OfRangeExceptio n - There is
no row at position 3.
(No Status ???)

Col1 Col2 Status PIN
1 653 AAA 1
2 27 AAA 1
3 892 AAA 1
3 40 AAA 1
4 52 AAA 1
1 5 AAA 1
2 151 AAA 1
3 11 AAA 1
3 1472 AAA 1


?schemaTable.Ro ws(0)!COLUMN_NA ME.ToString
"Col1"
?schemaTable.Ro ws(1)!COLUMN_NA ME.ToString
"Col2"
?schemaTable.Ro ws(2)!COLUMN_NA ME.ToString
Run-time exception thrown : System.IndexOut OfRangeExceptio n - There is
no row at position 2.
(No PIN ??? No Status ??? Now two columns dropped ???)

This one
Col1 SS_Col2 Col3 PIN SS_Col4
1 5 AAA 1 2
2 6 AAA 1 3
3 7 AAA 1 4
3 8 AAA 1 5
4 9 AAA 1 6
1 10 AAA 1 7
2 11 AAA 1 8
3 12 AAA 1 9
3 13 AAA 1 10

gives

?schemaTable.Ro ws(0)!COLUMN_NA ME.ToString
"Col1"
?schemaTable.Ro ws(1)!COLUMN_NA ME.ToString
"SS_Col2"
?schemaTable.Ro ws(2)!COLUMN_NA ME.ToString
Run-time exception thrown : System.IndexOut OfRangeExceptio n - There is
no row at position 2.

.... and so on. I have tried many combinations. This situation doesn't
have neither rhyme nor reason. Can somebody hep me, please ?

Thanks a lot.
Alex