.net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing
.net automation excel app not closing .net automation excel app not closing
.net automation excel app not closing
Go Back  Xtreme Visual Basic Talk > > > .net automation excel app not closing


Reply
 
Thread Tools Display Modes
  #1  
Old 09-25-2009, 12:52 PM
chewmon34 chewmon34 is offline
Newcomer
 
Join Date: Sep 2009
Posts: 2
Default .net automation excel app not closing


Hello Everyone,

So I've read Mike's very helpful thread here But I'm still having an issue where my excel.exe process doesn't close until I close my .net application.

I'd like to avoid using Process.Kill() if at all possible.

I was hoping someone could take a look at my code and see if anything jumps out.

Code:
    Private Sub WriteRowsToXls(ByVal rows As DataRowCollection, ByVal xlsPath As String, ByVal showStatus As Boolean, Optional ByVal AddTotals As Boolean = True)
        Dim xlsApp As New Microsoft.Office.Interop.Excel.Application
        Dim wb As Workbook = Nothing
        'StatusDialog.CloseStatus()
        If showStatus Then StatusDialog.ChangeStatus("Loading Information")
        Dim ws As Worksheet = Nothing
        Dim wSheets As Sheets = Nothing

        Try

            xlsApp.Workbooks.Open(xlsPath)

            wb = xlsApp.ActiveWorkbook

            wSheets = wb.Worksheets
            ws = wSheets(StudentsWS)
            ws.Unprotect(StudentsWSPW)

            Dim updateEvery As Integer = 0
            Dim updateCounter As Integer = 0
            Dim startRow As Long = 2
            Dim curRow As Long = startRow
            Dim pdEqua As String = "=sumstudentpayments(A"
            'owedEqua = E-F
            Dim studentCol As Integer = 1
            Dim classCol As Integer = 2
            Dim periodCol As Integer = 3
            Dim soldCol As Integer = 4
            Dim dueCol As Integer = 5
            Dim paidCol As Integer = 7
            Dim adjustCol As Integer = 6
            Dim owedCol As Integer = 8

            updateEvery = CInt(rows.Count / 10)
            If updateEvery < 5 Then updateEvery = 5

            ws.Activate()
            xlsApp.ScreenUpdating = False
            'xlsApp.DisplayAlerts = False
            'xlsApp.Visible = True
            With ws
                For Each r As DSTeacherTally.spNewStudentTotalBschRow In rows
                    .Cells(curRow, studentCol).value = r.fname & " " & r.lname
                    .Cells(curRow, classCol).value = r.group_name
                    .Cells(curRow, periodCol).value = r.period
                    .Cells(curRow, soldCol).value = r.totalPcs
                    .Cells(curRow, dueCol).value = r.TotalPreTax
                    .Cells(curRow, paidCol).formula = pdEqua & curRow.ToString & ")"
                    'ws.Range(excel.Cells(curRow, paidCol), Cells(curRow, paidCol)).formula = pdEqua & curRow.ToString & ")"
                    .Cells(curRow, owedCol).formula = "=E" & curRow.ToString & " + F" & curRow.ToString & " - G" & curRow.ToString
                    curRow += 1
                    If showStatus Then
                        updateCounter += 1
                        If updateCounter >= updateEvery Then
                            updateCounter = 0
                            StatusDialog.ChangeStatus("Loading Information" & vbCrLf & Math.Round((curRow - startRow) / rows.Count, 2, MidpointRounding.AwayFromZero).ToString("P0") & " Complete")
                        End If
                    End If
                Next



                If showStatus Then StatusDialog.ChangeStatus("Cleaning Up And Saving File")

                SetRangeAllBorders(.Range(.Cells(startRow, studentCol), .Cells(curRow - 1, owedCol)))
                '.Range(.Cells(startRow, adjustCol), .Cells(curRow - 1, adjustCol)).Value = 0

                SetRangeToCurrency(.Columns(dueCol), False)
                SetRangeToCurrency(.Columns(paidCol), False)
                SetRangeToCurrency(.Columns(adjustCol), True)
                SetRangeToCurrency(.Columns(owedCol), True)


                'Due, Adjustments, Paid, Owed, Sum E, F, G, H
                If AddTotals Then
                    Dim lastUsed As Long = curRow
                    Dim dueMod As Integer = 2
                    Dim paidMod As Integer = 4
                    Dim adjustedMod As Integer = 3
                    Dim owedMod As Integer = 5

                    .Cells(lastUsed + dueMod, 1) = "Total Due"
                    .Cells(lastUsed + dueMod, 2).formula = "=Sum(E" & startRow & ":E" & lastUsed & ")"
                    .Cells(lastUsed + adjustedMod, 1) = "Total Adjustments"
                    .Cells(lastUsed + adjustedMod, 2).formula = "=Sum(F" & startRow & ":F" & lastUsed & ")"
                    .Cells(lastUsed + paidMod, 1) = "Total Paid"
                    .Cells(lastUsed + paidMod, 2).formula = "=Sum(G" & startRow & ":G" & lastUsed & ")"

                    .Cells(lastUsed + owedMod, 1) = "Total Owed"
                    .Cells(lastUsed + owedMod, 2).formula = "=Sum(H" & startRow & ":H" & lastUsed & ")"

                    SetRangeAllBorders(.Range(.Cells(lastUsed + dueMod, 1), .Cells(lastUsed + owedMod, 2)))
                    With .Range(.Cells(lastUsed + dueMod, 1), .Cells(lastUsed + owedMod, 1))
                        .Interior.ColorIndex = 15
                        .Font.Bold = True
                    End With

                End If



                'Add Named Range Students that Has the entire range of Student Names
                wb.Names.Add(Name:=StudentsWS, _
                RefersTo:=.Range("A" & startRow.ToString & ":A" & curRow.ToString), _
                Visible:=False)

                .Protect(Password:=StudentsWSPW, DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
                AllowUsingPivotTables:=True)

                'ws.Protect(StudentsWSPW)
            End With

            With wb.Worksheets(PaymentsWS)
                .unprotect(PaymentsWSPW)
                SetRangeValidation(.columns(2))

                .Protect(Password:=PaymentsWSPW, DrawingObjects:=True, Contents:=True, Scenarios:=True _
                , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
                AllowUsingPivotTables:=True)
            End With

            wb.Worksheets(1).activate()
            'xlsApp.ScreenUpdating = True

            'System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws)
            'ws = Nothing
            'wb.Close(True)
            'xlsApp.Quit()
        Catch ex As Exception
            HandleError(ex, "Error Creating Teacher Tally File")
            'Try
            'If Not wb Is Nothing Then wb.Close(True)
            'xlsApp.Quit()
            'Catch ex2 As Exception
            'HandleError(ex2, "Error Closing Excel")
            'End Try
        Finally
            ' Cleanup:
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()

            If Not ws Is Nothing Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws)

            If Not wSheets Is Nothing Then System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wSheets)

            If Not wb Is Nothing Then
                wb.Close(True)
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb)
            End If


            If Not xlsApp Is Nothing Then
                xlsApp.Quit()
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlsApp)
            End If

            ws = Nothing
            wSheets = Nothing
            wb = Nothing
            xlsApp = Nothing
            'GC.Collect()

        End Try
    End Sub
This method references a couple of other methods I use for repeated formatting tasks, I'll include those methods below. (I added the FinalReleaseComObject into the other methods after reading Mike's thread but the issue persists)

Code:
    Private Sub SetRangeValidation(ByVal rng As Range)
        With rng.Validation
            .Delete()
            .Add(Type:=XlDVType.xlValidateList, AlertStyle:=XlDVAlertStyle.xlValidAlertStop, Operator:= _
            XlFormatConditionOperator.xlBetween, Formula1:="=Students")
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "Oops!"
            .InputMessage = ""
            .ErrorMessage = "You must select a student from the list"
            .ShowInput = False
            .ShowError = True
        End With
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rng)
        rng = Nothing
    End Sub
Code:
    Private Sub SetRangeToCurrency(ByVal rng As Range, ByVal redNegative As Boolean)
        rng.Style = "Currency"
        If redNegative Then rng.NumberFormat = "$#,##0.00;[Red]$#,##0.00"
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rng)
        rng = Nothing
    End Sub
Method SetRangeAllBorders does about the same as these other methods but my post is too long to add it.

The only other things not shown in these code snippets are a few string variable that are defined in the class, studentWS, studentsWSPW, paymentsWS and paymentsWSPW

My App creates an instance of the class, loads some data, runs the method above, and then sets the class to nothing.

EDIT: I'm using .NET 3.5 automating Excel 10

Hopefully I'm missing something obvious.
Thanks in Advance,
Jim

Last edited by chewmon34; 09-25-2009 at 01:09 PM. Reason: Forgot to mention .Net and Excel Versions
Reply With Quote
  #2  
Old 09-25-2009, 01:43 PM
chewmon34 chewmon34 is offline
Newcomer
 
Join Date: Sep 2009
Posts: 2
Default

I think I figured it out.

If I put the GC.Collect calls after I set the instance of my class to nothing, then it seems like the process goes away every time.
Reply With Quote
  #3  
Old 09-26-2009, 08:20 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Ok, glad you got it.

I don't really see anything wrong with your code, but there is a lot there, so I could have missed something.

As I understand how things work, putting GC.Collect at the beginning and then explicitly releasing your named references via Marshal.FinalReleaseComObject at the end is the correct approach. But most other code I see puts it at the end, and this seems to run fine.

The advantage to putting it at the end is that if you have a mistake somewhere and a variable to which you have a named reference is set to 'nothing' without calling Marshal.FinalReleaseComObject, then you definitely want to call GC.Collect only *after* that reference is set to nothing, and so calling GC.Collect at the end is safer in this respect.

I didn't see anything like that in your code, but with so much there I can't be sure.

Anyway, putting it at the end is more than fine. Most importantly, you got it to release, and that's what counts!
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


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
.net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing .net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing .net automation excel app not closing
.net automation excel app not closing
.net automation excel app not closing
 
.net automation excel app not closing
.net automation excel app not closing
 
-->