?
Solved

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

Posted on 2003-03-20
12
Medium Priority
?
1,026 Views
Last Modified: 2012-06-21
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
Comment
Question by:Tom Knowlton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 8175888
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
 
LVL 10

Expert Comment

by:GoodJun
ID: 8176135
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8176502
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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8176510
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
 
LVL 10

Expert Comment

by:GoodJun
ID: 8176911
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8176931
Okay.
0
 
LVL 2

Expert Comment

by:jamesyu
ID: 8184272
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
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8184313
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
 
LVL 2

Expert Comment

by:jamesyu
ID: 8184346
we should use Marshal.ReleaseComObject, but it is not enough, excel process still there untill you close your report application.
0
 
LVL 2

Accepted Solution

by:
jamesyu earned 500 total points
ID: 8184807
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
 
LVL 2

Expert Comment

by:jamesyu
ID: 8196923
Dose it works?
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 8197029
Haven't had a chance to test it yet.

I'll let you know!

Thanks,

Tom
0

Featured Post

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question