I am trying to create an application that can read multiple sheets out of an Excel Workbook. Opening the connection and reading the data out of a known sheet is no problem. I am running into a wall when trying to extract the data from an unknown sheet and multiple sheets. I want to be able to read the data from all of the sheets in a workbook. Here is the code i am currently using:

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=" & FileLoadName & "; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet
MyCommand.Fill(DS)

'######################################
'Populate grid on GUI
'######################################
DataGrid1.DataSource = DS.Tables(0).DefaultView

This brings in all of the data on Sheet1, but doesn't display Sheet2 or Sheet3!!! Any help would be appreciated.

I just asked a similar question for importing data from a workbook. Here was the reply I received:

Sub GetWorksheetNames(strFile as string)
Dim xl As New Excel.Application
Dim wkbk As Excel.Workbook
Dim strFile As String
Dim I as Integer
Dim J as Long

Set wkbk = xl.Workbooks.Open(strFile)

For I = 1 to wkbk.Worksheets.Count
  J = 1
  Do While wkbk.Worksheets(I).Range("A" & J).Value <> ""
    J = J + 1
  Loop
  Debug.Print wkbk.Worksheets(I).Name
  Debug.Print "Number of Records: " & J
  ' Your code here to add these sheet names to a Listbox or something
Next I

wkbk.Close
Set wkbk = Nothing
xl.Quit
Set xl = Nothing

End Sub