locked
On cleanuing up COM object when using Microsoft.Office.Interop.Excel RRS feed

  • Question

  • When using Microsoft.Office.Interop.Excel the COM objects that are created by the code must be released using System.Runtime.InteropServices.Marshal.ReleaseComObject().

    Most of the time it's pretty clear when a new COM object is created such as:

    Excel._Application excelApp = null;
    Excel._Workbook wb = null;
    Excel._Worksheet ws = null;
    Excel.Range newRange = null;
    
    try
    {
    	// four COM objects are created below
    	excelApp = new Excel.Application();
    	wb = excelApp.Workbooks.Add();
    	ws = (Excel.Worksheet)wb.Worksheets.Add();
    	newRange = (Excel.Range)ws.Range["A1","A30"];
    
    	// do these line of cod create new COM object?
    	newRange.Font.Bold = true;
    	newRange.Borders.Color = borderColor;
    }
    finally
    {
    	if (excelApp != null) Marshal.ReleaseComObject(excelApp)
    	if (wb != null) Marshal.ReleaseComObject(wb)
    	if (ws != null) Marshal.ReleaseComObject(ws)
    	if (newRange != null) Marshal.ReleaseComObject(newRange)
    }
    

    In the above code I create four COM objects in the first part that need to be released when I'm finished with them. But it's not clear if the other two lines of code create a new COM object or not.  If they do then my code needs to look more like this:

    Excel._Application excelApp = null;
    Excel._Workbook wb = null;
    Excel._Worksheet ws = null;
    Excel.Range newRange = null;
    Excel.Font fnt = null;
    Excel.Borders bds = null;
    
    try
    {
    	// four COM objects are created below
    	excelApp = new Excel.Application();
    	wb = excelApp.Workbooks.Add();
    	ws = (Excel.Worksheet)wb.Worksheets.Add();
    	newRange = (Excel.Range)ws.Range["A1","A30"];
    
    	// do these line of cod create new COM object?
    	fnt = newRange.Font
    	fnt.Bold = true;
    
    	bds = new newRange.Borders;
    	bds.Color = borderColor;
    }
    finally
    {
    	if (excelApp != null) Marshal.ReleaseComObject(excelApp)
    	if (wb != null) Marshal.ReleaseComObject(wb)
    	if (ws != null) Marshal.ReleaseComObject(ws)
    	if (newRange != null) Marshal.ReleaseComObject(newRange)
    	if (fnt != null) Marshal.ReleaseComObject(fnt)
    	if (bds != null) Marshal.ReleaseComObject(bds)
    }
    How can I tell if getting a property creates a new COM object or not?

    Friday, April 3, 2015 7:33 PM

All replies

  • Hello,

    >>But it's not clear if the other two lines of code create a new COM object or not.

    It created a COM object, you could check its stack by Visual Studio as:

    >>How can I tell if getting a property creates a new COM object or not?

    You could check the Visual Studio local stack, or check the returned value of Marshal.ReleaseComObject(object o), the value decrements the reference count of the specified Runtime Callable Wrapper (RCW) associated with the specified COM object. For details, you could check this link:

    http://stackoverflow.com/questions/4591681/rcw-reference-counting-when-using-com-interop-in-c-sharp

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Wednesday, April 15, 2015 8:05 AM
    • Unmarked as answer by JHJ at ADM Wednesday, April 29, 2015 10:45 PM
    Monday, April 6, 2015 7:39 AM
  • Thank you for your replay but I do understand that the font object is a COM object.  What I'm trying to figure out is if a NEW object is created each time I access the font member of a Range object and if I need to call Marshal.ReleaseComObject on the font object after using it.

    Most member object of an object are a single instance and each time you access the member you simply get the pointer to that instance. For example:

    using(DataTable dt = new DataTable("Some Table Name")) 
    {
    	PropertyCollection ep1 = dt.ExtendedProperties;
    	PropertyCollection ep2 = dt.ExtendedProperties;
    	if (Object.ReferenceEquals(ep1,ep2)) Console.WriteLine("They are the same object");
    	else Console.WriteLine("They are different objects");
    }

    The output will be: They are the same object

    On the other hand this:

    Excel._Application excelApp = new Excel.Application();
    Excel._Workbook wb = excelApp.Workbooks.Add();
    Excel._Worksheet ws = (Excel.Worksheet)wb.Worksheets.Add();
    Excel.Range newRange = (Excel.Range)ws.Range["A1","A30"];
    			
    // do these lines of code create new COM object?
    Excel.Font ef1 = newRange.Font;
    Excel.Font ef2 = newRange.Font;
    if (Object.ReferenceEquals(ef1,ef2)) Consloe.WriteLine("They are the same object");
    else Consloe.WriteLine("They are different objects");

    The output will be: They are different objects

    It looks like each time I access the font member I get a new object.  I suspect that is not the case and what I am getting is two pointers to the same object and the reference counter is incremented by one.

    So really the question is what happens to the font member object of the Range object when the range object is released.  I assume the font member will be released along with the Range object ever if the font object has a reference count greater then 0.

    If I am correct in my assumption then I can access the font member object as much as I need to without worrying about releasing it.

    I have been reading a lot about working with COM and the need to use Marshal.ReleaseComObject and there does seem to be a lot of disagreement and even confusion on the mater about when and if COM objects need to be explicitly released.

    Tuesday, April 28, 2015 7:33 PM