Error exporting to excel

[johmolan]

I am trying to export a datagridview ti excel, but i get an error I do not quite understand.

I have made two click-events using two buttons looking like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim cnn As SqlConnection

        Dim sql As String

        cnn = New SqlConnection(Form1.DS2)
        cnn.Open()
        sql = "SELECT * FROM Ordre"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click


        Dim xlApp As 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 Excel.ApplicationClass
        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

Button 1 fills the datagridview and in works, but when I get try button two I get an invalid index error pn the line highlighted in red.

Can anyone have an idea why?

[jwelsh]

I am trying to export a datagridview ti excel, but i get an error I do not quite understand.

I have made two click-events using two buttons looking like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
        Dim cnn As SqlConnection

        Dim sql As String

        cnn = New SqlConnection(Form1.DS2)
        cnn.Open()
        sql = "SELECT * FROM Ordre"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click


        Dim xlApp As 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 Excel.ApplicationClass
        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

Button 1 fills the datagridview and in works, but when I get try button two I get an invalid index error pn the line highlighted in red.

Can anyone have an idea why?

I'm not familiar with the Excel API, but it might be the capitalization (Sheet1 instead of sheet1) on the line where you have an error, ie:

...
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
...

If that doesn't work I would try to use a zero base index instead of a string name, ie:

...
xlWorkSheet = xlWorkBook.Sheets(0)
...

Or just look up the documentation for this class and see how you are supposed to reference a sheet.

[bklynman01]

I think it might be because you're not giving it a file to open. I built an application that uses excel, and this is how I opened the file...

Dim xlFile As New Excel.Application
    Dim xlSheet As New Excel.Worksheet
    xlFile.Workbooks.Open(Me.strFilename)
    xlSheet = xlFile.Workbooks(1).Sheets("Sheet1")

The big difference is where you assign the workbook. You use the misValue, which has no value (that i can see).

Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

Hope this helps!