<

Killing EXCEL objects from memory in VB.NET

Published on
10,266 Points
3,666 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

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
Discover the basics of using Outlook 2016 from office 365.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month