none
creating table from xlsx file RRS feed

  • Question

  • Hi,

    I create table using

    MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;  Data Source='D:\My VS2010 projects\Starter\Formulario_ITBM.xlsx';  Extended Properties=Excel 12.0;")

    and it works fine. I decided to make a sub with the following parameters - the form, where the DataGridView is, the xlsx file name and the sheet name.

    Call Create_table(Me, Me.DataGridView1, "D:\My VS2010 projects\Starter\Formulario_ITBM.xlsx", "Libro_de_Compras")
    Public Sub Create_table(ByRef Forma As System.Windows.Forms.Form, ByVal DatGri As DataGridView, ByVal Source_file As String, ByVal Source_sheet As String)
            Try
                Dim MyConnection As System.Data.OleDb.OleDbConnection
                Dim DtSet As System.Data.DataSet
                Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
    
    MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;  Data Source=Source_file ;  Extended Properties=Excel 12.0;")
               
    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Source_sheet$]", MyConnection)
                
                MyCommand.TableMappings.Add("Table", "TestTable")
                DtSet = New System.Data.DataSet
                MyCommand.Fill(DtSet)
                DatGri.DataSource = DtSet.Tables(0)
                MyConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Sub
    And i receive error on
    MyCommand.Fill(DtSet) - could not find source sheet
    
    The call is 
    Create_table(MeMe.DataGridView1, "D:\My VS2010 projects\Starter\Formulario_ITBM.xlsx""Libro_de_Compras")
    

    Thursday, August 11, 2016 3:19 PM

Answers

  • Hi dinamis,

    use the code below worked for me. same as your concept of the code.

    Private Sub openFileDialog1_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
            Dim filePath As String = OpenFileDialog1.FileName
            Dim extension As String = Path.GetExtension(filePath)
            Dim header As String = If(rbHeaderYes.Checked, "YES", "NO")
            Dim conStr As String, sheetName As String
    
            conStr = String.Empty
            Select Case extension
    
                Case ".xls"
                    'Excel 97-03
                    conStr = String.Format(Excel03ConString, filePath, header)
                    Exit Select
    
                Case ".xlsx"
                    'Excel 07
                    conStr = String.Format(Excel07ConString, filePath, header)
                    Exit Select
            End Select
    
            'Get the name of the First Sheet.
            Using con As New OleDbConnection(conStr)
                Using cmd As New OleDbCommand()
                    cmd.Connection = con
                    con.Open()
                    Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                    sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
                    con.Close()
                End Using
            End Using
    
            'Read Data from the First Sheet.
            Using con As New OleDbConnection(conStr)
                Using cmd As New OleDbCommand()
                    Using oda As New OleDbDataAdapter()
                        Dim dt As New DataTable()
                        cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]"
                        cmd.Connection = con
                        con.Open()
                        oda.SelectCommand = cmd
                        oda.Fill(dt)
                        con.Close()
    
                        'Populate DataGridView.
                        dataGridView1.DataSource = dt
                    End Using
                End Using
            End Using
        End Sub

    The original code is taken from the site below.

    Code

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, August 12, 2016 3:05 AM
    Moderator