Release Excel Objects From memory

[RonPepe]

I have been trying to release an Excel object from memory. How is this done? I create the object using

Excel_App = New Microsoft.Office.Interop.Excel.Application

I have tried many, many ways to actually get rid of it from memory, what works? ( I mean what REALLY works?)

 

[earthandfire]

This has been covered many times here (including quite recently, I think), however this may help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

Hope this helps.

 

[firefytr]

Is there a reason to release it from memory before the routine ends? When the routine ends it should clear it from memory (AFAIAA). But to do so before the routine end, the Object = Nothing works.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[earthandfire]

If you check the running processes in Task Manager, you will find that unless you follow the advice in the link I posted an instance of Excel could still be running after your program has terminated.


Hope this helps.

 

[TipGiver]

With EXCEL and programs like that, you should close the app when finished (excel working at background). By close i mean to at least exit the application as you would do if you directly used it.

In general, when you use 'new':
- If app works at background see above.
- object.close (streams, etc)
- object.dispose (if exist htis property)
- object = Nothing

In .NET the garbage collector works pretty fine, but not rely on it help him by {dispose / nothing}

 

[firefytr]

I'm not talking about the instance running, that is a given, hence the .Quit command. I'm talking being released from memory.

I'm more familiar with VB than VB.NET though. In VB, it releases when run-time is over. I was under the impression dotNET was the same.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

.. I was under the impression dotNET was the same.

Hehe, let me make certain that I'm only referring to this particular subject.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[TipGiver]

Yes, but if you do not need the objects.. release them. Dont just wait the GC or the user to quit ..

 

[firefytr]

Matt Curland disagrees, and if it's good enough for him then it's sure good enough for me...

http://tinyurl.com/j9qkf

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[fawkes]

I've found, with com objects, that you should explicitly declare an object to help dispose of it.

For example, I wouldn't use excelApp.Sheets(0).Cells(3,2) i would declare a sheet and range object

dim myShett as worksheet
dim myCell as range
mySheet=excellApp.Sheets(0) 
mycell=mysheet.cells(3,2)

It makes it a lot easier to dispose of the objects. I especially found this out using the Mathcad API.

 

[nwruiz]

I am another victim of this COM problem. Below is the code snippet I use to export a custom object to Excel.

    Public Overloads Sub ExportToExcel(ByVal filepath As String)
        Dim xlsApp As Excel.Application
        Dim xlsWkbk As Excel.Workbook
        Dim xlsSht As Excel.Worksheet
        Dim xlsRange As Excel.Range
        Dim templateName As String = "c:\Data\Suez_HourlyData.xlt"
        Dim tmpBillDate As String = Me.PostDate.ToString("MM/dd/yyyy")

        Dim x As Object = Type.Missing

        ' Detail variables
        Dim i As Integer
        Dim hdrRows As Integer = 7
        Dim nRow As Integer
        Dim nCol As Integer
        Dim time1 As Date = Now()

        Try

            xlsApp = New Excel.Application
            xlsApp.DisplayAlerts = False
            xlsWkbk = xlsApp.Workbooks.Open(templateName, x, x, x, x, x, x, x, x, x, False, x, x, x)
            xlsSht = xlsWkbk.ActiveSheet
            xlsApp.Visible = False

            ' Start writing to Excel
            ' Header output
            xlsRange = xlsSht.Cells(1, 2)               'B1
            xlsRange.Value2 = Me.EdcAcct
            xlsRange = xlsSht.Cells(2, 2)               'B2
            xlsRange.Value2 = Me.KyBaLeadZeros
            xlsRange = xlsSht.Cells(3, 2)               'B3
            xlsRange.Value2 = Me.KyEnroll
            xlsRange = xlsSht.Cells(4, 2)               'B4
            xlsRange.Value2 = tmpBillDate

            ' Detail output
            For i = 0 To Me.RowCount - 1
                nRow = hdrRows + (i + 1)
                nCol = 1 ' SR_SEQ_No
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.SrSeqNo
                nCol = 2 ' DT
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.Dt(i)
                nCol = 3 ' HHMM
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.Hhmm(i)
                nCol = 4 ' MeteredKWh
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.MeteredKwh(i)
                nCol = 5 ' ActualKWh
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.ActualKwh(i)
                nCol = 6 ' Peak
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.Peak(i)
                nCol = 7 ' Indexprice
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.IndexPrice(i)
                nCol = 8 ' Price adder
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.PriceAdder(i)
                nCol = 9 ' Pricing Zone
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.PricingZone(i)
                nCol = 10 ' Index Only Energy Charge
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.IndexOnly(i)
                nCol = 11 ' Index Adder Energy Charge
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = Me.IndexPlusAdder(i)
                nCol = 12 ' Post Date
                xlsRange = xlsSht.Cells(nRow, nCol)
                xlsRange.Value2 = tmpBillDate
            Next

            ' Save the workbook
            'xlsWkbk.AcceptAllChanges()
            xlsWkbk.SaveAs(filepath, Excel.XlFileFormat.xlWorkbookNormal, _
                x, x, False, False, Excel.XlSaveAsAccessMode.xlNoChange, _
                Excel.XlSaveConflictResolution.xlLocalSessionChanges, False, x, x)

        Catch comEx As COMException
            Debug.WriteLine(comEx.ToString())
        Catch ex As Exception
            Debug.WriteLine(ex.ToString())
        Finally
            ' Free up resources
            xlsWkbk.Close()
            xlsApp.Quit()

            ReleaseComObject(xlsRange)
            ReleaseComObject(xlsSht)
            ReleaseComObject(xlsWkbk)
            ReleaseComObject(xlsApp)
        End Try

        Dim time2 As Date = Now()
        Dim timeDiffInSeconds As Double = (time2.Ticks - time1.Ticks) / 10000000.0
        Debug.WriteLine("Excel time in seconds: " & timeDiffInSeconds)
    End Sub

    Private Sub ReleaseComObject(ByRef reference As Object)
        Try
            Do Until _
                System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) <= 0
            Loop
        Catch ex As COMException
            Debug.WriteLine(ex.Message)
        Finally
            reference = Nothing
        End Try
    End Sub

The code runs, but does not always release the COM objects associated with the Excel process. This code snippet is inside of a class I am writing. My test driver uses 150+ of these objects, to write 150 bill statements to Excel.

Does anyone know what needs to be done to fix this problem? Also, I've noticed that this export feature is significantly slower than its strictly-VB counterpart. Any ideas as to how this code may be optimized?

Thanks for your help.
Nick Ruiz