analys 0 Newbie Poster

I try to export my tables to excel in multiple sheets but i'm having an error state that object variable or with block variable not set.
Hope anyone can help me. Here's my code.

Private Sub exportexcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportexcel.Click
        Dim del_cmd As New OleDbCommand
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim chartRange As Excel.Range

        conn.Open()
        Dim location As String = ""
        Try
            'Save dialogue box declaration
            SaveFileDialog1.Filter = "Excel files (*.xls)|*.xls"
            SaveFileDialog1.RestoreDirectory = True
            SaveFileDialog1.Title = "Save Documents"
            SaveFileDialog1.FileName = ""
            If SaveFileDialog1.ShowDialog() = DialogResult.OK Then

                xlApp = CreateObject("Excel.Application")
                xlWorkBook = xlApp.Workbooks.Add

                ' first worksheet
                If xlApp.Application.Sheets.Count() < 1 Then
                    cmd.CommandText = "Select  * From students"
                    cmd.Connection = connection
                    da.SelectCommand = cmd
                    da.Fill(dt)
                    dgrid.DataSource = dt
                    location = SaveFileDialog1.FileName
                    xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)
                    xlWorkSheet = xlWorkBook.Sheets("Students")
                    For Each col As DataGridViewColumn In DataGridView1.Columns
                        xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText
                        For Each rowa As DataGridViewRow In DataGridView1.Rows
                            xlWorkSheet.Cells(rowa.Index + 2, col.Index + 1) = rowa.Cells(col.Index).Value
                        Next
                    Next
                    chartRange = xlWorkSheet.Range("A1", "Z1")
                    xlWorkSheet.SaveAs(location)
                Else
                    xlWorkSheet = xlApp.Worksheets(1)
                End If

                ' second
                If xlApp.Application.Sheets.Count() < 2 Then
                    cmd.CommandText = "Select  * From results"
                    cmd.Connection = conn
                    da.SelectCommand = cmd
                    da.Fill(dt)
                    dgrid.DataSource = dt
                    location = SaveFileDialog1.FileName
                    xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)
                    xlWorkSheet = xlWorkBook.Sheets("Results")
                    For Each col As DataGridViewColumn In DataGridView1.Columns
                        xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText
                        For Each rowa As DataGridViewRow In DataGridView1.Rows
                            xlWorkSheet.Cells(rowa.Index + 2, col.Index + 1) = rowa.Cells(col.Index).Value
                        Next
                    Next
                    chartRange = xlWorkSheet.Range("A1", "Z1")
                    xlWorkSheet.SaveAs(location)
                Else
                    xlWorkSheet = xlApp.Worksheets(2)
                End If

                ' third
                If xlApp.Application.Sheets.Count() < 3 Then
                    cmd.CommandText = "Select  * From payment"
                    cmd.Connection = conn
                    da.SelectCommand = cmd
                    da.Fill(schomasys)
                    dgrid.DataSource = schomasys
                    location = SaveFileDialog1.FileName
                    xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)
                    xlWorkSheet = xlWorkBook.Sheets("Payment")
                    For Each col As DataGridViewColumn In DataGridView1.Columns
                        xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText
                        For Each rowa As DataGridViewRow In DataGridView1.Rows
                            xlWorkSheet.Cells(rowa.Index + 2, col.Index + 1) = rowa.Cells(col.Index).Value
                        Next
                    Next
                    chartRange = xlWorkSheet.Range("A1", "Z1")
                    xlWorkSheet.SaveAs(location)
                Else
                    xlWorkSheet = xlApp.Worksheets(3)
                End If

                xlWorkBook.Close()
                xlApp.Quit()
                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                MsgBox("Excel Export Successful", MsgBoxStyle.Information)
                dt.Clear()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            End
        End Try
        del_cmd.CommandText = "Delete From students"
        del_cmd.CommandText = "Delete From results"
        del_cmd.CommandText = "Delete From payment"
        del_cmd.Connection = connection
        del_cmd.ExecuteNonQuery()
        conn.Close()
    End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.