<

Killing EXCEL objects from memory in VB.NET

Published on
10,029 Points
3,429 Views
1 Endorsement
Last Modified:
Approved
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the kill process portion with a msgbox stating that excel was detected and it should be closed before proceeding as a precaution statement.

I've seen many examples and discussions on how to properly exit and dispose of excel objects. Believe me, I tried them all. Some examples worked once, while others had no effect. I needed something to consistently dispose of the excel.exe trail in memory.

Since the ultimate effect of killing the process is not desirable, I did create a IsExcelOpen() function, which is basically the same function of the CloseAllResidentExcels function except the prc.kill() process is eliminated and instead displays a warning message to the end user to please save and close all currently open spreadsheets before continuing. I was fully prepared for the hiss and moaning to ensue shortly after deploying the update — but surprisingly everyone had no issue with it.  

Many of the examples for closing an excel spreadsheet probably do work in the simple most case scenarios. But now I'm starting to wonder if the datagridview1 object has to be closed as well before quitting the excel application for these scenarios to work. I haven't had to much to time to find out why it's not closing properly. Everyone probably has a different version of Microsoft Office, different operating systems, and different framework versions that might have a factor in the hit and miss cases of what works and what doesn't.

Another possibility might be the way excel gets initialized in the code. Are we using duplicate references? Is the com interop enabled and initialized correctly. I tried various methods and none were able to get rid of the excel.exe objects in task manager.

 
Imports System 
Imports System.Diagnostics 
 
Private Sub printdocument1_EndPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles printdocument1.EndPrint 
 
    Do While True 
        'kill all resident excels in memory with extreme prejudice 
        If CloseAllResidentExcels() = True Then 
            Exit Do 
        End If 
    Loop 
     
    'reinit your vars here 
    xlApp = CType(CreateObject("Excel.Application"), Excel.Application) 
    wbkToShow = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet) 
    wshToShow = DirectCast(wbkToShow.Worksheets("Sheet1"), Excel.Worksheet) 
End Sub 
 
Private Function CloseAllResidentExcels() As Boolean 
 
    CloseAllResidentExcels = False 
 
    Try 
        'loop this process until there are no valid id's to return 
        Dim Prc As Process 
        Dim localByName As Process() = Process.GetProcessesByName("EXCEL") 
        Prc = Process.GetProcessById(localByName(0).Id) 
        Prc.Kill() 
 
    Catch ex As Exception 
        CloseAllResidentExcels = True 
    End Try 
 
End Function

Open in new window

1
Comment
0 Comments

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month