Get Excel data when there is not a 'SHEET1'

[DavidT_macktool]

anybody know how to read in Excel data when the Excel Spreadsheet has had the worksheet renamed.

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

How would you update the above code to get the data if [Sheet1$] has been renamed to something else more meaningful, like "Employee Listing"? The key is that you will not know what the name is of the worksheet(s) every time.

Thanks for reading,

 

[jmcilhinney]

The sheet name is taking the place of the table name. You can't get data from a database without knowing the name of the table you want to get it from, so it is quite logical and reasonable that you can't get data from an XLS file without knowing the name of the worksheet. Assuming that you have Excel installed you could use Automation to get the sheet name.

 

[DavidT_macktool]

Load Worksheet names into listbox

    Private Sub GetWorksheets()
        GetWSheets = False
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            filename = OpenFileDialog1.FileName
            If Not (filename Is Nothing) Then
                ExcelConnection = New System.Data.OleDb.OleDbConnection( _
                      "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source=" & filename & " ; " & _
                      "Extended Properties=Excel 8.0;")
                ExcelConnection.Open()
                Dim dt As DataTable
                dt = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
                      New Object() {Nothing, Nothing, Nothing, "TABLE"})
                ListBox1.DisplayMember = "TABLE_NAME"
                ListBox1.ValueMember = "TABLE_NAME"
                ListBox1.DataSource = dt
                ExcelConnection.Close()
                GetWSheets = True
            End If
        End If
    End Sub

    Private Sub GetExcelSpreadsheet()
        If GetWSheets = True Then
            Worksheet = CStr(ListBox1.SelectedValue)
            ExcelConnection = New System.Data.OleDb.OleDbConnection( _
      "provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=" & filename & " ; " & _
      "Extended Properties=Excel 8.0;")
            ExcelCommand = New System.Data.OleDb.OleDbDataAdapter( _
                  "select * from  [" & Worksheet & "]", ExcelConnection)
            DSexcel = New System.Data.DataSet
            ExcelCommand.Fill(DSexcel)
            ExcelConnection.Close()
            DataGrid1.DataSource = DSexcel.Tables(0)
        Else
            ' do nothing
        End If
    End Sub

First part loads worksheet names into a listbox.
Second sub uses the selected worksheet name in the select statement.

Here is a 1 form project that loads selected worksheet into a datagrid.

 

[jmcilhinney]

I saw code like this in another thread and I knew I'd given dodgy advice elsewhere but I couldn't remember where to go back and correct my error. My apologies. Always remember that "you can't" usually really means "I don't know how to".

 

[DavidT_macktool]

No worries, your advice has been rock solid 99% of the time, law of averages finally caught up to you...just kidding. This forum has helped so much over the past year, I have gone from 'green' to somewhat knowing something about some things. Thanks for the help.

 

[pachjo]

OK shhot me down in flames if I am completely wrong here...

This is the exact problem I have at the moment.

My app allows a user to select a spreadsheet and then creates a linked server to it to import the data into my application database.

Now as the excel spreadsheet file becomes a linked server is it not possible to reference the sheet by an ordinal number?

Is it not the same as referencing fields by name or ordinal?

Appreciate this might not be possible as I am struggling to find any links to the solution.

Just seems naff to have to tell the user "oh by the way....make sure the data to import in a sheet called sheet1"

Thnaks

 

[DavidT_macktool]

I'm not shooting you down, but there is no need to have the 'sheet1' required. the code above gets all the sheet names for you, it just dumps them into a listbox. if you don't like the user having to select the worksheet from the listbox, you just have to access all the sheets and get thier information loaded into datasets. load up a different dataset for each sheet and then all programming and references are back to the way you are familiar with (referencing fields by name or ordinal). i bind the dataset to a grid and navigate through and reference items just like any other dataset.

any help?

 

[pachjo]

Mmmm, I will plumb in your code and see what occurs.

I obiviously missed the plot on that one!

 

[pachjo]

This just displays the word "Tables" and not the name of the sheet?

[SIZE=2]oleDbExcelConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbConnection( _
[/SIZE][SIZE=2][COLOR=#a31515]"provider=Microsoft.Jet.OLEDB.4.0; "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"data source="[/COLOR][/SIZE][SIZE=2] & glb_strImportFilePath & [/SIZE][SIZE=2][COLOR=#a31515]" ; "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"Extended Properties=Excel 8.0;"[/COLOR][/SIZE][SIZE=2])
oleDbExcelConnection.Open()
dtSpreadSheet = oleDbExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
[/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2]() {[/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#a31515]"TABLE"[/COLOR][/SIZE][SIZE=2]})
MessageBox.Show(dtSpreadSheet.TableName)
[/SIZE]

 

[pachjo]

Aha!

[SIZE=2]oleDbExcelConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.OleDb.OleDbConnection( _
[/SIZE][SIZE=2][COLOR=#a31515]"provider=Microsoft.Jet.OLEDB.4.0; "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"data source="[/COLOR][/SIZE][SIZE=2] & glb_strImportFilePath & [/SIZE][SIZE=2][COLOR=#a31515]" ; "[/COLOR][/SIZE][SIZE=2] & _
[/SIZE][SIZE=2][COLOR=#a31515]"Extended Properties=Excel 8.0;"[/COLOR][/SIZE][SIZE=2])
oleDbExcelConnection.Open()
dtSpreadSheet = oleDbExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
[/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2]() {[/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#a31515]"TABLE"[/COLOR][/SIZE][SIZE=2]})
MessageBox.Show(dtSpreadSheet.Rows(0).Item([/SIZE][SIZE=2][COLOR=#a31515]"TABLE_NAME"[/COLOR][/SIZE][SIZE=2]).ToString)
[/SIZE]