I am bit of a confusion here. I let the user manually close Excel but still a process remains on the background? May you kindly help me resolve this issue. In my own understanding, I tried releasing COM objects as well with it? Also I would like to know what errors have I've been doing with it. I appreciate any help. Thank you so much.
Private Sub releaseComObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Sub BtnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExport.Click
'verfying the datagridview having data or not
If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If
BtnExport.Text = "Please Wait..."
BtnExport.Enabled = False
'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
Next
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow
For j As Integer = 0 To DataGridView1.Columns.Count - 1
dr1(j) = DataGridView1.Rows(i).Cells(j).Value
Next
dset.Tables(0).Rows.Add(dr1)
Next
Dim excel As New Excel.Application
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet
wBook = excel.Workbooks.Add()
wSheet = CType(wBook.ActiveSheet(), Microsoft.Office.Interop.Excel.Worksheet)
Dim dt As System.Data.DataTable = dset.Tables(0)
Dim dc As System.Data.DataColumn
Dim dr As System.Data.DataRow
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(1, colIndex) = dc.ColumnName
Next
For Each dr In dt.Rows
rowIndex = rowIndex + 1
colIndex = 0
For Each dc In dt.Columns
colIndex = colIndex + 1
excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
Next
Next
wSheet.Columns.AutoFit()
excel.Visible = True
BtnExport.Text = " Export"
BtnExport.Enabled = True
releaseComObject(wSheet)
releaseComObject(wBook)
releaseComObject(excel)
End Sub