Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   Vb 2010 MS Excel automation... Won't release excel instance (http://www.xtremevbtalk.com/-net-office-automation/327126-vb-2010-ms-excel-automation-wont-release-excel-instance.html)

Bink64 06-25-2014 02:34 PM

Vb 2010 MS Excel automation... Won't release excel instance
 
I have read through Mike Rosenblum's posts on Automation, but I'm still having issues with relasing the excel instance. I'm using excel 2010 and Vb 2010.

The issue is when I loop through the cells in Range1 and Range2, but I don't know how to do it to release all of the range objects that are created in the loop??:confused:

Here is my code, I have removed part of it that I'm commenting out in the trobleshooting process.

Code:

    Sub OpenWorksheets()
        Dim xlApp As Excel.Application
        Dim xlWorkbooks As Excel.Workbooks
        Dim xlWorkbook1 As Excel.Workbook
        Dim xlWorkbook2 As Excel.Workbook
        Dim xlWorksheets1 As Excel.Sheets
        Dim xlWorksheets2 As Excel.Sheets
        Dim xlWorksheet1 As Excel.Worksheet
        Dim xlWorksheet2 As Excel.Worksheet
        Dim cell1 As Excel.Range
        Dim cell2 As Excel.Range
        Dim Range1 As Excel.Range
        Dim Range2 As Excel.Range

        Dim iFile1Cnt As Int32
        Dim iFile2Cnt As Int32
        Dim iTempCount As Int32
        Dim x As Int32
        Dim y As Int32
        Dim blnWrite As Boolean
        Dim dblColWidth As Double



        Me.txtRangeFile1.Text = Replace(Me.txtRangeFile1.Text, "$", "")
        Me.txtRangeFile2.Text = Replace(Me.txtRangeFile2.Text, "$", "")
        Me.txtFile1Cnt.Text = CalcCellCount(Me.txtRangeFile1.Text).ToString
        Me.txtFile2Cnt.Text = CalcCellCount(Me.txtRangeFile2.Text).ToString

        If Me.txtFile1Cnt.Text = Me.txtFile2Cnt.Text And (Me.txtFile1Cnt.Text <> "" Or Me.txtFile2Cnt.Text <> "") Then
            Me.txtFile1Cnt.BackColor = Color.White
            Me.txtFile2Cnt.BackColor = Color.White
            Me.tspb1.Minimum = 0
            Me.tspb1.Maximum = CInt(Me.txtFile1Cnt.Text)
            Me.tspb1.Value = 0
            Me.tspb1.Visible = True
            Me.StatusStrip1.Text = "Starting File Comparison"

            xlApp = New Excel.Application
            xlWorkbooks = xlApp.Workbooks
            xlWorkbook1 = xlWorkbooks.Open(Me.txtFile1.Text, , True)
            xlWorkbook2 = xlWorkbooks.Open(Me.txtFile2.Text, , True)

            xlWorksheets1 = xlWorkbook1.Sheets
            xlWorksheets2 = xlWorkbook2.Sheets

            xlWorksheet1 = xlWorksheets1.Item(Me.lstFile1.SelectedItem.ToString)
            xlWorksheet1.Activate()
            xlWorksheet2 = xlWorksheets2.Item(Me.lstFile2.SelectedItem.ToString)
            xlWorksheet2.Activate()

            'xlApp.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal)

            xlApp.Visible = False

            iFile1Cnt = 0
            iFile2Cnt = 0

            With Me.dgvCompare
                dblColWidth = (.Width - 145) / 4
                .Rows.Clear()
                .Columns.Clear()

                .Columns.Add("F1 Cell", "F1 Cell")
                .Columns(0).Width = 50
                .Columns.Add("F2 Cell", "F2 Cell")
                .Columns(1).Width = 50
                .Columns.Add("F1 Value", "F1 Value")
                .Columns(2).Width = dblColWidth
                .Columns.Add("F2 Value", "F2 Value")
                .Columns(3).Width = dblColWidth
                .Columns.Add("F1 Formula", "F1 Formula")
                .Columns(4).Width = dblColWidth
                .Columns.Add("F2 Formula", "F2 Formula")
                .Columns(5).Width = dblColWidth
            End With

            SetGridColumns()

            Range1 = xlWorksheet1.Range(Me.txtRangeFile1.Text)
            Range2 = xlWorksheet2.Range(Me.txtRangeFile2.Text)
            MsgBox(Range1.Count)
            '***
            iTempCount = 0
            For x = 1 To Range1.Columns.Count 'columns
                For y = 1 To Range1.Rows.Count 'rows
                    Me.tssl1.Text = "Checking column: " & x & " row:" & y
                    Me.tspb1.Increment(1)

                    With Me.dgvCompare
'****If I comment out the  cell1 and cell2 lines it releases the excel instance
                        cell1 = Range1(y, x)
                        cell2 = Range2(y, x)

                    End With

                    ReleaseExcelObjects(cell2)
                    ReleaseExcelObjects(cell1)
                Next y
            Next x

            Me.dgvCompare.FirstDisplayedScrollingRowIndex = 0

            Me.tspb1.Value = 0
            Me.tspb1.Visible = False
            Me.tssl1.Text = "File Compare is Complete"

            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()


            ReleaseExcelObjects(cell2)
            ReleaseExcelObjects(cell1)
            ReleaseExcelObjects(Range2)
            ReleaseExcelObjects(Range1)
            ReleaseExcelObjects(xlWorksheet2)
            ReleaseExcelObjects(xlWorksheet1)
            ReleaseExcelObjects(xlWorksheets2)
            ReleaseExcelObjects(xlWorksheets1)
            xlWorkbook2.Close(False)
            ReleaseExcelObjects(xlWorkbook2)
            xlWorkbook1.Close(False)
            ReleaseExcelObjects(xlWorkbook1)
            ReleaseExcelObjects(xlWorkbooks)
            xlApp.Quit()
            ReleaseExcelObjects(xlApp)

        Else
            MsgBox("The File 1 cell qty MUST match the File 2 cell qty!")
            Me.txtFile1Cnt.BackColor = Color.Tomato
            Me.txtFile2Cnt.BackColor = Color.Tomato
            Me.StatusStrip1.Text = "Cell Qty must match!"
        End If

    End Sub

    Sub ReleaseExcelObjects(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub


Rapiant 10-09-2014 08:55 PM

I have a question for you - have you tried running the code without the debugger?

GC.Collect doesn't necessarily work when you're debugging, see link below:

http://stackoverflow.com/questions/7...-in-debug-mode

xlZ3r0 01-16-2015 08:46 AM

Why can't you just set your Range, Worksheet, & Workbook Objects to nothing manually instead of using a separate function?

Once the Sub Routine finishes, the references should be released if they are set nothing. At least that's how I understood Visual Basic to work. Maybe I missed a step.

zzstuzor 03-10-2015 12:00 PM

This is very old, but maybe helpful to someone as a reference.

You're referencing Range1.Columns anonymously and then again for each row, without cleaning them up. But at least you are cleaning up each cell within the row. Best to set the Columns to an object and clean it up after, and Dim a reference to Rows outside the loop but only set it inside the loop and clean it up at the end of its loop.

It's fine to release the excel objects in a separate function but don't call GC.Collect EVERY time you call the function. Call it once at the end of the whole function.

The goal with the GC.Collect() is to release all the objects from their reference handles, so they are floating handleless, and THEN call the GC twice. You're calling it like 20 times. The GC cleans up everything that doesn't have a reference handle. So, you can imagine there is no point calling the following code RIGHT BEFORE you release all the objects.

Put this code at the end of everything, right before End Sub.
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

Also remove the Finally section from your ReleaseExcelObjects method, so your GC only gets called at the end of your OpenWorksheets function, and not each time you release an object. Release each object handle and let them clean up shortly after, a few milliseconds won't make a difference ;)

WFaz 05-16-2015 05:21 PM

COM Errors
 
I have managed to get Excel to disappear from the task manager in most cases. I use late binding to insure compatibility with the user's installed version of Excel no matter what version I have installed (2010). I avoid all double dot references by creating named variables for every workbook, worksheet and range. I call GC.Collect and wait for pending finalizers twice, and then specifically use FinalReleaseComObject for every object.
My only problem is that I cannot figure out how to kill Excel if a Com Error occurs in my code. If an error is thrown, Excel will disappear from the task list after my application is stopped, but not after the user closes Excel. Any ideas on how to solve this last problem?
Thanks, Bill

Here is the typical code structure ...

Try

oKxl = CreateObject("Excel.Application")
oBooks = oKxl.Workbooks
oKwb = oBooks.Open(strDataPath & strFileName, , True)
oSheets = oKwb.Worksheets
oKws = oSheets.Item(1)
oKxl.DisplayAlerts = False
oKxl.AlertBeforeOverwriting = False
oKxl.ScreenUpdating = False
oKxl.Visible = False
oKws.EnableCalculation = False

DO SOME STUFF HERE

oKws.PageSetup.PrintArea = "A1:N" & CStr(xlRow - 1)
oKxl.ScreenUpdating = True
oKws.EnableCalculation = True
oKxl.Calculate()
oKxl.Visible = True
oKxl.UserControl = True

Catch Ex As Exception
MessageBox.Show("Appropriate Error Message")
If oKxl IsNot Nothing Then
oKxl.Quit()
End If

Finally
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oKws)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oSheets)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oKwb)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oBooks)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oKxl)

End Try


All times are GMT -6. The time now is 12:47 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.