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
