Link to home
Start Free TrialLog in
Avatar of VBBRett
VBBRett

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of carmodyk
carmodyk
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial