Link to home
Start Free TrialLog in
Avatar of Tom Knowlton
Tom KnowltonFlag for United States of America

asked on

VB .NET: Excel application process is not terminating in Task Manager - Windows 2000 Pro

Why isn't the process terminating?



Here is the code:



Public Sub fnMonthlyReport()
        Dim filename
        filename = "c:\MonthlyReport\Report_" & Year(Now) & "_" & IIf(Month(Now) < 10, "0" & Month(Now), Month(Now)) & "_" & IIf(Day(Now) < 10, "0" & Day(Now), Day(Now))

        Dim excelApp = New Excel.Application()
        Dim excelWorksheet As Excel.Worksheet
        Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add

        excelWorksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)

        Dim book As Excel.Workbook
        Dim sheet As Excel.Worksheet
        Dim ae As New AEList.AEList()

        dsAEList = ae.fnReturnAEList

        'this will make excel never popup prompt windows
        '     excelApp.Application.DisplayAlerts = False

        excelApp.DisplayAlerts = False

        'Me.excelBook.Application.DisplayAlerts = False


        For Each drAEList In dsAEList.Tables(0).Rows
            ' for your request 1

            fnMonthlyReportSheet(excelBook.Worksheets.Add(after:=excelBook.Worksheets(excelBook.Worksheets.Count)), drAEList)

            ' for your request 2
            book = excelApp.Workbooks.Add()
            fnMonthlyReportSheet(book.Worksheets(1), drAEList)
            book.Worksheets("Sheet2").Delete()
            book.Worksheets("Sheet3").Delete()
            book.SaveAs(filename & "_" & fnCleanString(drAEList(1)) & ".xls")
            book.Close()  ' I think you what to close it
        Next 'Next AE
        excelBook.Worksheets("Sheet1").Delete()
        excelBook.Worksheets("Sheet2").Delete()
        excelBook.Worksheets("Sheet3").Delete()
        excelBook.SaveAs(filename & ".xls")
        excelBook.Close() ' do you want to close it????

        excelApp.Application.Quit()
        excelApp = Nothing

    End Sub
Avatar of iboutchkine
iboutchkine

You can prevent Excel hanging around by explicitly cleaning up any Excel
objects you instantiated with Marshal.ReleaseComObject and then invoking the
..NET garbage collecter before exiting.

Something like:

While (Marshal.ReleaseComObject(m_excelObj) <> 0) ' Repeat until no
more COM references
End While
m_excelObj = nothing
..... repeat for any other Excel objects allocated

' Invoke garbage collector before termination
GC.Collect()
GC.WaitForPendingFinalizers()
This is the right behavior by using .net to call unmanaged code (Excel). You are actually just release the reference to the wrapper for the excel object not the the cOM refernece. You can use iboutchkine's code to force the collection of the excel object. It is a expensive process, generally you create an excel object and use the same object through out your app, kill it when you close your app. If for a web app, you can use the excel as a service and always have one instance running.
Avatar of Tom Knowlton

ASKER

You are both saying that the behavior I am seeing is NORMAL?

Is the code you are providing me the correct way to free the Excel object, or is it sort of a hack?

I'm just concerned that excelApp.Quit is not getting rid of the process.  I really expected it to do so.

Can you provide deeper insight into the problem I am experiencing?
Another concern is if the end-user has Excel open for some other unrelated .XLS file.

Obviously I don't want to close other legitimate Excel files...just the excel object my program created.

Tom
Marshal.ReleaseComObject is the right way to free the Excel object (actually for all unmanaged object). the gabage collotor portion is like a hack. You don't have to do the gabage collection portion. It will be freed when the GC runs (you don't have to force it to run). do not need to worry Excel's openned by others (you creat one in your app and get rid of one when you close)
Okay.
Do you fix it already?

i found a solution, if you still have problem, I'll show you how to fix it


James Yu
James:

I haven't had a change to implement the other suggestion:

While (Marshal.ReleaseComObject(m_excelObj) <> 0) ' Repeat until no
more COM references
End While
m_excelObj = nothing
..... repeat for any other Excel objects allocated

' Invoke garbage collector before termination
GC.Collect()
GC.WaitForPendingFinalizers()



What is your alternative solution?


Thanks,

Tom
we should use Marshal.ReleaseComObject, but it is not enough, excel process still there untill you close your report application.
ASKER CERTIFIED SOLUTION
Avatar of jamesyu
jamesyu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dose it works?
Haven't had a chance to test it yet.

I'll let you know!

Thanks,

Tom