Copy Current DataGridView Row Into Excel-CodePudding

I’m trying to copy the current row from the DataGridView into Excel but can’t seem to find the correct syntax for it to work. Hopefully someone can help me out with the below code.

Thanks in advance.

    Public Sub CopyRowToExcel()

        Dim xlApp As Excel.Application
        Dim WB As Excel.Workbook
        Dim WS As Excel.Worksheet
        Dim LR As Integer
        Dim RowNum As Integer

        xlApp = GetObject(, Constants.ExcelApp)
        WB = xlApp.Workbooks("Products")
        WS = WB.Sheets("DataSheet")

        LR = WS.Range("A" & WS.Rows.Count).End(Excel.XlDirection.xlUp).Row   1
        RowNum = DataGridView1.CurrentRow.Index

        WS.Range("A" & LR & ":L" & LR).Value = DataGridView1.Rows(RowNum).Cells(1 To 12).Value

    End Sub

CodePudding user response：

You do not say if the grid has a data source. If you do not have a data source for the grid, then I suggest you use one. It will make things easier in numerous ways, and this is an example.

Assuming the grid has a DataTable as a DataSource, then you should be able to write a whole row to the Excel file similar to what you appear to be asking with…

DataGridView1.Rows(RowNum).Cells(1 To 12).Value

When you set a ranges value in Excel like…

WS.Range("A" & LR & ":L" & LR).Value =

… will work as long as the right-hand side of the “=” is an array of the same dimensions. In this case where the range is a “single” row, then the array needs to be a ONE (1) dimensional array. Then, if the range is larger than the array, it will fill the missing array part(s) with something like… “#N/A” … And if the range is less than the size of the array, then it will ignore the extra values in the array.

If the range dimension is not the same as the array dimension… you will get an error.

Therefore if the grid used a DataTable and you wanted to append the selected row to the end of an existing worksheet, then you should be able to do something like…

Dim excelRange As String = "A" & LR & ":L" & LR
Dim drv As DataRowView = DataGridView1.SelectedRows(0).DataBoundItem
xlWorksheet.Range(excelRange, Type.Missing).Value2 = drv.Row.ItemArray.ToArray()

In my small tests, this worked as described above.