Click here to Skip to main content
12,695,365 members (28,504 online)
Click here to Skip to main content
Add your own
alternative version

Stats

122.9K views
35 bookmarked
Posted

Release Excel Object

, 28 Apr 2005
Rate this:
Please Sign up or sign in to vote.
How to kill 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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

antony100
Web Developer
United States United States
No Biography provided

You may also be interested in...

Pro

Comments and Discussions

 
GeneralThanks Pin
Simon Hawkin6-Mar-09 8:00
memberSimon Hawkin6-Mar-09 8:00 
GeneralBetter than Excel Automation Pin
BarbaMariolino13-Jun-08 5:01
memberBarbaMariolino13-Jun-08 5:01 
QuestionI've got a problem ASP .NET Pin
ricardo_pr22-Nov-07 15:26
memberricardo_pr22-Nov-07 15:26 
Questionhow to kill the object in asp Pin
wkotesh@hotmail.com3-Aug-07 5:14
memberwkotesh@hotmail.com3-Aug-07 5:14 
GeneralThank you! It works! Pin
jonax2710-Jul-07 18:37
memberjonax2710-Jul-07 18:37 
Generalbut there is some error, please correct it Pin
voorugonda prashanth12-Jun-07 4:46
membervoorugonda prashanth12-Jun-07 4:46 
GeneralGreat Pin
Zodraz31-Jan-07 4:23
memberZodraz31-Jan-07 4:23 
QuestionExcel cell formulae in vb.net Pin
umeshkumar2610-Aug-06 3:08
memberumeshkumar2610-Aug-06 3:08 
GeneralThank You - plus minor addition Pin
4Bugs16-Oct-05 3:06
member4Bugs16-Oct-05 3:06 
GeneralThank you!!!!!! Pin
Anonymous22-Mar-05 5:20
sussAnonymous22-Mar-05 5:20 
GeneralactiveX Error Pin
Recep GÜZEL15-Mar-05 3:38
memberRecep GÜZEL15-Mar-05 3:38 
GeneralRe: activeX Error Pin
Recep GÜZEL15-Mar-05 3:51
memberRecep GÜZEL15-Mar-05 3:51 
GeneralRe: activeX Error Pin
Anonymous31-Jul-05 22:06
sussAnonymous31-Jul-05 22:06 
Generali need to get the hyperlinks' paths too Pin
tarathecasper24-Feb-05 0:12
membertarathecasper24-Feb-05 0:12 
QuestionWhy ? Pin
Christian Graus18-Jan-05 15:42
memberChristian Graus18-Jan-05 15:42 
AnswerRe: Why ? Pin
Simon Hawkin6-Mar-09 8:01
memberSimon Hawkin6-Mar-09 8:01 
GeneralFeedback on article/code Pin
M.Lansdaal18-Jan-05 15:24
memberM.Lansdaal18-Jan-05 15:24 
GeneralRe: Feedback on article/code Pin
antony10021-Jan-05 8:44
memberantony10021-Jan-05 8:44 
GeneralRe: Feedback on article/code Pin
Anonymous4-May-05 19:58
sussAnonymous4-May-05 19:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 28 Apr 2005
Article Copyright 2005 by antony100
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid