Reading data from an Excel spreadsheet with unknown sheet names

Reading data from an Excel spreadsheet is pretty easy, given you know the name of the sheet in the file you are reading data from. What if you don’t know the name of the sheet? A current project I’m working on needs to give the user the ability to select the sheet name to read data from.  Here’s a small snippet of code that reads the names of the sheets and populates a DropDownList control.

Dim cnExcel As OleDbConnection = New OleDbConnection( _
    “Provider=Microsoft.Jet.OLEDB.4.0;” &_
    “Data Source=” & strWorkbookFileName & “;” & _
    “Extended Properties=”"Excel 8.0;HDR=Yes”"”)
cnExcel.Open()
Dim dtSheets As DataTable = cnExcel.GetOleDbSchemaTable( _
    OleDbSchemaGuid.Tables, _
    New Object() {Nothing, Nothing, Nothing, “Table”})
Dim i As Integer
Dim strWorksheetName As String
For i = 0 To dtSheets.Rows.Count - 1
    strWorksheetName = dtSheets.Rows(i)(2)
    dropWorksheetName.Items.Add(New ListItem(strWorksheetName, strWorksheetName))
Next
cnExcel.Close()