[lnkForumImage]
TotalShareware - Download Free Software

Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

GS

6/18/2007 10:04:00 PM

I am having trouble with closing excel application completely when done with
an excel application automation. I have to exit the vb.net application
before the excel is closed and if I have use the excel multiple time in the
application, I see multiple instance of excel in the task manager

simplied code

Dim oExcel As Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Excel.Worksheet

Try
ModuleXlApp.getExcelApp(oExcel, oBook, oSheet, False) ' not
visible
Catch e As NullReferenceException
setStatus("NullReference Exception in arguments calling
ModuleXlApp.getExcelApp - ignored")

'Paste the data (html tables) in the clipboard

Dim oRange As Microsoft.Office.Interop.Excel.Range
Dim oUsedRange As Microsoft.Office.Interop.Excel.Range

oRange = oSheet.Range("A1")
oSheet.Paste()

oUsedRange = oSheet.UsedRange
oUsedRange.Replace(" ", "")
....... othe non excel processing


End Try

oRange.Clear()
ModuleXlApp.RlseIComObj(oRange)
oUsedRange.Clear()
oBook.ConflictResolution =
Excel.XlSaveConflictResolution.xlLocalSessionChanges

ModuleXlApp.RlseIComObj(oUsedRange)

ModuleXlApp.RelseExcelApp(oExcel, oBook, oSheet)

----------
In ModuleXlApp, I have:
Public Sub getExcelApp(ByRef oExcel As
Microsoft.Office.Interop.Excel.Application, _
ByRef oBook As Microsoft.Office.Interop.Excel.Workbook, _
ByRef oSheet As Microsoft.Office.Interop.Excel.Worksheet, _
ByVal bVisible As Boolean _
)
oExcel = New Microsoft.Office.Interop.Excel.Application()

oBook = oExcel.Workbooks.Add
oSheet = DirectCast(oBook.ActiveSheet,
Microsoft.Office.Interop.Excel.Worksheet)

End Sub

Public Sub RelseExcelApp(ByRef oExcel As
Microsoft.Office.Interop.Excel.Application, _
ByRef oBook As Microsoft.Office.Interop.Excel.Workbook, _
ByRef oSheet As Microsoft.Office.Interop.Excel.Worksheet _
)
oSheet.UsedRange.Clear()
oBook.Saved = True
Dim oWbs As Excel.Workbooks
oWbs = oExcel.Workbooks

oBook.Saved = True ' not needed if xlApp_WorkbookBeforeClose is in
place

oExcel.PromptForSummaryInfo = False
oExcel.Application.DisplayAlerts = False
RlseIComObj(oBook)

oWbs.Close()

oExcel.Quit()

' must relese in this order after excel quit because of the use of
oWbs
RlseIComObj(oSheet)
RlseIComObj(oWbs)
RlseIComObj(oExcel)
End Sub

Public Sub RlseIComObj(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally
o = Nothing
End Try
End Sub


what have I missed in RelseExcelApp that would cause Excel still continue
to run ?


3 Answers

GS

6/19/2007 4:52:00 AM

0

aha, apparently they do get released eventually.
in my test app with pasting and some minor replace operation,, I get up to 3
instance
and after the fourth use of similar test in the test application session,
the GC must be automatically triggered to release all Excel instances. I
have a ttl physical RAM of 2GB, and the threshold is about 1.3 or 1.4 GB

"GS" <gsmsnews.microsoft.comGS@msnews.Nomail.com> wrote in message
news:ONxCRSfsHHA.1672@TK2MSFTNGP06.phx.gbl...
> I am having trouble with closing excel application completely when done
with
> an excel application automation. I have to exit the vb.net application
> before the excel is closed and if I have use the excel multiple time in
the
> application, I see multiple instance of excel in the task manager
>
> simplied code
>
> Dim oExcel As Microsoft.Office.Interop.Excel.Application
> Dim oBook As Microsoft.Office.Interop.Excel.Workbook
> Dim oSheet As Excel.Worksheet
>
> Try
> ModuleXlApp.getExcelApp(oExcel, oBook, oSheet, False) ' not
> visible
> Catch e As NullReferenceException
> setStatus("NullReference Exception in arguments calling
> ModuleXlApp.getExcelApp - ignored")
>
> 'Paste the data (html tables) in the clipboard
>
> Dim oRange As Microsoft.Office.Interop.Excel.Range
> Dim oUsedRange As Microsoft.Office.Interop.Excel.Range
>
> oRange = oSheet.Range("A1")
> oSheet.Paste()
>
> oUsedRange = oSheet.UsedRange
> oUsedRange.Replace(" ", "")
> ....... othe non excel processing
>
>
> End Try
>
> oRange.Clear()
> ModuleXlApp.RlseIComObj(oRange)
> oUsedRange.Clear()
> oBook.ConflictResolution =
> Excel.XlSaveConflictResolution.xlLocalSessionChanges
>
> ModuleXlApp.RlseIComObj(oUsedRange)
>
> ModuleXlApp.RelseExcelApp(oExcel, oBook, oSheet)
>
> ----------
> In ModuleXlApp, I have:
> Public Sub getExcelApp(ByRef oExcel As
> Microsoft.Office.Interop.Excel.Application, _
> ByRef oBook As Microsoft.Office.Interop.Excel.Workbook, _
> ByRef oSheet As Microsoft.Office.Interop.Excel.Worksheet, _
> ByVal bVisible As Boolean _
> )
> oExcel = New Microsoft.Office.Interop.Excel.Application()
>
> oBook = oExcel.Workbooks.Add
> oSheet = DirectCast(oBook.ActiveSheet,
> Microsoft.Office.Interop.Excel.Worksheet)
>
> End Sub
>
> Public Sub RelseExcelApp(ByRef oExcel As
> Microsoft.Office.Interop.Excel.Application, _
> ByRef oBook As Microsoft.Office.Interop.Excel.Workbook, _
> ByRef oSheet As Microsoft.Office.Interop.Excel.Worksheet _
> )
> oSheet.UsedRange.Clear()
> oBook.Saved = True
> Dim oWbs As Excel.Workbooks
> oWbs = oExcel.Workbooks
>
> oBook.Saved = True ' not needed if xlApp_WorkbookBeforeClose is
in
> place
>
> oExcel.PromptForSummaryInfo = False
> oExcel.Application.DisplayAlerts = False
> RlseIComObj(oBook)
>
> oWbs.Close()
>
> oExcel.Quit()
>
> ' must relese in this order after excel quit because of the use of
> oWbs
> RlseIComObj(oSheet)
> RlseIComObj(oWbs)
> RlseIComObj(oExcel)
> End Sub
>
> Public Sub RlseIComObj(ByVal o As Object)
> Try
> System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
> Catch
> Finally
> o = Nothing
> End Try
> End Sub
>
>
> what have I missed in RelseExcelApp that would cause Excel still
continue
> to run ?
>
>


SvenC

6/19/2007 5:50:00 AM

0

Hi,

GS wrote:
> I am having trouble with closing excel application completely when
> done with an excel application automation. I have to exit the vb.net
> application before the excel is closed and if I have use the excel
> multiple time in the application, I see multiple instance of excel in
> the task manager
>
> simplied code
>
> Dim oExcel As Microsoft.Office.Interop.Excel.Application
> Dim oBook As Microsoft.Office.Interop.Excel.Workbook
> Dim oSheet As Excel.Worksheet

All COM instances you assign to the above variables should be passed to
System.Runtime.InteropServices.Marshal.ReleaseComObject, that will
internally call Release on the COM interface.

So do call

System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

before you assign a new value or Nothing to oExcel.

Otherwise only a GC run will release the interface pointers.

--
SvenC

Lee Gillie

8/31/2007 8:35:00 PM

0

One confounding issue I have with .NET is that objects are not always
destructed. When you release instances they are destructed during GC. If
a GC has not occurred at image run down then the memory block is
released without rundown. Protect yourself by always closing or
disposing when available. But of course COM object interop is a little
different animal. I use this for out of process servers to provide
myself with a dispose method:

HTH - Lee
Imports System.Diagnostics

''' <summary>
''' This class helps manage out of process COM servers.
''' Create one new instance of this class for each object you want to
create.
''' Use the CreateObject method to instance your out of process server.
''' When done, do your best to shut down the COM object.
''' Finally call dispose on this object.
''' You can simply let this object go out of scope, but GC runs deferred
and sometimes is never run.
'''
''' Lee Gillie, CCP
''' Online Data Processing, Inc.
''' Spokane, WA
'''
''' </summary>
''' <remarks></remarks>
Public Class OutOfProcessComServer
Implements IDisposable
' THERE ARE KNOWN BUGS AUTOMATICE OFFICE PROGRAMS
' such as WORD, EXCEL, and so on. You PROPERLY
' shut down the component, but the process continues
' to live!
Friend m_Object As Object
Friend m_ProcessID As Integer
Friend m_ServerName As String
Friend m_ProgID As String
Friend m_ProcessName As String

Public Sub New()
m_Object = Nothing
m_ProcessID = -1
m_ServerName = ""
m_ProgID = ""
End Sub

Public Sub New(ByVal ProgID As String)
CreateObject(ProgID)
End Sub

Public Sub New(ByVal ProgID As String, ByVal ServerName As String)
CreateObject(ProgID, ServerName)
End Sub

Public Function CreateObject(ByVal ProgID As String) As Object
Return CreateObject(ProgID, "")
End Function

Public Function CreateObject(ByVal ProgID As String, ByVal
ServerName As String) As Object
If m_Object Is Nothing Then
' If the COM object is NOT already known, then create it
m_ProgID = ProgID
m_ServerName = ServerName
' For any given COM object, we MUST know the name of
' the program it runs for the out-of-process server.
' No way around this I know of except to hard code it.
' Maybe it could be queried via INTEROP ?
Select Case m_ProgID.Trim.ToUpper
Case "WORD.APPLICATION"
m_ProcessName = "WINWORD"
Case "EXCEL.APPLICATION"
m_ProcessName = "EXCEL"
Case Else
Throw New Exception("Process Name for OUT OF
PROCESS SERVER " & ProgID & " is not known.")
End Select
Dim PIDList As New ArrayList
Dim Processes() As Process
If m_ServerName.Length > 0 Then
' Just prior to creating the out of process server,
' find the processes for the given name.
Processes = Process.GetProcessesByName(m_ProcessName,
m_ServerName)
' Now instance the COM object which starts the service
process
m_Object = Microsoft.VisualBasic.CreateObject(ProgID,
m_ServerName)
Else
' Just prior to creating the out of process server,
' find the processes for the given name.
Processes = Process.GetProcessesByName(m_ProcessName)
' Now instance the COM object which starts the service
process
m_Object = Microsoft.VisualBasic.CreateObject(ProgID)
End If
' Now make a list process ids of all the pre-existing processes
Dim proc As Process
For Each proc In Processes
PIDList.Add(proc.Id)
Next
' And again, survey by the given name
If m_ServerName.Length > 0 Then
Processes = Process.GetProcessesByName(m_ProcessName,
m_ServerName)
Else
Processes = Process.GetProcessesByName(m_ProcessName)
End If
' Now any process id not in our pre-existing list is the
new one
For Each proc In Processes
If Not PIDList.Contains(proc.Id) Then
' We found the new one we just created !
Me.m_ProcessID = proc.Id
Exit For
End If
Next
End If
' Return our object to caller
Return m_Object
End Function


Public Sub Dispose() Implements System.IDisposable.Dispose
' Last ditch effort to release the server after the
' user has closed all instances.
If m_Object IsNot Nothing Then
Try

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Object)
m_Object = Nothing
Catch ex As Exception
Debug.WriteLine("WARNING: Unable to ReleaseComObject: "
& ex.Message)
End Try
End If

If m_ProcessID = -1 Then
Debug.WriteLine("WARNING: Out Of Process Server ProcessID
was never discovered.")
Else
Dim proc As Process
Try
If m_ServerName.Length > 0 Then
proc = Process.GetProcessById(m_ProcessID,
m_ServerName)
Else
proc = Process.GetProcessById(m_ProcessID)
End If
Catch ex As ArgumentException
' Server shut down as it should have
Return
End Try

' Server did NOT shut down as it should have. It needs our
help.
' This routine WONT return until the process has fully run
down.
' Or 10 seconds, which ever comes first.
Debug.WriteLine("Saftey shut down of out of process COM
server " & m_ProgID)
proc.Kill()
If proc.WaitForExit(10000) Then
' Process exited within the 10 seconds we are willing
to wait.
Else
If m_ServerName.Length > 0 Then
Debug.WriteLine("WARNING: Out of process COM server
" & m_ProgID & ", local ProcessID " & m_ProcessID.ToString & " did not
exit in 10 seconds.")
Else
Debug.WriteLine("WARNING: Out of process COM server
" & m_ProgID & ", " & m_ServerName & " ProcessID " &
m_ProcessID.ToString & " did not exit in 10 seconds.")
End If
End If
End If
m_ProcessID = -1
m_ServerName = ""
m_ProgID = ""
End Sub

End Class