I am using the following code which appears in slightly different versions
in many articles on the web
Dim excelConn As New System.Data.OleDb.OleDbConnection
Dim dtXlsSchema As DataTable
Dim int As Integer
excelConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & strExcelFileName & ";" & "Extended Properties='Excel
8.0;HDR=Yes;IMEX=1'"
excelConn.Open()
dtXlsSchema =
excelConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New
Object() {Nothing, Nothing, Nothing, "TABLE"})
For int = 0 To dtXlsSchema.Rows.Count - 1
Debug.WriteLine(dtXlsSchema.Rows(int).Item("Table_Name").ToString)
Next
The value in dtXlsSchema.Rows.Count is always zero.
If in code I tried to build a DataSet from the excel file, I use a
TableName/WorksheetName with the syntax strWorksheetName & "$;" with or
without square brackets, with or without the dollar sign, I get an error
saying that vs.net, cannot find the object {TableName}.
The Excel file exists and has 5 worksheets. The Open() succeeds without any
error message.
Can anyone tell me what I am doing wrong??
Code after this example:
dtXlsSchema = excelConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns,
New Object() {Nothing, Nothing, strWorksheetName & "$", "TABLE"})
For int = 0 To dtXlsSchema.Columns.Count - 1
Debug.WriteLine(dtXlsSchema.Columns(int).ToString)
Next
does have a value(28)
Garry