Release Excel Object

Introduction

Nearly every developer has encountered one day what I have encountered when dealing with a COM object and the pain in releasing it from memory.

In the following example, I will describe my experience about how I solved an issue with the Excel object, and the way I followed to kill those hanging objects, where I tried many known methods and none worked.

The key of my successful experience is, I got to know every object that is created explicitly or implicitly when calling one of the methods for some Excel object, and especially the sequence while creating/killing those created objects. There's nothing new in my example except the way I'm handling the life cycle of the object. This article is completely based on my own personal views and experiences.

Start by creating an Excel file that consists of five sheets, add some rows with some values in some cells to each sheet. (Make sure that the second and the sixth cells have values.) Make the sixth cell as hyperlink by right clicking and assigning some address to it, and save the file under the c:\myExcel.xls folder.

Create a new ASP.NET web project in Visual Studio .NET. Look under the Project dropdown menu, select Add Reference, then click on the COM tab. Select the Microsoft Excel Object Library, add it to your references, and click on OK to close the dialog.

Open the code behind the file of WebForm1 and enter the following code:

Sub Page_Load createAndKillExcelObject()
End Sub
 
Private Sub createAndKillExcelObject()
 
    'I used late binding to resolve the issue from 
    'the difference in version 
    ' between my machine and the server
    'This method is SLOW, but it ensure the 
    'appropriate release of all Excel's 
    ' objects the excel file in subject here, 
    'consist of 1 book and many sheets.
    'each sheet consist of different number of rows
    'in this example:
    'I want to get to the second cell (cell(2) ) 
    'in each row in each sheet to get 
    ' to its Text property
    ' +
    ' to the sixth cell ( cell(6) ) in each row 
    'in each sheet to extract the 
    ' Hyperlink value.
    
    'Based on the same logic followed here, 
    '(creating and killing objects in the 
    ' SAME SEQUENCE) you can extend this logic to fit your need.
    
    Dim someExcelFile As String = "c:\myExcel.xls"
    
    'note the sequence in creating Excel objects.
    'the followed sequence (creating and killing) 
    'is the key to release Excel 
    'object at the end.
     
    Dim xl As Object
    xl = CreateObject("Excel.Application")
     
    'creating and pointing 'myWorkbooks' variable 
    'to the Excel's workbook object.
    'Excel's workbooks has a 'Close()' property, 
    'that I will call it later for 
    'myWorkbooks before killing it.
    'NOTE: if you have more than 1 workbook, you need to kill them all 
    '       individually before the workbooks object to be released. 
    Dim myWorkbooks As Object = xl.Workbooks
    xl.Visible = False 
    myWorkbooks.Open(someExcelFile)  
    
    'we need to access each sheet individually, 
    'therefore, I create 'mySheets' 
    'object that contains the individuals sheets, 
    'which for, we'll create an 
    'individual variables, that point to each sheet 
    'contained in 'mySheets' object.
    
    'creating and pointing 'mySheets' variable to 
    'the Excel's worksheets object.
    ' 'mySheets' object contains more 5 sheets.
    'in order to kill 'mySheets' object, we need 
    'to kill every sheet individually, 
    ' before 'mySheets' to be released
    Dim mySheets As Object
    mySheets = xl.Workbooks(1).Worksheets
     
    'NOTE:
    'calling the 'Count' property of Wroksheets 
    'will create automatically
    'the individual sheets contained in mySheets object, 
    'which will not be visible
    'to you, but they will exist in memory. 
    'And without killing those sheets
    'individually, we would not be able to kill mySheets object.
    'By assigning those individual sheets to 
    'a variables, we can keep track of them
    'and kill them when needed.
    
    'mySheetArray contains those individuals sheets in mySheets object.
    
    Dim mySheetCount As Int16 = mySheets.count
    
    'mySheetArray that contains the individuals sheets in mySheets object
    Dim mySheetArray(mySheetCount) As Object
    Dim i, j, k As Int16
    
    For i = 1 To mySheetCount
        mySheetArray(i) = mySheets(i)
        
        'create a variable that point to the 
        'range object in each sheet contained in 
        'mySheets object.
        'We'll release this range later as part 
        'of our process to release Excel object
        Dim myRanges As Object = mySheets(i).UsedRange
         
        'create a variable that point to the 
        ''Columns' object in myRanges object.
        'We'll release this columns object later 
        'as part of our process to release 
        'Excel object
        Dim myColumns As Object = myRanges.Columns
        Dim myColumnsCount As Int16 = myColumns.Count
        
        'create a variable that point to 
        ''myRows' object in myRanges object.
        'We'll release this Rows object later 
        'as part of our process to release 
        'Excel object
        Dim myRows As Object = myRanges.Rows
        Dim myRowsCount As Int16 = myRows.Count
        
        'myRowArray will contains all rows 
        'contained in 'myRows' object
        Dim myRowArray(myRowsCount) As Object
        
        For j = 1 To myRowsCount
        
            'assign each row in 'myRows' object to a variable
            myRowArray(j) = myRows(j)
            
            'extract the sixth cell's value in this row
            Dim myColumns_6 As Object = myRowArray(j).Cells(6)
            
            'myHyperlink variable point to the Hyperlink object
            'specific to each column contained in myColumns object
            Dim myHyperlink As Object = myColumns_6.Hyperlinks
            
            If myHyperlink.Count > 0 Then 
            
                'myHyperlink_1 point to the first 
                'Hyperlink object contained
                'in myColumns_6 object, contained in myColumns object
                Dim myHyperlink_1 As Object = myColumns_6.Hyperlinks(1)
                
                'calling the 'Address' property of 
                'myHyperlink1 return a String
                '(no hidden object in memory is created 
                'when calling this property)
                Dim adress As String = myHyperlink_1.Address
                
                'we need to access the second cell in this sheet,
                'so, let's start by creating the 'myCells' 
                'object, that contains all cells.
                Dim myCells As Object = myRowArray(j).Cells
                
                'myCells_2 point to cell with the index 
                '2 contained in myCells object
                Dim myCells_2 As Object = myCells(2)
                
                Dim cellText As String
                cellText = myCells_2.Text
                
                 'start releasing all object by the 
                'same sequence that were created.
                '(last to first)
                
                ReleaseComObject(myCells_2)
                ReleaseComObject(myCells)
                ReleaseComObject(myHyperlink_1)
            End If
            ReleaseComObject(myHyperlink)
            ReleaseComObject(myColumns_6)
            ReleaseComObject(myRowArray(j))
        Next
        myRowArray = Nothing
        ReleaseComObject(myColumns)
        ReleaseComObject(myRows)
        ReleaseComObject(myRanges)
        ReleaseComObject(mySheetArray(i))
    Next
    mySheetArray = Nothing
    ReleaseComObject(mySheets)
    xl.Workbooks(1).Close(False)
    ReleaseComObject(myWorkbooks)
    xl.Quit() ReleaseComObject(xl)
End Sub
 
Private Sub ReleaseComObject(ByRef Reference As Object)
    Try        
        Do Until _
         System.Runtime.InteropServices.Marshal.ReleaseComObject(Reference)<=0
        Loop
    Catch
    Finally
        Reference = Nothing
    End Try
End Sub

Open the "Task Manager" window, run this code and watch the Excel process created and released. Note: Make sure that ASP_NET process has the right permissions to create the Excel object before you run this code.

To give ASP.NET the permissions it needs to use Excel, you might need to add the line <identity impersonate="true"/> to your web.config file or configure your app to run under an appropriate user account.

All comments are welcome.