Opening and Closing Excel File Properly

[a1jit]

Hi Guys,

I wrote a piece of code here to open and close an excel file..
Everything works fine..But im not sure why when i go to the task manager, the instance of excel is still there running > means that the excel file is not closed properly..Do you guys have any idea what is the problem?

Thankx in advance

oXL = new Excel.Application();
		
oWB = oXL.Workbooks.Open("C:\\Content.xls",
Type.Missing,false,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing,
Type.Missing,Type.Missing,true,
Type.Missing,Type.Missing,Type.Missing);
				
oWB.Close(false,Type.Missing,Type.Missing);

oXL.Workbooks.Close();

oXL.Quit();

 

[VagabondSW]

I had the exact same problem. There is something about Excel that makes it hard to clean up. We know that every Excel object, worksheets, workbooks, the application itself, all will leave that EXCEL.EXE in memory if not cleaned up. However, there must be something else as well.

I took two approaches to solving this problem. I was under extreme time constraints, so all of these steps may not be necessary, but it did solve the problem and the problem remains solved to this day.

First, I implemented the following Destructor and Dispose method in my ClassXL class.

~ClassXL()
{
  if (xlapp != null)
  Dispose(false);
}

public void Dispose()
{
  Dispose(true);
  GC.SuppressFinalize(this);
}

public virtual void Dispose(bool disposing)
{
  if (!this.disposed)
  {
    for (int i = 0; i < alXlObjects.Count; i++)
    {
      int referenceCount = 0;
      do
      {
        referenceCount = System.Runtime.InteropServices.Marshal.ReleaseComObject(alXlObjects[i]);
        alXlObjects[i] = null;
      }
      while (referenceCount > 0);
    }

    this.xlapp = null;
    if (disposing)
    {
      GC.SuppressFinalize(this);
    }
  }
  disposed = true;
}

As you can see, the Dispose method iterates through an ArrayList containing all the XL objects that I created during initialization. For example:

if (this.xlapp == null)
{
  this.xlapp = new excel9.Application();
  alXlObjects.Add(this.xlapp);
}

Finally, I also included a call to GC.Collect from the consumer class using the ClassXL.

classXL.Dispose();
GC.Collect();

Like I said, not all these steps may be necessary, but it fixed the problem you described back in November 2004 and the problem remains fixed to this day.

 

[mark007]

I use the following:

 Private Sub DisposeObject(ByVal obj As Object)
        Dim count As Integer
        Try
            If obj Is Nothing Then Exit Try
            count = Marshal.ReleaseComObject(obj)
            While count > 0
                count = Marshal.ReleaseComObject(obj)
            End While
        Catch ex As Exception
        Finally
            obj = Nothing
        End Try
    End Sub

Which I found somewhere when first reading up on Excel automation through .Net. You basically need to call it on all objects when youre done with them.