Can't destroy Excel Process; tried Just about everything!

[LP]

Hello,

I am trying to use .NET with Excel. I installed Office 2003 and selected
..NET programming suport option, so it installed all those PIA, as MS
sugests. But I can not find a way to destroy Excel process, it still hangs
in the taks manager' Processes as running. I am trying very simple code
(see below), but Excel wont go away, I tried just about anything I could
find on MSDN or by Google. Even calling WIN32 API to destroy Excel. But
NOTHING works, Any help will be appreciated.
'API declaration:
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal
hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As
Long) As Long

' actual VB.NET Code:

Public Const WM_DESTROY = &H2

Public Const WM_USER = &H400

Dim exApplication As New Excel.Application

exApplication.DisplayAlerts = False

exApplication.Caption = "Microsoft Excel (to close)"

Dim wrk As Excel.Workbook

wrk = exApplication.Workbooks.Add()

Dim settings As New SiteSettings

Dim dir As String = settings.getAppSettingValue("TempDirectory")

Dim fileName As String = dir & Session.SessionID & ".xls"

wrk.SaveAs(fileName)

wrk.Close()

NAR(wrk)

wrk = Nothing

exApplication.Quit()

NAR(exApplication)

exApplication = Nothing

' Kill process

Dim hwnd As Long

Dim lngResult As Long

hwnd = FindWindow("XLMAIN", "Microsoft Excel (to close)")

If hwnd <> 0 Then

lngResult = SendMessage(hwnd, WM_USER + 18, 0, 0)

lngResult = SendMessage(hwnd, WM_DESTROY, 0, 0)

End If



Private Sub NAR(ByRef o As Object)

Dim i As Integer = -2

Try

While (i <> 0 And i <> -1)

i = System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

End While

Catch

Finally

o = Nothing

End Try

End Sub

 

[Howard Kaikow]

..NET languages do nopt clean up the same way as VBA does.

In addition to setting variables to Nothing, add the following at the end of
your code to expedite garbage collection.
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

And do not forget to Quit Excel.

 

[LP]

Thanks for the reply. I actually already had .Collect() below. I added:
GC.Collect() GC.WaitForPendingFinalizers() twice, but still Excel Process is
running. Any other ideas?

 

[Scott M.]

Don't forget to call Marshall.ReleaseComObject(yourXLinstanceObjectVariable)
for EACH COM object you've instanced after you quit Excel.

 

[LP]

Yes, I am doing that with NAR() function:

NAR(wrk)


Private Sub NAR(ByRef o As Object)
Dim i As Integer = -2
Try
While (i <> 0 And i <> -1)
i =
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

End While

Catch
Finally
o = Nothing
End Try
End Sub

 

[lgbjr]

LP,

I spent a few weeks dealing with the same thing. I finally discovered that
how Excel objects are created and the order in which Excel objects are
disposed is critical to getting the Excel Process to terminate.

Try the following. the code opens a new instance of excel, adds a workbook,
adds some sheets to the workbook, then adds some data to the first row of
each sheet. Then it saves and exits

Dim r, c, s as Integer

Dim xl As Excel.Application

Dim wbs As Excel.Workbooks

Dim wb As Excel.Workbook

Dim ws As Excel.Worksheet

Dim rng As Excel.Range

Dim st As Excel.Sheets

Dim SName() As String = {"First", "Second", "Third", "Fourth"}

Dim settings As New SiteSettings
Dim dir As String = settings.getAppSettingValue("TempDirectory")
Dim fileName As String = dir & Session.SessionID & ".xls"



r = 1

xl = New Excel.Application

xl.Visible = False

xl.DisplayAlerts = False

xl.SheetsInNewWorkbook = 1

wbs = xl.Workbooks

wb = wbs.Add

st = wb.Sheets

ws = wb.ActiveSheet

ws.Name = "Summary"

For s = 0 To Sname.Length

st.Add()

ws = wb.ActiveSheet

ws.Name = Sname(s)

For c = 1 To 10

ws.Cells.Item(r, c) = ¡°X¡±

Next

Next



xl.ActiveWorkbook.SaveAs(filename)

xl.ActiveWorkbook.Saved = True

NAR(rng)

NAR(st)

NAR(ws)

xl.ActiveWorkbook.Close()

NAR(wb)

NAR(wbs)

xl.Quit()

NAR(xl)

GC.Collect()



Private Sub NAR(ByVal o As Object)

Dim i, j As Integer

Try

For i = 1 To
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

j =
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

Next

Catch

Finally

o = Nothing

End Try

End Sub


Let me know how it goes!

HTH
Lee

 

[LP]

Thanks lgbjr.

I tried your code, but unfortunatly it's still not working. I even tried
enchancing your code; destroying selected range and , looping through
worksheets and workbooks in xl application and destroying them and still
doesnt work. It seems to me it's somekind of bug in Excel.Interop, I really
want to hear from MS people about this. MSDN library doesn't seem to have
any usefull info about this issue.

 

[lgbjr]

Hmm,

For me, it does work. And believe me, I tried many different iteration over
the course of a few weeks trying to get rid of the Excel process. Hopefully
MS will have some additional info, other than the MSDN article with the NAR
function.

Actually, I haven't worked on that part of my code in some time. I'll have
to run it and verify that it does actually work. I have it marked as
completed on my task list. But, maybe, that was done too soon (and I just
got tired of working on it )

I'll let you know.

cheers
Lee

 

[Scott M.]

Something tells me that it's the way you've got the NAR method setup that is
the problem.

Also, you should be setting your object variable to nothing before you call
Marsahall.ReleaseComObject:

"The ReleaseComObject method decrements the reference count of a runtime
callable wrapper. When the reference count reached zero, the runtime
releases all its references on the unmanaged COM object, and throws a
System.NullReferenceException if you attempt to use the object further."

Have you tried just using ReleaseCom object directly in your code rather
than trying to create a "kill all" method?

 

[Fredrik Wahlgren]

Is the Google Desktop installed? For some weird reason, its presence
prevents Excel to exit.

/Fredrik

 

[Lee Gillie]

I've never had much luck getting OFFICE apps to run down cleanly and
consistently.

My approach is to
1) scan processes prior to CreateObject.
(accumulate PID integer in an ArrayList)
2) CreateObject
3) scan processes again, see what is new
(verify PID integer using ArrayList.Contains)

The time frame between 1 and 3 must be as short as possible.
Sanity check the process name if the above makes you uncomfortable.

Then once you have tried every trick you can think of to tell EXCEL to
shut down in a nice way, then you can see if/when the process has exited
with the .NET "Process" object. Give it a moment or two. If it still has
not gone away, then kill it!

To me, there seems to be too much voodoo in getting office automation to
avoid leaving orphaned processes out there.

HTH - Lee

 

[LP]

Thanks Lee,

My approach is to
1) scan processes prior to CreateObject.
(accumulate PID integer in an ArrayList)
2) CreateObject
3) scan processes again, see what is new
(verify PID integer using ArrayList.Contains)

What do you mean scan process, could elaborate on this? Any code snippets
would be nice.

I did get it to work to certain point; just to open a file, add a workbook
and "save as" it. From everyone's comments on this thread and from my own
painful experience, I did figure out the pattern behind cleaning up excel.
Basically ReleaseComObject everything that excel object "touches" even if
it's not obvious. For example my code never directly referenced worksheets
collection, but I did referenced workbooks, but I still had to get reference
to worksheets and ReleaseComObject it to kill excel process. I guess
underneath the covers workbooks object creates worksheets reference.

So I got that part to work, but when I started actually writing data to
excel, I quickly realized it's not going to be worth my time. Because I
literally had to Release every cell I referenced. And even then I could miss
something not so obvious.

So I decided to do all excel related work in classic asp. I wasn't too happy
about going back to old scripting world, but it worked out beautifully for
me. It took me a couple hours to finish export to excel functionality when I
strugeled in .NET for 4 days and still couldn't get it to work flawlesly.
I really doubt it makes any sense to use Excel in .NET, because you're
spending more time writing code to quit excel gracefully than actually
writing business rules code which really matters. I will be making a few
more posts about other Excel and ASP.NET options . I would appreciate any
further comments.

Thank you

 

[Scott M.]

You know, you could write a VB 6.0 component that does all this Excel stuff
and then call that from .NET. You would only have to worry about
ReleaseComObject on the component, not all the Excel objects.

There really isn't any great benefit of using Excel directly in .NET as you
are still using COM InterOp. Making an component that talks to Excel for
you still uses COM InterOp, but make the .NET to COM part a bit cleaner.

 

[LP]

Yes, I tired that, but I got a permision error, I didn't have time to deal
with that. That's a topic for another post anyway.

 

[Guest]

I noticed that all of the responces are in VB!

I just tried this in C#

I seemed to have much better Luck!

TEH

 

[Guest]

I was just about to do a post on this subject having gotten absolutely
nothing on an unmonitored forum. Thanks to all who have posted before. Let's
see if my clout as an MSDN subscriber gets the promised response from MS in
24 hours

MS is pushing the Office / .net interface. This cleanup is absolutely
something that has to work in a straight forward manner for their success in
this regard.

What is the secret of cleaning up Excel after having declared only the
application and a workbook? I.E. Those are the only explicit excel objects in
my project where I'm observing the problem. They are declared early. Here is
a very rough outline

dim xl as new microsoft....excel.application
dim wb as new microsoft....excel.workbook
rem do some stuff with the worksheets collection of the workbook (like read
it into the porogram)

Rem now what??????
rem hmmm ...
wk = nothing
xl.quit
xl = nothing
NAR(WK)
NAR(XL)
' now GC to beat the band
.....

I have not tried what is outlined above and won't because of the experiences
you fine folk have had. MS how about some help here.

Regards,
Al Christoph
Senior Consultant and Proprietor
Three Bears Software, LLC
just right software @ just right prices @3bears.biz

 

[Carlos J. Quintero [.NET MVP]]

Be very careful with implicit object creation such us WorkBooks in the code

oBook = oExcel.Workbooks.Add()

See:

Office application does not quit after automation from Visual Studio .NET
client
http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

--

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio .NET, VB6, VB5 and VBA
You can code, design and document much faster.
Free resources for add-in developers:
http://www.mztools.com