Tom Knowlton
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.Displ ayAlerts = False
excelApp.DisplayAlerts = False
'Me.excelBook.Application. DisplayAle rts = False
For Each drAEList In dsAEList.Tables(0).Rows
' for your request 1
fnMonthlyReportSheet(excel Book.Works heets.Add( after:=exc elBook.Wor ksheets(ex celBook.Wo rksheets.C ount)), 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("Shee t1").Delet e()
excelBook.Worksheets("Shee t2").Delet e()
excelBook.Worksheets("Shee t3").Delet e()
excelBook.SaveAs(filename & ".xls")
excelBook.Close() ' do you want to close it????
excelApp.Application.Quit( )
excelApp = Nothing
End Sub
Here is the code:
Public Sub fnMonthlyReport()
Dim filename
filename = "c:\MonthlyReport\Report_"
Dim excelApp = New Excel.Application()
Dim excelWorksheet As Excel.Worksheet
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
excelWorksheet = CType(excelBook.Worksheets
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.Displ
excelApp.DisplayAlerts = False
'Me.excelBook.Application.
For Each drAEList In dsAEList.Tables(0).Rows
' for your request 1
fnMonthlyReportSheet(excel
' for your request 2
book = excelApp.Workbooks.Add()
fnMonthlyReportSheet(book.
book.Worksheets("Sheet2").
book.Worksheets("Sheet3").
book.SaveAs(filename & "_" & fnCleanString(drAEList(1))
book.Close() ' I think you what to close it
Next 'Next AE
excelBook.Worksheets("Shee
excelBook.Worksheets("Shee
excelBook.Worksheets("Shee
excelBook.SaveAs(filename & ".xls")
excelBook.Close() ' do you want to close it????
excelApp.Application.Quit(
excelApp = Nothing
End Sub
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.
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?
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?
ASKER
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
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)
ASKER
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
i found a solution, if you still have problem, I'll show you how to fix it
James Yu
ASKER
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.WaitForPendingFinalizer s()
What is your alternative solution?
Thanks,
Tom
I haven't had a change to implement the other suggestion:
While (Marshal.ReleaseComObject(
more COM references
End While
m_excelObj = nothing
..... repeat for any other Excel objects allocated
' Invoke garbage collector before termination
GC.Collect()
GC.WaitForPendingFinalizer
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dose it works?
ASKER
Haven't had a chance to test it yet.
I'll let you know!
Thanks,
Tom
I'll let you know!
Thanks,
Tom
objects you instantiated with Marshal.ReleaseComObject and then invoking the
..NET garbage collecter before exiting.
Something like:
While (Marshal.ReleaseComObject(
more COM references
End While
m_excelObj = nothing
..... repeat for any other Excel objects allocated
' Invoke garbage collector before termination
GC.Collect()
GC.WaitForPendingFinalizer