Multiple datagrids to one excel sheet

Question

I have the below code which will transfer the records from my datagrid to my excel spreadsheet. Currently this code works for one datagrid to an excel sheet. Now I need to improve the below code so that it can work for multiple datagrids. I want help to extend this code so that I can pull the records from 3 data grids to the same excel sheet one below another.

Dim excel As Microsoft.Office.Interop.Excel.Application
Try
excel = New Microsoft.Office.Interop.Excel.Application
excel.Workbooks.Open("C:\Satish\TestExcel\vbexcel.xlsx")
Dim i As Integer, j As Integer
Dim diff As Integer = 1
' if you want column header from dgv elese omit the block 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
For j = 0 To DataGridView1.ColumnCount - 1
excel.Worksheets(1).cells(1, j + 1) = DataGridView1.Columns(j).Name
Next
diff += 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
For i = 0 To DataGridView4.RowCount - 1

If DataGridView4.Rows(i).IsNewRow = False Then
For j = 0 To DataGridView4.ColumnCount - 1

excel.Worksheets(1).cells(i + diff, j + 1) = DataGridView4.Item(j, i).Value
Next
End If
Next
excel.Worksheets(1).select()
excel.ActiveWorkbook().Save()
excel.Workbooks.Close()
excel.Quit()
excel = Nothing
Catch ex As System.Runtime.InteropServices.COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())
End Try

Being a need base programmer I'm struggling to find a solution for more than two days now. — Satish, Aug 01 '14 at 06:15

tezzo · Answer 1 · 2014-08-01T10:08:37.173

0

Try this code:

Dim intExcelRow As Integer = 1

For i As Integer = 1 To 3

    Dim YourDataGridView As DataGridView = Me.Controls("YourDataGridView" & i)

    'header of the first DataGridView only
    'remove If i = 1 if you need to print 3 different headers
    If i = 1 Then

        For intColumn = 0 To YourDataGridView.ColumnCount - 1
            excel.Worksheets(1).cells(intExcelRow, intColumn + 1) = YourDataGridView.Columns(intColumn).Name
        Next intColumn

        intExcelRow = intExcelRow + 1

    End If

    For intRow = 0 To YourDataGridView.RowCount - 1

        If YourDataGridView.Rows(intRow).IsNewRow = False Then

            For intColumn = 0 To YourDataGridView.ColumnCount - 1
                excel.Worksheets(1).cells(intExcelRow, intColumn + 1) = YourDataGridView.Item(intColumn, intRow).Value
            Next intColumn

            intExcelRow = intExcelRow + 1

        End If

    Next intRow

Next i

edited Aug 01 '14 at 10:08

answered Aug 01 '14 at 07:57

tezzo

9,918
1
21
45

Also I'm getting "Object reference " error for line For intRow = 0 To YourDataGridView.RowCount - 1 – Satish Aug 01 '14 at 08:31
this code prints only the header of the first DataGridView: if you want to print 3 different headers you have to remove the if indicated (NOT the code contained). – tezzo Aug 01 '14 at 08:38
verify if Me.Controls("YourDataGridView" & i) returns a valid DataGridView; if not simply change the control name to search. – tezzo Aug 01 '14 at 08:40
Yes I assigned a message box to check, but it returns null value – Satish Aug 01 '14 at 08:52
What are the names of your 3 DataGridView? If you named the 3 DataGridView as DataGridView1, DataGridView2, DataGridView3 simply change code like this: Me.Controls("DataGridView" & i) – tezzo Aug 01 '14 at 08:57
I did change the code first time only to Dim YourDataGridView As DataGridView = Me.Controls("DataGridView" & i) MsgBox(YourDataGridView) But for some reason the messag box is returning empty and then stops with Object Error – Satish Aug 01 '14 at 09:00
I debugged and could see that value for i is getting updated , but for some reason the full value "DATAGRIDVIEW4" is not getting assigned to yourdatagridview – Satish Aug 01 '14 at 09:05
The reason is very simple: i is going from 1 to 3. Do you understand what happened when you call Me.Controls("DataGridView" & i)? What are the names of your 3 DataGridView? Can you change them to DataGridView1, DataGridView2 and DataGridView3? – tezzo Aug 01 '14 at 09:15
I was able t understated that peice of the code where it was trying to create Datagridview1, Datagridview2 and Datagridview3. My datagrids name is also the same DataGridView1, DataGridView2 and DataGridView3. SO really not sure what is happenng – Satish Aug 01 '14 at 09:17
Forget my code. What are the names of the 3 DataGridView placed in your Form? In your code I find only DataGridView1 and DataGridView4; can you renamed them as DataGridView1, DataGridView2 and DataGridView3? – tezzo Aug 01 '14 at 09:28
My datagrids names are DataGridView1, DataGridView2 and DataGridView3. Datagridview4 Im not using it anymore – Satish Aug 01 '14 at 09:41
I cleaned up everything and was able to run it. But now I'm getting a COM error "Exception from HRESULT: 0x800A03EC" – Satish Aug 01 '14 at 10:00
I changed declaration of intExcelRow based on this discussion: http://stackoverflow.com/questions/12714626/exception-from-hresult-0x800a03ec-error; now it is: Dim intExcelRow As Integer = 1 – tezzo Aug 01 '14 at 10:09

Multiple datagrids to one excel sheet

1 Answers1