cannot release excel object

[Raymond Chiu]

Dear all,

I have written the following code in the button click event but find that
excel session object always remain in the server as seen from taskmanager.
Do you know why?
------------------------------------------------------------------------------
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet

objExcel.Workbooks.Open(Page.MapPath("reports\Cost.xls"))

objExcel.DisplayAlerts = False

objSheet = objExcel.ActiveSheet

Dim sFileName As String = InvNo & "_" & Format(Date.Now(), "yyyyMMddHHmm") &
".xls"

objSheet.SaveAs(Page.MapPath("..\out\" & sFileName))

objExcel.Workbooks.Close()
objExcel.Quit()

objSheet = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)

Page.Response.Redirect("dl.aspx?filename=" & sFileName)

 

[Armin Zingler]

Dear all,

I have written the following code in the button click event but find
that excel session object always remain in the server as seen from
taskmanager. Do you know why?
------------------------------------------------------------------------------
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet

objExcel.Workbooks.Open(Page.MapPath("reports\Cost.xls"))

objExcel.DisplayAlerts = False

objSheet = objExcel.ActiveSheet

Dim sFileName As String = InvNo & "_" & Format(Date.Now(),
"yyyyMMddHHmm") & ".xls"

objSheet.SaveAs(Page.MapPath("..\out\" & sFileName))

objExcel.Workbooks.Close()
objExcel.Quit()

objSheet = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)

Page.Response.Redirect("dl.aspx?filename=" & sFileName)

Just an attempt: Apply ReleaseComObject also with objsheet and
objexcel.workbooks.
(the _only_ reason why I don't use DAO Interop BTW)



Armin

 

[cfps.Christian]

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)

you can stick with the code but do this

While
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel) > 0
Loop

ReleaseComObject returns an integer, when that integer = 0 then the
object is released. FinalReleaseComObject is supposed do do that for
you, but it also returns an integer. Either way I'd probably put both
in a While loop.

 

[Herfried K. Wagner [MVP]]

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)

you can stick with the code but do this

While
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel) > 0
Loop

Well, if you only create one reference to the COM object, there is no need
to call 'ReleaseComObject' more than once.

Some tips can be found here:

Office application does not quit after automation from Visual Studio .NET
client
<URL:http://support.microsoft.com/?scid=kb;EN-US;317109>

 

[Raymond Chiu]

Dear all,

I have put the coding as follows. Hope it can help.
I tried on my Windows XP Professional development computer and no problems.
But put on windows 2000 server in production with all updates. The problem
exists. Is it related to the windows or office problem???
It accumulate a lot of excel sessions object in the task manager everyday
and I need to kill them several times every day.

I write the following code in the button
event. -----------------------------------------------
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet

objExcel.Workbooks.Open(Page.MapPath("reports\Cost.xls"))

objExcel.DisplayAlerts = False

objSheet = objExcel.ActiveSheet

Dim sFileName As String = InvNo & "_" & Format(Date.Now(), "yyyyMMddHHmm") &
".xls"

objSheet.SaveAs(Page.MapPath("..\out\" & sFileName))

objExcel.Workbooks.Close()
objExcel.Quit()

objSheet = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)

objExcel=nothing

Page.Response.Redirect("dl.aspx?filename=" & sFileName)

-------------------------------------------------------------------------------------------------------

I have the following code in the dl.aspx. Do it affect the excel session in
the taskmanager?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Handles Me.Load

If Me.User.Identity.IsAuthenticated = False Then
FormsAuthentication.RedirectToLoginPage()
Exit Sub
End If

Dim sFileName As String =
Page.Request.QueryString("filename").Trim()
Dim sFilePath As String

Response.Clear()
Response.ContentType = "application/x-unknown"
Response.AddHeader("Content-Disposition", "attachment; filename=" &
sFileName)

If Page.Request.QueryString("filepath") Is Nothing Then
Response.BinaryWrite(File.ReadAllBytes(Page.MapPath("..\out\" &
sFileName)))
Else
sFilePath = Page.Request.QueryString("filepath").ToString.Trim()
Response.BinaryWrite(File.ReadAllBytes(Page.MapPath("..\" &
sFilePath & "\" & sFileName)))
End If

Response.End()


End Sub

 

[Bill McCarthy]

Hi,

Without looking at the interop library, typically the way we would address
this on a server is to use GetObject, and try to re-use the existing
application or instantiate one if GetObject fails. That still has issues
with high loads, as does using Excel on a server.

Personally, I'd be looking at whether or not you can use Excel's XML file
formats, and just write out the XML using VB9's XML literals etc. It'd be a
lot cleaner and scale a lot better.

Dear all,

I have put the coding as follows. Hope it can help.
I tried on my Windows XP Professional development computer and no
problems.
But put on windows 2000 server in production with all updates. The problem
exists. Is it related to the windows or office problem???
It accumulate a lot of excel sessions object in the task manager everyday
and I need to kill them several times every day.

I write the following code in the button
event. -----------------------------------------------
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet

objExcel.Workbooks.Open(Page.MapPath("reports\Cost.xls"))

objExcel.DisplayAlerts = False

objSheet = objExcel.ActiveSheet

Dim sFileName As String = InvNo & "_" & Format(Date.Now(), "yyyyMMddHHmm")
& ".xls"

objSheet.SaveAs(Page.MapPath("..\out\" & sFileName))

objExcel.Workbooks.Close()
objExcel.Quit()

objSheet = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel)

objExcel=nothing

Page.Response.Redirect("dl.aspx?filename=" & sFileName)

-------------------------------------------------------------------------------------------------------

I have the following code in the dl.aspx. Do it affect the excel session
in the taskmanager?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

If Me.User.Identity.IsAuthenticated = False Then
FormsAuthentication.RedirectToLoginPage()
Exit Sub
End If

Dim sFileName As String =
Page.Request.QueryString("filename").Trim()
Dim sFilePath As String

Response.Clear()
Response.ContentType = "application/x-unknown"
Response.AddHeader("Content-Disposition", "attachment; filename=" &
sFileName)

If Page.Request.QueryString("filepath") Is Nothing Then
Response.BinaryWrite(File.ReadAllBytes(Page.MapPath("..\out\" &
sFileName)))
Else
sFilePath =
Page.Request.QueryString("filepath").ToString.Trim()
Response.BinaryWrite(File.ReadAllBytes(Page.MapPath("..\" &
sFilePath & "\" & sFileName)))
End If

Response.End()


End Sub

 

[Raymond Chiu]

Dear Bill Mc Carthy,

Your ideas is great.

Thanks,

Hi,

Without looking at the interop library, typically the way we would address
this on a server is to use GetObject, and try to re-use the existing
application or instantiate one if GetObject fails. That still has issues
with high loads, as does using Excel on a server.

Personally, I'd be looking at whether or not you can use Excel's XML file
formats, and just write out the XML using VB9's XML literals etc. It'd be
a lot cleaner and scale a lot better.

 

[Claes Bergefall]

See inline for specifics, but basically for any property or method that
returns any type of Excel related object, you'll need to keep a reference to
that object and then call ReleaseComObject on it. You can't chain properties
together like object.property1.property2.property3 because each property
call returns a COM object that needs to be released.

See also the link that Herfried posted

/claes

Dear all,

I have written the following code in the button click event but find that
excel session object always remain in the server as seen from taskmanager.
Do you know why?
------------------------------------------------------------------------------
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet

objExcel.Workbooks.Open(Page.MapPath("reports\Cost.xls"))

objExcel.Workbooks returns a COM object that needs to be released with a
call to ReleaseComObject.

objExcel.DisplayAlerts = False

objSheet = objExcel.ActiveSheet

Dim sFileName As String = InvNo & "_" & Format(Date.Now(), "yyyyMMddHHmm")
& ".xls"

objSheet.SaveAs(Page.MapPath("..\out\" & sFileName))

objExcel.Workbooks.Close()

Same as above; objExcel.Workbooks returns a COM object that needs to be
released with a call to ReleaseComObject.

objExcel.Quit()

objSheet = Nothing

objSheet is a COM object. You need to pass it to ReleaseComObject to release
it