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()
Private Sub createAndKillExcelObject()
Dim someExcelFile As String = "c:\myExcel.xls"
Dim xl As Object
xl = CreateObject("Excel.Application")
Dim myWorkbooks As Object = xl.Workbooks
xl.Visible = False
Dim mySheets As Object
mySheets = xl.Workbooks(1).Worksheets
Dim mySheetCount As Int16 = mySheets.count
Dim mySheetArray(mySheetCount) As Object
Dim i, j, k As Int16
For i = 1 To mySheetCount
mySheetArray(i) = mySheets(i)
Dim myRanges As Object = mySheets(i).UsedRange
Dim myColumns As Object = myRanges.Columns
Dim myColumnsCount As Int16 = myColumns.Count
Dim myRows As Object = myRanges.Rows
Dim myRowsCount As Int16 = myRows.Count
Dim myRowArray(myRowsCount) As Object
For j = 1 To myRowsCount
myRowArray(j) = myRows(j)
Dim myColumns_6 As Object = myRowArray(j).Cells(6)
Dim myHyperlink As Object = myColumns_6.Hyperlinks
If myHyperlink.Count > 0 Then
Dim myHyperlink_1 As Object = myColumns_6.Hyperlinks(1)
Dim adress As String = myHyperlink_1.Address
Dim myCells As Object = myRowArray(j).Cells
Dim myCells_2 As Object = myCells(2)
Dim cellText As String
cellText = myCells_2.Text
myRowArray = Nothing
mySheetArray = Nothing
Private Sub ReleaseComObject(ByRef Reference As Object)
Do Until _
Reference = Nothing
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.