none
Join two worksheet Excel to Datagridview RRS feed

  • Question

  • I have data from two sheet in excel want combine.First sheet1 then followed by sheet2.

    I use "UNION" to combine that but all data peel off after debug in Datagridview.

    somebody can show me make data in datagridview organized.

    sheet1 figure bellow;

    sheet2 like bellow;

    Result show in datagridview;

      Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    
            Dim _filename As String = "C:\Users\Uset\Documents\Book3.xlsx"
    
            Dim _conn As String
            _conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filename & ";Extended Properties='Excel 12.0;IMEX=1;'"
    
            Dim _connection As OleDbConnection = New OleDbConnection(_conn)
            Dim da As OleDbDataAdapter = New OleDbDataAdapter()
            Dim _command As OleDbCommand = New OleDbCommand()
    
            _command.Connection = _connection
            _command.CommandText = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
            da.SelectCommand = _command
    
            Try
                da.Fill(ds1, "Sheet1" & "Sheet2")
                MessageBox.Show("The import is complete!")
                Me.DataGridView1.DataSource = ds1
                Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"
    
            Catch e1 As Exception
                MessageBox.Show("Import Failed, correct Column name in the sheet!")
            End Try
    
        End Sub

    Thursday, March 21, 2019 2:42 PM

Answers

  • Hi,

    fix the code,Union all

    _command.CommandText = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mipakteh Friday, March 22, 2019 4:43 AM
    Friday, March 22, 2019 1:56 AM

All replies

  • Hi,

    fix the code,Union all

    _command.CommandText = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union all SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by mipakteh Friday, March 22, 2019 4:43 AM
    Friday, March 22, 2019 1:56 AM
  • thank you very much Alex.Work fine
    Friday, March 22, 2019 4:43 AM