Working with Excel file trough ADO.NET
This code works fine for reading cells from known Excel file:
Dim MyCommand As OleDb.OleDbDataAdapter
Dim MyConnection As OleDb.OleDbConnection
Dim strCon As String
strCon = "provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=c:\MyData.xls" & _
"Extended Properties=Excel 8.0;"
MyConnection = New OleDb.OleDbConnection(strCon)
' Select the data from Sheet1 of the workbook.
Dim strSelect As String
strSelect = "select * from [Sheet1$]"
MyCommand = New System.Data.OleDb.OleDbDataAdapter(strSelect, MyConnection)
DS = New System.Data.DataSet
MyCommand.Fill(DS)
MyConnection.Close()
Dim strCell00 As String
strCell00 = CStr(DS.Tables(0).Rows(0).Item(0))
End If
My Q. is what if I don't know worksheet name, which is in this case "Sheet1$". How to get all worksheet names in Excel file?