Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Go Back  Xtreme Visual Basic Talk > > > Vb 2010 MS Excel automation... Won't release excel instance


Reply
 
Thread Tools Display Modes
  #1  
Old 06-25-2014, 03:34 PM
Bink64 Bink64 is offline
Newcomer
 
Join Date: Jun 2014
Location: Charlotte, NC
Posts: 1
Unhappy 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??

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

Last edited by OnErr0r; 06-25-2014 at 10:06 PM. Reason: Added [code][/code] tags
Reply With Quote
  #2  
Old 10-09-2014, 09:55 PM
Rapiant's Avatar
Rapiant Rapiant is offline
Centurion
 
Join Date: Jun 2008
Location: Iowa
Posts: 111
Default

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
__________________
--Adam Lee--

Website
My Blog
Reply With Quote
  #3  
Old 01-16-2015, 09:46 AM
xlZ3r0 xlZ3r0 is offline
Newcomer
 
Join Date: Jan 2015
Posts: 8
Default

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.
Reply With Quote
  #4  
Old 03-10-2015, 01:00 PM
zzstuzor zzstuzor is offline
Newcomer
 
Join Date: Mar 2015
Posts: 1
Smile

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
Reply With Quote
  #5  
Old 05-16-2015, 06:21 PM
WFaz WFaz is offline
Newcomer
 
Join Date: May 2015
Posts: 1
Default 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

Last edited by WFaz; 05-16-2015 at 06:37 PM. Reason: Add some code
Reply With Quote
Reply

Tags
excel 2010 automation, interop, marshalrelease, vb 2010


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
 
Vb 2010 MS Excel automation... Won't release excel instance
Vb 2010 MS Excel automation... Won't release excel instance
 
-->