[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

When pulling an Excel file as a Dataset in VB.NET, how can you change the sheet name as a variable?

Posted on 2006-06-29
1
Medium Priority
?
254 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 6

Accepted Solution

by:
carmodyk earned 2000 total points
ID: 17019583
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

        Try

            MyConnection = New OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & TextBox1.Text & "; " & _
                  "Extended Properties=Excel 8.0;")

            ' 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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question