Selecting the first sheet in an Excel workbook

[cphoenixc]

When accessing Excel data I use;

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)

All help files etc. I've read till now use this to select all data from the sheet with name "Sheet1", but I do not know the name of the sheet, so is there a way to select the first sheet no matter it's name?

[CyberHawke]

This works:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtSchema As DataTable
        Dim drSchema As DataRow
        Dim strForTextBox As String
        Dim strTableName As String
        Dim strSelect As String = "SELECT * FROM [{0}]"
        Dim da As New OleDbDataAdapter
        Dim ds As New DataTable
        Dim fDlg As New OpenFileDialog
        fDlg.Filter = "Excel Spreadsheets (*.xls)|*.xls"
        fDlg.ShowDialog()
        If fDlg.FileName.Length > 0 Then
            Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                  "Data Source=" & fDlg.FileName & _
                                                  ";Extended Properties=Excel 8.0;")
 
            cn.Open()
            dtSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
 
            'DataGrid1.SetDataBinding(dtSchema, "")
            drSchema = dtSchema.Rows(0)
            strTableName = drSchema("TABLE_NAME")
            Dim cmd As New OleDbCommand(String.Format(strSelect, strTableName))
            cmd.Connection = cn
            da.SelectCommand = cmd
            da.Fill(ds)
            'For Each drSchema In dtSchema.Rows
            '    strForTextBox = strForTextBox & "Table name = " & drSchema("TABLE_NAME") & vbCrLf
            'Next
            DataGrid1.DataSource = ds
 
            'TextBox1.Text = strForTextBox
 
            cn.Close()
            cn.Dispose()
        End If
    End Sub

I just love a challenge