View Single Post
 
Old 06-25-2014, 02: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 09:06 PM. Reason: Added [code][/code] tags
Reply With Quote