Philip C. Philip C. - 4 months ago 15
Vb.net Question

VB.Net, Utilizing Excel assemblies, Memory leak

My program is essentially a form that uses the excel assemblies to open a file and puts the information into array lists.

After running my program multiple iterations I have found that in task manager I have multiple excel files still open, although they are hidden from my task bar. Additionally "System and compressed memory" takes up more space each time.

I assume that I did not properly release memory but I am not sure how, I currently have:

shXL = Nothing
wbXl = Nothing
appXL.Quit()
appXL = Nothing


Note: shxl is the worksheet, wbxl is the workbook, and appxl is the application.

Any help Would be appreciated

Answer

Here is some fully working open and close code

Private _xlApp As Microsoft.Office.Interop.Excel.Application
Private _xlWSheet As Microsoft.Office.Interop.Excel.Worksheet
Private _xlWBook As Microsoft.Office.Interop.Excel.Workbook

Private Sub btnOpen_Click(sender As Object, e As EventArgs) Handles btnOpen.Click
    Dim filename As String = "C:\Scratch\Test.xlsx"
    _xlApp = New Microsoft.Office.Interop.Excel.Application
    _xlApp.DisplayAlerts = False
    _xlWBook = _xlApp.Workbooks.Open(filename)
End Sub

Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
    'close the workbook and quit the app
    If _xlWBook IsNot Nothing Then _xlWBook.Close()
    If _xlApp IsNot Nothing Then _xlApp.Quit()
    'destroy the objects
    If _xlWSheet IsNot Nothing Then _xlWSheet = Nothing
    If _xlWBook IsNot Nothing Then _xlWBook = Nothing
    If _xlApp IsNot Nothing Then _xlApp = Nothing

    'Force garbage collection
    GC.Collect()
End Sub

Also note that if you are not setting: _xlApp.DisplayAlerts = False then when you close the workbook, if you have any unsaved changes, this will wait for a non existant user to click yes or no to a "Save Changes" dialog which will probably cause the objects to hang around

Comments