[RESOLVED] Exporting DGV To Excel Sheet

[seth.hall]

I looked at some on here and found this:

Public Sub Test()
        Dim xlApp As Global.Excel.Application
        Dim xlWorkBook As Global.Excel.Workbook
        Dim xlWorkSheet As Global.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        'xlApp = Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

Im having a problem with - xlWorkSheet.Cells(i + 1, j + 1)
Any ideas to fix this or another way to export to excel?

[ForumAccount]

My suggestion would be to export to a CSV or TSV. Why? Because then you don't need Excel references, and the user does not need Excel to run your application. Then you just open the CSV/TSV with Excel and it's done.

[seth.hall]

Well the user already has Excel so thats not a problem. This is what Im trying:

Imports Microsoft.Office.Interop.Excel
Module Excel
    Sub export2()

        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        For i = 0 To Form10.DataGridView1.RowCount - 2
            For j = 0 To Form10.DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    Form10.DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()


        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

End Module

Now I click my export button, there is a record in the dgv it gives me the File Saved message and the file is 8kb. I open it up and its blank?

[ForumAccount]

I forgot to mention but doing it as a CSV/TSV is much faster then instantiating Excel. Anyhow, I didn't check but I think this:

xlWorkSheet.Cells(i + 1, j + 1) = Form10.DataGridView1(j, i).Value.ToString()

should be:

xlWorkSheet.Cells(i + 1, j + 1).Value = Form10.DataGridView1(j, i).Value.ToString()

[seth.hall]

Yea, I fixed it awhile earlier but forgot to mark the Thread Resolved. But yea that was the problem. Thanks.