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.
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
'reinit your vars here
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
wbkToShow = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
wshToShow = DirectCast(wbkToShow.Worksheets("Sheet1"), Excel.Worksheet)
Private Function CloseAllResidentExcels() As Boolean
CloseAllResidentExcels = False
'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)
Catch ex As Exception
CloseAllResidentExcels = True