Kill Excel on Error

Question

I am hoping you can help me here, in the past you all have been great. I have tried every variation of the kill script for killing excel from vb.net, to no avail.

First I can't post explicit code on here because it is my company's proprietary software, but I can tell you a few things. Also there are over 28,000 lines of code.

1. I am not using

   Imports Excel = Microsoft.Office.Interop.Excel
   

   due to the fact that we have to accommodate different variations of clients software. I am creating the new excel as an object as such

   Dim XLObj As Object = CreateObject("Excel.Application")
   

   I have seen this used on several other sites but the kill function they are using is when you save and then close it, which I'm not doing.

2. The error message I am getting says that "Com object that has been separated from its underlying RCW cannot be used". I'm not sure where this com object is because I have released the sheets, workbook and then the application.

3. Oh and I don't want to use the excel.kill() because if a client already has the excel open I don't want to kill it without saving it. I only want to kill the newly generated excel process that doesn't have a window open associated with it.

My questions are as follows

1. I need to be able to close the Excel application when/if the open fails. So say I am click a link and it opens the dialog box to select an Excel template to load but either the data from the database is corrupt or the sql statement is broken. The program throws and error and then Excel should close in the Task Manager. Unfortunately it doesn't close hence the problem.

2. is there a way to close only the newly created process id? I have tried to use the directions here but it doesn't work either. When I do that it gives me a different error "Value cannot be null Parameter name: o". The line that is throwing the error is on (from the link)

   Marshal.FinalReleaseComObject(tempVar)
   

   I only tried this because we are using the With on the XLObj. The With is in reference to the workbook itself so shouldn't it be released when I close the workbook? And being as I'm causing it to error on purpose at the moment it shouldn't reach the With statement anyway.

3. Is there a way to tell which com object is not closing?

Things I have tried:

1. This releaseObject that I found on the internet. (don't ask me where I've been through about 75 pages)

   Private Sub releaseObject(ByRef obj As Object)
   Try
       System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
       If obj Is Nothing Then
   
       Else
           obj = Nothing
       End If
   
   
   Catch ex As Exception
       If obj Is Nothing Then
   
       Else
           obj = Nothing
       End If
   
   Finally
       GC.Collect()
       GC.WaitForPendingFinalizers()
   End Try
   End Sub
   

This is used in conjunction with this function (which was pieced together from the many sites I have been on)

    Public Sub CloseExcel(ByRef WorkBook As Object, ByRef Application As Object)
    Dim xLSheet As Object = WorkBook.Sheets

    For Each xLSheet In WorkBook.Sheets
        If xLSheet IsNot Nothing Then
            releaseObject(xLSheet)
        End If
        If xLSheet IsNot Nothing Then
            Kill(xLSheet)
        End If
    Next

    If WorkBook IsNot Nothing Then
        WorkBook.Close(False)
    End If

    If WorkBook IsNot Nothing Then
        Kill(WorkBook)
    End If
    releaseObject(WorkBook)

    If Application IsNot Nothing Then
        Application.Quit()
    End If
    If Application IsNot Nothing Then
        Kill(Application)
    End If


    releaseObject(Application)

    GC.Collect()
    GC.WaitForPendingFinalizers()

    Application.Quit()
End Sub

and because it is also referenced the Kill function

    Public Sub Kill(ByRef obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
    Catch ex As Exception
        MessageBox.Show("moduleExcel.Kill  " &  ex.Message)
    Finally
        obj = Nothing


    End Try
End Sub

any help would be greatly appreciated.

Answer 1

Ok so for those of you having this exact same issue. I do have a solution for you. Yes the above code does work but for a few minor adjustments.

1. you need to take out all the code in the CloseExcel sub and place it EXACTLY where you want it to close. So if you want it to close if the program errors out, put after the catch statement. You cannot call a Sub and pass in your objects and expect it to kill the process.

2. you need a few bits above the opening of the new Excel process. and they are as follows.

   'declare process for excel
   Dim XLProc As Process
   
   'loads the financials excel bookmarks
   'this will be where you declare your new excel opbject
   Dim XLObj As Object = CreateObject("Excel.Application") 
   
   
   'get window handle
   Dim xlHWND As Integer = XLObj.hwnd
   
   Dim ProcIDXL As Integer = 0
   'get the process ID
   GetWindowThreadProcessId(xlHWND, ProcIDXL)
   XLProc = Process.GetProcessById(ProcIDXL)
   

and of course you will need the GetWindowThreadProcessId which I got from the link I included in the original question. I am posting it here so you don't have to search for it.

    <System.Runtime.InteropServices.DllImport("user32.dll", SetLastError:=True)> _
Private Function GetWindowThreadProcessId(ByVal hWnd As IntPtr, ByRef lpdwProcessId As Integer) As Integer
End Function

This code will only close the single process you have it associated with, it will not close other open Excel files. Our clients sometimes will have multiple files open and we don't want to close them without telling them. This KILLS the Excel process that was created at run time when the system Errors out.