Question Getting Excel to close gracefully?

[SubTestEgr]

Hello, everyone. I have just joined your forum hoping to find an answer to a problem I have been pulling my hair out over.

I am trying for the first time to automate Excel 2007 using Visual Basic.NET. I have VS 2008 Professional and am running on a system with Windows XP 64-bit.

Here is the code as far as I've figured it out:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.IO
Public Class WindowsExcelTestForm
    Private xlApp As Excel.Application = Nothing
    Private xlWbs As Excel.Workbooks = Nothing
    Private xlWb As Excel.Workbook = Nothing
    Private xlWs As Excel.Worksheet = Nothing
    Private xlRng As Excel.Range = Nothing

    Private Sub MFRCOMO(ByVal o As Object)
        Try
            Marshal.FinalReleaseComObject(o)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly)
        Finally
            o = Nothing
        End Try
    End Sub
    Private Sub ExitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitButton.Click
        Try
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            MFRCOMO(xlRng)
            MFRCOMO(xlWs)
            MFRCOMO(xlWb)
            MFRCOMO(xlWbs)
            MFRCOMO(xlApp)
            Me.Close()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly)
        End Try
    End Sub

    Private Sub OpenButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenButton.Click
        If File.Exists("C:\TEST\Windows Excel Test 2.xlsx") Then File.Delete("C:\Test\Windows Excel Test 2.xlsx")
        xlApp = CType(New Excel.Application, Excel.Application)
        xlApp.Visible = True
        xlWbs = CType(xlApp.Workbooks, Excel.Workbooks)
        xlWb = CType(xlWbs.Add, Excel.Workbook)
        xlWs = CType(xlWb.Sheets(Index:=1), Excel.Worksheet)
        xlRng = CType(xlWs.Range("A1"), Excel.Range)
        xlRng.Value = "Test"
        xlWb.SaveAs(Filename:="C:\TEST\Windows Excel Test 2.xlsx") 'This is the line that causes the difference
        xlWb.Close(SaveChanges:=False)
        xlWbs.Close()
        xlApp.Quit()

    End Sub
End Class

My problem is that when the code finishes Excel hangs in resident memory for about 20 seconds, then I get a window stating "Microsoft Office Excel has encountered a problem and needs to close." asking me if I want to send or not send a report.

I have tested this code starting with simply opening then closing the application on up through adding the Range Value "Test" and saving it. I have established that the problem occurs due to the presense of the line containing "SaveAs", but I cannot find a problem with the syntax.

Can someone please help? Thanks very much!!

 

[newguy]

This is what I use for my word application. I would call it right after you .Quit. No need to call GC.Collect or GC.WaitForPendingFinalizers(). Also don't set the objects as Nothing prior to this sub - it does that for you, but if you do it will skip the ReleaseComObject portion.

Public Shared Sub ReleaseObject(ByVal o As Object)
    Dim i As Integer
    If Not o Is Nothing Then
      Try
        i = System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        While i > 0
          i = System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        End While
      Catch
      Finally
        o = Nothing
      End Try
    End If
End Sub

 

[SubTestEgr]

Thanks very much! I'll give that a try! Though doesn't the command Marshal.FinalReleaseCOMObject() method implicitly loop the process like your loop?

Thanks!

 

[newguy]

Well, what I do know is what works, and if your having issues then maybe is does not work that well. I have played enough to know that I have not had any issues with lingering processes with mine, so I'm good.

 

[JohnH]

Though doesn't the command Marshal.FinalReleaseCOMObject() method implicitly loop the process like your loop?

That is what it says: Marshal.FinalReleaseComObject Method (System.Runtime.InteropServices)

The FinalReleaseComObject method releases the managed reference to a COM object. Calling this method is equivalent to calling the ReleaseComObject method in a loop until it returns 0.

 

[SubTestEgr]

Newguy,

Thanks for the technique. I gave it a try, but it didn't change my outcome. But nothing tried, nothing gained! Thanks, anyway!

 

[SubTestEgr]

The problem has been solved! It was the presense of the Microsoft Office Live Add-in that was causing the problem!

Thanks to everyone!

Cheers!