When pulling an Excel file as a Dataset in VB.NET, how can you change the sheet name as a variable?
I am writing a program where I will have to connect to an Excel Spreadsheet with an OLEDB connection string. I am able to connect and the name of the spreadsheet is a variable that changes depending on what file path you choose to load into a text box. But, my question is, how would you be able to change the sheet name as a user? I wanted to use a drop down box for the user to choose, but I'm not sure how to query the sheet names. Here is the code that I have for the Excel function. See below.
Function ReadExcelsheet(ByVal Path As String, Optional ByVal Sheet As String = "Sheet1") As DataSet
Dim ds As New DataSet
Dim da As New OleDbDataAdapter
Dim conn_string As OleDbConnection
Dim excel_cmd As New OleDbCommand
Dim dt As New DataTable
'Make sure the text box has a string to find an Excel spreadsheet
If TextBox1.Text = "" Then
MsgBox("No spreadsheets have been loaded yet.")
Exit Function
Else
'This is where the connection string is used
conn_string = New OleDbConnection( _
"Provider= Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & TextBox1.Text & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")
'The command connecting to a database
excel_cmd.Connection = conn_string
'The command string's command test
excel_cmd.CommandText = "SELECT * FROM [" & Sheet & "$]"
'da = New OleDbDataAdapter("SELECT * FROM [" & Sheet & "$]", conn_string)
'Since the OleDbCommand stores the connection string and the command text, the line below
'is the same as the line above.
da.SelectCommand = excel_cmd
'Open the connection string
Try
conn_string.Open()
Catch ex As Exception
MsgBox("Unable to open the connection!", MsgBoxStyle.Critical, "Error!")
Exit Function
End Try
If conn_string.State = ConnectionState.Open Then
MsgBox("The connection string has connected to the source.")
Else
MsgBox("Unable to connect")
End If
'The data adapter fills the dataset with the table information from the database
da.Fill(ds)
da.Fill(dt)
End If
ReadExcelsheet = ds
DataGridView1.DataSource = dt
UpdateSQLTable(ds)
End Function
Here is a little something that might help you along your way. It gets the file location from a textbox and then fills a combobox of the datatable names. Once you have the names you can then utilize your function.
Private Sub GetTableNames()
Dim MyCommand As OleDbDataAdapter
Dim MyConnection As OleDbConnection
' MyCommand = New System.Data.OleDb.OleDbDataAdapter(strConnect, MyConnection)
MyConnection.Open()
Dim schemaTable As DataTable = MyConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim counter As Integer
For counter = 0 To schemaTable.Rows.Count - 1
Dim rd As DataRow = schemaTable.Rows(counter)
If rd("TABLE_TYPE").ToString = "TABLE" Then
ComboBox1.Items.Add(rd("TABLE_NAME").ToString)
End If
Next
MyConnection.Close()
MyConnection.Dispose()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Private Sub GetTableNames()
Dim MyCommand As OleDbDataAdapter
Dim MyConnection As OleDbConnection
Try
MyConnection = New OleDbConnection( _
"provider=Microsoft.Jet.OL
"data source=" & TextBox1.Text & "; " & _
"Extended Properties=Excel 8.0;")
' MyCommand = New System.Data.OleDb.OleDbDat
MyConnection.Open()
Dim schemaTable As DataTable = MyConnection.GetOleDbSchem
New Object() {Nothing, Nothing, Nothing, "TABLE"})
Dim counter As Integer
For counter = 0 To schemaTable.Rows.Count - 1
Dim rd As DataRow = schemaTable.Rows(counter)
If rd("TABLE_TYPE").ToString = "TABLE" Then
ComboBox1.Items.Add(rd("TA
End If
Next
MyConnection.Close()
MyConnection.Dispose()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub