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

Recommended Answers

All 7 Replies

Have you stepped through the code to find out which sheet/row/cell/line of code the error is being thrown on?

Something that will give us a clue as to where the exception is being thrown?

 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")

I believe this statement is your problem: xlWorkSheet = xlWorkBook.Sheets("Students")
Unless your Normal template has a Students worksheet in it, xlWorkSheet will be unassigned (Nothing).

Hi TnTinMN,
I have did as your suggestion, but the error is still there

Hi TnTinMN,
I have did as your suggestion, but the error is still there

I was not aware that I made any code suggestions other than point out a pattern that looked like it had faulty logic.

Did you apply your fix to the similar logic used for the "payment" and "results" worksheets?

Please show your new logic.

You can reduce the amount of code/debugging you have with something like the following:

Dim lstSheets As New List(Of String)
lstSheets.Add("Students")
lstSheets.Add("Result")
lstSheets.Add("Payment")

Private Sub exportexcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exportexcel.Click
    If xlApp.Sheets.Count <= 3 Then
        For i = 0 To xlApp.Sheets.Count - 1
            'This will do the same as your IF statements.
            SaveInfo(lstSheets(i))
            DeleteCurrent(i)
        Next
    Else
        MsgBox("More sheets exist that are not handled.")
    End If
End Sub

Private Sub SaveInfo(ByVal sCurrent As String)
    Try
        cmd.CommandText = "Select  * From " & sCurrent.ToLower
        cmd.Connection = con
        da.SelectCommand = cmd
        da.Fill(dt)
        dgrid.DataSource = dt
        location = SaveFileDialog1.FileName
        xlWorkSheet = CType(xlWorkBook.Worksheets.Add(), Excel.Worksheet)
        xlWorkSheet = xlWorkBook.Sheets(sCurrent)
        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)
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

Private Sub DeleteCurrent(ByVal sCurrent as String)
    Try
        del_command.CommandText = "Delete From " & sCurrent
        del_command.ExecuteNonQuery()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub    

Hi Begginnerdev,
I'm having an error COMException was unhandled: Exception from HRESULT: 0x800A03EC
44fa74d307509446324db71df0315ebf

Hi,

Looks like...
"xlApp" is an Application Object.. and "Sheets" is not immediate properlty...
You have to check for..
xlWorkBook.Sheets.Count

Change the workbook, variable name accordingly.. and also, make sure, it is declared in the same procedure or declared Module Level... and it has to be opened, before checking sheets.count...

Regards
Veena

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.