6 Replies - 877 Views - Last Post: 16 October 2013 - 10:42 AM Rate Topic: -----

#1 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 200
  • View blog
  • Posts: 1,246
  • Joined: 11-May 12

Excel object won't dispose correctly

Posted 16 October 2013 - 06:09 AM

I have a program that is used to fix columns in an excel file, The issue I have is I can't find the correct way to dispose of an excel object. I tried using .Quit on the Excel object in combination with .Close, and there are 2 processes that stay opened up in task manager that say EXCEL.exe. I tried forcing garbage collection, and that gets rid of one of the EXCEL.exe processes, but the other stays. So I did a combination of both of the methods I just explained and that does dispose of the objects, but it seems to me to be a very awkward way of doing the task, and I was wondering if anyone knew of a better way, or a link that could put me in the right direction?

full code is below
comment's explain what was done to finally dispose of the objects.

Thanks :)/>
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices.Marshal
Imports System.IO
Public Class Form1
    Dim fPath As String = ""
    Dim fComparepath As String = ""
    Dim lstAnalyzedColumns As New List(Of String)
    Dim lstCompareTo As New List(Of String)
    Dim results As New List(Of String)
    Dim aBet() As String = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U"}

    Private Sub LoadToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadToolStripMenuItem.Click
        fPath = ""
        txtFilePath.Text = ""
        Dim ofd As OpenFileDialog = New OpenFileDialog()
        ofd.Filter = "Excel WorkSheets|*.xls;*.xlsx"
        Dim result As DialogResult = ofd.ShowDialog()
        If result = DialogResult.OK Then
            fPath = ofd.FileName
            txtFilePath.Text = fPath.Substring(fPath.LastIndexOf("\") + 1)
        End If
    End Sub

    ' this is part of what is used to dispose of the object
    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            FinalReleaseComObject(obj)
            ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Function CompareCols(ByVal lstCompare As List(Of String), ByVal lstAnalyzed As List(Of String))
        lsbMissing.Items.Clear()
        lsbUnknown.Items.Clear()
        lsbWrongPosition.Items.Clear()
        Dim res As New List(Of String)
        Dim unknown As New List(Of String)
        For i As Integer = 0 To lstAnalyzed.Count - 1
            If Not lstCompare.Contains(lstAnalyzed(i)) Then
                res.Add(String.Format("{0};{1}", lstAnalyzed(i), "Unknown"))
                unknown.Add(lstAnalyzed(i))
            End If
        Next
        For i As Integer = 0 To lstCompare.Count - 1
            If lstAnalyzed.Contains(lstCompare(i)) Then
                If Not lstAnalyzed.IndexOf(lstCompare(i)) = i Then
                    res.Add(String.Format("{0};{1};{2};{3}", lstCompare(i), "Placement", aBet(lstAnalyzed.IndexOf(lstCompare(i))), aBet(i)))
                End If
            Else
                If Not unknown.Contains(lstCompare(i)) Then
                    res.Add(String.Format("{0};{1}", lstCompare(i), "Missing"))
                End If
            End If
        Next
        Return res
    End Function

    Private Sub AnalyzeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AnalyzeToolStripMenuItem.Click
        If Not fPath = "" And Not fComparepath = "" Then
            lstAnalyzedColumns.Clear()
            lstCompareTo.Clear()
            ' declare my excel object along with the workbook and the worksheet
            Dim excel As New Excel.Application
            results = New List(Of String)
            Dim wb As Excel.Workbook = excel.Workbooks.Open(fPath)
            Dim ws As Excel.Worksheet = excel.ActiveSheet
            Dim range As Excel.Range = excel.Range("A1", "U1")
            Dim numRows As Integer = 1
            For i As Integer = 1 To range.Count
                lstAnalyzedColumns.Add(If(range.Item(i).Value = Nothing, "NULL", range.Item(i).Value.ToString))
            Next
            While ws.Cells(numRows, 1).Value IsNot Nothing
                numRows += 1
            End While
            ' using just these three lines didn't correctly dispose of any of the objects
            'wb.Close()
            'ws = Nothing
            'excel.Quit()
            ' using just these three lines disposed of 2 of the objects
            'ReleaseObject(excel)
            'ReleaseObject(wb)
            'ReleaseObject(ws)

            ' using these six lines together disposed of all the objects, but seems to be a very odd way of achieving the task.
            wb.Close()
            ws = Nothing
            excel.Quit()
            ReleaseObject(excel)
            ReleaseObject(wb)
            ReleaseObject(ws)

            excel = New Excel.Application
            wb = excel.Workbooks.Open(fComparepath)
            ws = excel.ActiveSheet
            range = excel.Range("A1", "U1")
            For i As Integer = 1 To range.Count
                lstCompareTo.Add(range.Item(i).Value.ToString)
            Next
            wb.Close()
            ws = Nothing
            excel.Quit()
            ReleaseObject(excel)
            ReleaseObject(wb)
            ReleaseObject(ws)
            lblNumRows.Text = "Number of Rows: " & numRows - 2
            results = CompareCols(lstCompareTo, lstAnalyzedColumns)
            For Each s As String In results
                Dim arg() As String = s.Split(";")
                If arg(0).Replace(";", "").Trim() = "" Then
                    arg(0) = "null"
                End If
                Select Case arg(1)
                    Case "Missing"
                        lsbMissing.Items.Add(arg(0))
                    Case "Placement"
                        lsbWrongPosition.Items.Add(arg(0))
                    Case "Unknown"
                        lsbUnknown.Items.Add(arg(0))
                End Select
            Next
        Else
            If fPath = "" Then
                MessageBox.Show("Choose a file to analyze")
            End If
            If fComparepath = "" Then
                MessageBox.Show("Choose a file to compare to")
            End If
        End If
    End Sub

    Private Sub ComparedToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComparedToolStripMenuItem1.Click
        fComparepath = ""
        txtComparedFile.Text = ""
        Dim ofd As OpenFileDialog = New OpenFileDialog()
        ofd.Filter = "Excel WorkSheets|*.xls;*.xlsx"
        Dim result As DialogResult = ofd.ShowDialog()
        If result = DialogResult.OK Then
            fComparepath = ofd.FileName
            txtComparedFile.Text = fComparepath.Substring(fComparepath.LastIndexOf("\") + 1)
        End If
    End Sub

    Private Sub CorrectPositionsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CorrectPositionsToolStripMenuItem.Click
        Dim excel As New Excel.Application
        Dim wb As Excel.Workbook = excel.Workbooks.Open(fPath)
        Dim ws As Excel.Worksheet = excel.ActiveSheet
        For Each s As String In results
            Dim arg() As String = s.Split(";")
            If arg(1) = "Placement" Then
                ws.Columns(String.Format("{0}:{0}", arg(2))).Cut()
                ws.Columns(String.Format("{0}:{0}", arg(3))).Insert(XlInsertShiftDirection.xlShiftToRight)
            End If
        Next
        'excel.SaveWorkspace(fPath)
        wb.Close()
        ws = Nothing
        excel.Quit()
        ReleaseObject(excel)
        ReleaseObject(wb)
        ReleaseObject(ws)
    End Sub
End Class


This post has been edited by ybadragon: 16 October 2013 - 06:15 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Excel object won't dispose correctly

#2 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1053
  • View blog
  • Posts: 4,081
  • Joined: 02-July 08

Re: Excel object won't dispose correctly

Posted 16 October 2013 - 09:35 AM

This should be enough to dispose of your objects. I would recommend not reusing your Excel variable, but rather create a new one for each excel object you have inside the Sub routine and disposing them when done. Your variable may still be holding a weak reference. FinalReleaseComObject calls ReleaseComObject in a loop until the object's value is 0 - so it is all you need.

Private Sub ReleaseObject(ByVal obj As Object)
  FinalReleaseComObject(obj)
  obj = Nothing
  GC.Collect()
  GC.WaitForPendingFinalizers()
  GC.Collect()
End Sub

Was This Post Helpful? 0
  • +
  • -

#3 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 200
  • View blog
  • Posts: 1,246
  • Joined: 11-May 12

Re: Excel object won't dispose correctly

Posted 16 October 2013 - 09:42 AM

That still left 2 EXCEL.exe processes running, I also changed it so it isn't using the same variables again, and it still has the same result.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3462
  • View blog
  • Posts: 11,729
  • Joined: 12-December 12

Re: Excel object won't dispose correctly

Posted 16 October 2013 - 09:54 AM

I wouldn't start and stop new instances of Excel in each of your procedures, creating new object references at the same time.

I would consider encapsulating the Excel activities within a Class, perhaps even following a Singleton Pattern. (It is also possible to check if Excel is already running and use that instance.) Edited: Perhaps not a singleton pattern, as you don't want to keep Excel open longer than necessary.

But a first approach would be to create a single object reference for Excel, and perhaps for a Workbook, outside of the individual methods.

This post has been edited by andrewsw: 16 October 2013 - 10:00 AM

Was This Post Helpful? 0
  • +
  • -

#5 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1053
  • View blog
  • Posts: 4,081
  • Joined: 02-July 08

Re: Excel object won't dispose correctly

Posted 16 October 2013 - 10:07 AM

You have to close the objects in reverse order with the Excel object as last.
Was This Post Helpful? 2
  • +
  • -

#6 ybadragon  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 200
  • View blog
  • Posts: 1,246
  • Joined: 11-May 12

Re: Excel object won't dispose correctly

Posted 16 October 2013 - 10:11 AM

That worked :) thanks :)
Was This Post Helpful? 0
  • +
  • -

#7 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1053
  • View blog
  • Posts: 4,081
  • Joined: 02-July 08

Re: Excel object won't dispose correctly

Posted 16 October 2013 - 10:42 AM

Your welcome!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1