Hi, I'm trying to export data from excel sheet to my grid, I'm using the foll code, but I get an error at DataGridView1.Rows.Add(), saying rows cannot be added programmatically.

    Private Sub btnexport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexport.Click

        Dim xlsApp As Microsoft.Office.Interop.Excel.Application
        Dim xlsbook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlsSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim m_Rw As Integer
        Dim i As Integer
        Dim j As Integer

        xlsApp = CreateObject("Excel.Application")
        xlsApp.Workbooks.Open("C:\Users\finance and accounts\Desktop\sample.xlsx")
        xlsbook = xlsApp.ActiveWorkbook
        xlsSheet = xlsbook.Sheets(1)

        ' insert column headers...
        'DataGridView1.ColumnCount = xlsSheet.UsedRange.Columns.Count
        'DataGridView1.ColumnHeadersVisible = True
        For i = 0 To xlsSheet.UsedRange.Columns.Count - 1
            DataGridView1.Columns(i).Name = xlsSheet.Cells(1, i + 1).Value.ToString

        ' insert rows and data to its cells....
        Dim var1(xlsSheet.UsedRange.Columns.Count) As String ' array for consisting cell value
        m_Rw = 0
        For i = 2 To xlsSheet.UsedRange.Rows.Count - 1
            DataGridView1.AllowUserToAddRows = True
            For j = 0 To xlsSheet.UsedRange.Columns.Count - 1

                If xlsSheet.Cells(i, j + 1).Value = Nothing Then
                    var1(j) = ""
                    var1(j) = xlsSheet.Cells(i, j + 1).Value.ToString
                End If

                DataGridView1.Item(j, m_Rw).Value = var1(j)

            Next j
            m_Rw = m_Rw + 1
        Next i

        xlsSheet = Nothing  'disconnect from the Worksheet
        xlsbook = Nothing
        xlsApp.Quit()       'Close (and disconnect from) Excel
        xlsApp = Nothing

        Me.Cursor = Cursors.Default

    End Sub
4 Years
Discussion Span
Last Post by M.Waqas Aslam

ther are columns present in the grid... Am I supposed to mention the columns in the above code as well?? If yes, then how?


you can add column values line by line by doing this.

'this will add rows to your grid try this.
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.