Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

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
0
Tom Knowlton
Asked:
Tom Knowlton
  • 5
  • 4
  • 2
  • +1
1 Solution
 
iboutchkineCommented:
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()
0
 
GoodJunCommented:
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.
0
 
Tom KnowltonWeb developerAuthor Commented:
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?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Tom KnowltonWeb developerAuthor Commented:
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
0
 
GoodJunCommented:
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)
0
 
Tom KnowltonWeb developerAuthor Commented:
Okay.
0
 
jamesyuCommented:
Do you fix it already?

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


James Yu
0
 
Tom KnowltonWeb developerAuthor Commented:
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
0
 
jamesyuCommented:
we should use Marshal.ReleaseComObject, but it is not enough, excel process still there untill you close your report application.
0
 
jamesyuCommented:
Tom,

each time you call New Excel.Application(), you get a new excel process. Actually, we only need one excel process to create all reports.

I found that in your class definition, you already called New Excel.Application():
Public Class InvReports
   Private ds As New DataSet()
   Private cmdSQL As New SqlCommand()
   Private con As New SqlConnection(strConnection)
   Private da As New SqlDataAdapter()
   Private excelApp = New Excel.Application()
   Private excelWorksheet As Excel.Worksheet
   Private excelBook As Excel.Workbook = excelApp.Workbooks.Add

I checked your class code, you do not need this line:
   Private excelBook As Excel.Workbook = excelApp.Workbooks.Add
my suggestion is change it to
   Private excelBook As Excel.Workbook
   
I found that your other functions need to generate Excel file too. You have two choice:
1. keep this line in the calss definition
    Private excelApp = New Excel.Application()
   AND take out all other "New Excel.Application()" in your file.
   
   so, there will be only one Excel process while your program runing.
   
2. change this line in the calss definition
    Private excelApp = New Excel.Application()
   to
    Private excelApp As Excel.Application
    And on the begining of each function you need to create excel file add the following line:
   
    excelApp = New Excel.Application()
   
    and at the end of each function all NAR to clean-up and release EXCEL COM objects
   


I modified the code for you. And clean up EXCEL process created by this function
   
----------------------begin code  
' this NAR used to clean up Com Object References
Private Sub NAR(ByVal o As Object)
    Try
        While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) <> 0)
        End While
    Catch
    Finally
        o = Nothing
    End Try
End Sub

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

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

           ' for your request 2
           book = excelApp.Workbooks.Add()
           
           excelWorksheet = book.Worksheets(1)
           fnMonthlyReportSheet(book.Worksheets(1), drAEList)
           book.Worksheets("Sheet2").Delete()
           book.Worksheets("Sheet3").Delete()
           book.SaveAs(filename & "_" & fnCleanString(drAEList(1)) & ".xls")
           NAR(excelWorksheet)
           book.Close()  ' I think you what to close it
           NAR(book)
       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????
       NAR(book)

       excelApp.Quit()
       NAR(excelApp)
       
       GC.Collect()
       GC.WaitForPendingFinalizers()
   End Sub
   
   
   
0
 
jamesyuCommented:
Dose it works?
0
 
Tom KnowltonWeb developerAuthor Commented:
Haven't had a chance to test it yet.

I'll let you know!

Thanks,

Tom
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now