Get Excel data when there is not a 'SHEET1'

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
anybody know how to read in Excel data when the Excel Spreadsheet has had the worksheet renamed.
VB.NET:
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,493
Location
Sydney, Australia
Programming Experience
10+
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

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Load Worksheet names into listbox

VB.NET:
    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
VB.NET:
    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.
 

Attachments

  • ExcelLoad.zip
    10.5 KB · Views: 61

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,493
Location
Sydney, Australia
Programming Experience
10+
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". :eek:
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
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

Well-known member
Joined
Dec 12, 2006
Messages
370
Programming Experience
10+
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

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
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

Well-known member
Joined
Dec 12, 2006
Messages
370
Programming Experience
10+
This just displays the word "Tables" and not the name of the sheet?

VB.NET:
[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

Well-known member
Joined
Dec 12, 2006
Messages
370
Programming Experience
10+
Aha!

VB.NET:
[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]

:D
 
Top Bottom