Click here to Skip to main content
15,897,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
When I export grid data to excel ,Open an excel on messagebox saying Yes
Otherwise I have saved it on some path.

Case when excel object is not getting removed from task manager-
1)Export to excel and Say yes on messagebox saying("Do you want to open an excel")
2)Close an excel and then it is not getting removed from task manager.

I have used following code-

VB
Private Function Export_To_Excel(ByVal I_StrFileName As String, ByVal I_strSheetName As String, ByVal I_strSheetNumber As Integer) As Integer
       Dim dicExcel As New Dictionary(Of Integer, Integer)
       Dim MyExcelProcessId As Integer = 0
       Dim AllProcesses() As Process
       Dim blnFileExist As Boolean
       Dim i As Long
       Dim grdExportGrid As New DataGridView
       Dim dtExport As New DataTable
       Dim iRow As Integer
       Dim ColUpdatedIndex As Integer
       Dim ColAmountIndex As Integer

         Try
          ''CheckForExistingExcellProcesses
           AllProcesses = Process.GetProcessesByName("excel")
           For Each ExcelProcess As Process In AllProcesses
               dicExcel.Add(ExcelProcess.Id, 1)
           Next
           ''CheckForExistingExcellProcesses : End

           oExcel = CreateObject("Excel.Application")
           If Dir(I_StrFileName) <> "" Then
               oExcel.Workbooks.Open(I_StrFileName)
               blnFileExist = True
           Else
               oBook = oExcel.Workbooks.Add
               oSheet = oBook.Worksheets(I_strSheetNumber)
               oSheet.Name = I_strSheetName
           End If

           ''GetTheExcelProcessIdThatUsedByThisInstance
           AllProcesses = Process.GetProcessesByName("excel")
           ' Search For the Right Excel
           For Each ExcelProcess As Process In AllProcesses
               If Not dicExcel Is Nothing Then
                   If dicExcel.ContainsKey(ExcelProcess.Id) = False Then
                       ' This is the Process ID that used by your instance
                       MyExcelProcessId = ExcelProcess.Id
                       Exit For
                   End If
               End If
           Next
           ''GetTheExcelProcessIdThatUsedByThisInstance : End

           With oSheet
               i = 1
               For iCol = 0 To grdAccDetails.Columns.Count - 1
                   If grdAccDetails.Columns(iCol).Visible Then
                       .Cells(1, i) = grdAccDetails.Columns(iCol).HeaderText.Replace(vbCrLf, "")
                       .Cells(1, i).EntireRow.Font.Bold = True
                       .Cells(1, i).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White)
                       .Cells(1, i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
                       .Cells(1, i).Borders.Value = True
                       If grdAccDetails.Columns(iCol).HeaderText = "Updated At" Then
                           ColUpdatedIndex = i
                       End If
                       If grdAccDetails.Columns(iCol).HeaderText = "Amount" Then
                           ColAmountIndex = i
                       End If

                       i = i + 1
                   End If
               Next

               For iRow = 0 To grdAccDetails.Rows.Count - 1

                   i = 1
                   For iCol = 0 To grdAccDetails.Columns.Count - 1
                       If grdAccDetails.Columns(iCol).Visible Then
                           If grdAccDetails.Columns(iCol).HeaderText = "Updated At" Then
                               .Cells(iRow + 2, i).numberformat = "dd/mm/yyyy HH:mm:ss"
                           End If
                           If grdAccDetails.Rows(iRow).Cells("Operation").Value = "DELETE" Then
                               .Cells(iRow + 2, i).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(255, 128, 128))
                           End If
                           If grdAccDetails.Columns(iCol).HeaderText = "Amount" Then
                               .Cells(iRow + 2, i).numberformat = "###,##0.00"
                           End If
                           .Cells(iRow + 2, i).Borders.Value = True
                           .Cells(iRow + 2, i) = grdAccDetails.Rows(iRow).Cells(iCol).Value
                           i = i + 1
                       End If
                       .Cells(iRow + 1, i).EntireColumn.AutoFit()
                   Next iCol
               Next iRow
           End With
oBook.SaveAs(I_StrFileName)

           Dim strMSG As String = "Data exported to " & I_StrFileName & "." & vbCrLf & "Do you want to open it?"
           If MessageBox.Show(strMSG, appname, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

               oExcel.visible = True
           Else
               ''KillExcelProcessThatUsedByThisInstance
               AllProcesses = Process.GetProcessesByName("excel")
               For Each ExcelProcess As Process In AllProcesses
                   If ExcelProcess.Id = MyExcelProcessId Then
                       ExcelProcess.Kill()
                       Exit For
                   End If
               Next
               AllProcesses = Nothing
               ''KillExcelProcessThatUsedByThisInstance : End
           End If
           Export_To_Excel = 1
       Catch ex As Exception
           Export_To_Excel = 0
           oSheet = Nothing
           oBook = Nothing
           oExcel.Quit()
           oExcel = Nothing
           GC.Collect()
            End Try
   End Function
Posted
Updated 26-May-15 23:42pm
v4
Comments
Michael_Davies 26-May-15 11:47am    
Noticed that myself, however I do not quit Excel but there are non-visible running copies left with each run. Added the following and it cleared up for me:

While System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel) <> 0
Application.DoEvents()
End While

Excel = Nothing
@trupti_29 27-May-15 1:10am    
It is not working for above code.
Object still remains in task manager untill unless I close the application.
Michael_Davies 27-May-15 3:09am    
Have you tried putting the code before you kill the excel processes, the excel process is linked to your opening application and may not close because it is.

Did a quick test:

Imports Microsoft.Win32
Imports System.Management
Imports System.Threading
Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Dim Excel As Object

Excel = CreateObject("Excel.application")

Excel.quit()

While (System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel) <> 0)
Application.DoEvents()
End While

Excel = Nothing
End Sub
End Class

When I comment out the release loop an excel process is left in the background visible in taskmanager and had to manually kill it, uncommented the release loop ran again no excel process left running.

For your code then:

Dim strMSG As String = "Data exported to " & I_StrFileName & "." & vbCrLf & "Do you want to open it?"
If MessageBox.Show(strMSG, appname, MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
oExcel.visible = True
Else
oExcel.Quit()
While (System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel) <> 0)
Application.DoEvents()
End While
oExcel = Nothing
End If
@trupti_29 27-May-15 5:36am    
Yes,I have tried this solution but it is not working for my code.
Actually problem is when i used to open the excel programmatically and then user close it before closing an application ,it remains in task manager.
Michael_Davies 27-May-15 8:53am    
Create a new project with the simple code above and walk it through.

If you do not make Excel visible then the interop loop will close the background excel even without Excel.Quit().

If the User closes Excel you are right a background copy remains, this is because it is linked to your application that has not relinquished the link and Excel does not inform the user, quite right as your application might then try to communicate with Excel and it will fail as Excel has been closed.

So the real question is how to detect user closed Excel and act appropriately...

I tested the theory by adding a button to action the release loop:

Dim Excel As Object
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Excel = CreateObject("Excel.application")
Excel.Visible = True
End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
While (System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel) <> 0)
Application.DoEvents()
End While

Excel = Nothing
End Sub

Thinking...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900