Excel Won't DIE..please help!

[melegant]

I can't take it..

I have found countless messages on countless boards, even an article on microsofts site..however I CANNOT MAKE IT WORK.

 

Here is the deal, i have a very heft sub that builds an excel spreadsheet from a template.

 

everything works dandy, however EXCEL.EXE WON'T RELEASE..even though the rest works fine.

 

however, there is one and only one time i can make the dam excel release..

 

Public Sub excelimp()

       Dim MyExcel As New Excel.Application()

       MyExcel.Quit()
       MyExcel = Nothing
       NARS(MyExcel)
       GC.Collect()
       GC.WaitForPendingFinalizers()
       Exit Sub
'AT THIS POINT, IT RELEASES FINE

       Dim mc As New iContract(MSa)

       Dim contype As Int32
       Dim a As Int16
       Dim y As Int16
       Dim z As Int16

       mc.SQLCmdText = "Select notes,mfgr,model,descr,qty,hrs,list,sell,unitcost,extcost,mrkup,disclvl,ptype,idnum from msales where sanum = " & MSa & " AND jobnum = " & cmboJob.Text & " AND altnum = " & cmboAlt.Text
       Dim SQLdap As New SqlClient.SqlDataAdapter(mc.SQLCmdText, mc.SQLConnection)
       Dim SQLtbl As New DataTable()
'etc

 

Now, another ex., where it dosn't work

 

Public Sub excelimp()

       Dim MyExcel As New Excel.Application()
       Dim mc As New iContract(MSa)

       Dim contype As Int32
       Dim a As Int16
       Dim y As Int16
       Dim z As Int16

       mc.SQLCmdText = "Select notes,mfgr,model,descr,qty,hrs,list,sell,unitcost,extcost,mrkup,disclvl,ptype,idnum from msales where sanum = " & MSa & " AND jobnum = " & cmboJob.Text & " AND altnum = " & cmboAlt.Text
       Dim SQLdap As New SqlClient.SqlDataAdapter(mc.SQLCmdText, mc.SQLConnection)
       Dim SQLtbl As New DataTable()
       SQLdap.Fill(SQLtbl)
       contype = mc.ContractType
       With mc
           .ProcedureName = "sp_GetFolderFile"
           .ClearParameter()
           .ClearParameterVal()
           .sp_DocAll()
           .AddParameterVal(MSa.ToString)
           .AddParameterVal(cmboJob.Text)
           .AddParameterVal(cmboAlt.Text)
           .AddParameterVal(cmboRev.Text)
           .SQLConnection.Open()
           .InitProcedure()
           .SQLReader = Nothing
           .SQLReader = .SQLCmd.ExecuteReader(CommandBehavior.Default)
           .SQLReader.Read()
           FileCopy(ExcelTemplatePath, FullPath & Convert.ToString(.SQLReader.Item(0)) & "\" & Convert.ToString(.SQLReader.Item(1)) & ".xls")
           'test = FullPath & Convert.ToString(.SQLReader.Item(0)) & "\" & Convert.ToString(.SQLReader.Item(1)) & ".xls"
           Dim oWorkBooks As Excel.Workbooks = MyExcel.Workbooks
           Dim OWorkBook As Excel.Workbook = oWorkBooks.Add
           Dim oSheet As Excel.Worksheet = MyExcel.ActiveSheet

           MyExcel.Workbooks.Open(FullPath & Convert.ToString(.SQLReader.Item(0)) & "\" & Convert.ToString(.SQLReader.Item(1)) & ".xls")

           y = 4
           z = 4


           NARS(oSheet)
           OWorkBook.Close()
           NARS(OWorkBook)
           NARS(oWorkBooks)
           MyExcel.Quit()
           MyExcel = Nothing
           NARS(MyExcel)
           GC.Collect()
           GC.WaitForPendingFinalizers()
           Exit Sub

'IT WILL NOT RELEASE HERE..WHY! EVEN IF I SKIP ALL OTHER CODE AFTER I INIT THE EXCEL OBJECTS, IT WON"T GO! 

 

not only that, if i just call the new excel.application line, then skip down to where it kills the references to the objects, it will not release..EXCEL.EXE STAYS IN THE TASK MANAGER UNDER PROCESSES....

 

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q317109

 

I tried the above link ..DAM somoene help me out of my misery!

 

THANKS!

 

[/code]

[APaule]

It's all clear that your code doesn't work properly.

 

            MyExcel = Nothing
NARS(MyExcel)

You set MyExcel to nothing before passing it to NARS.:D

[Derek Stone]

Take the following line for example:

MyExcel.Workbooks.Open(...)

Instead, call the method like so:

Dim oWorkbooks As Excel.Workbooks = MyExcel.Workbooks
oWorkbooks.Open(...)

This coding style is required as a result of a weird inconsistency (dare I call it that?) in the garbage collector. Each COM object needs to have a managed object explicitly associated with it before one should access said object. This allows for the garbage collector to correctly free the unmanaged memory allocated to the COM object(s).

[edit]You'll need to search through your code for more occurences that need fixing. I didn't bother to mention them all, if more do exist.[/edit]

[APaule]

What I wanted to tell melegant was just do delete the line

MyExcel = nothing.

melegant took most of the code out of a Knowledgebase Article and what you cann't see here is the Sub NARS. There the Marshalling object kills the office application and in the Finally part of the errorhandling the object is set to nothing anyway.

But in the posted code snipet, the object passed to NARS is allready nothing. So nothing will happen in NARS and the office application remains loaded.

That's all.

Edited May 20, 2003 by APaule

[Derek Stone]

Melegant stated in his post that that code snippet works fine, since it successfully closes Excel. No advice on getting that block of code working is necessary. The second block of code is what he is requesting help with.

[APaule]

Derek Stone, the point is not to close Excel but to release it, so that the process is stopped.

I was able to reproduce the behaviour. So it is hard to believe that the first block of code works, because there melegant also sets the variable that holds the excel-object to nothing before he passes it to NARS.

So this code shouldn't work either, except the compiler considers time, weather and location...:D (I think we all have some experience with unpredictable and mysterious behaviour of code)

[veetian]

Hello,

I am facing problem ending the EXCEL.EXE process as well, I have followed the methods suggested by Derek Stone, but the process still lies in the server...

 

can i know what is NARS? a self-defined sub? how come i can't find any reference to it?

[melegant]

Nars

 

Privte Sub Nars(o as Object)
Try 
  System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally 
   o = nothing
End Sub

 

I have to say that would not work well either..so this is what I did

 

Private Sub KillExcelPr()
Dim mp As Process() = Procss.GetProcessesByName("EXCEL")

Dim p As Process
For Each P in mp 
  If P.Responding Then
     If p.MainWindowTitle = "" Then
        p.Kill()
     End If
  Else
     p.Kill()
  End If
Next p
End Sub

 

and there it is folks. that will kill any excel running in memory that does not have a title window. ( meaning if the user has a spreadsheet open otherwise, it won't get killed)

 

Peace.

[veetian]

Hello, thanks for the code, what namespace do i need to import in order for the Process.GetProcessesByName to get going?

[WineB]

The "KillExcel" code worked fine!

 

Just to say that the "KillExcel" code worked fine for me ! I only added a Try-Catch handler, because ASP.NET doesn't have the right to touch anything (like processes) other then what it has created itself.

But the rest worked like a charme !

Thanks !

[bob01900]

"This coding style is required as a result of a weird inconsistency (dare I call it that?) in the garbage collector. Each COM object needs to have a managed object explicitly associated with it before one should access said object. This allows for the garbage collector to correctly free the unmanaged memory allocated to the COM object(s)." - Derek Stone

 

Well, if you download "101 Code Samples for Visual Basic .NET" from

 

http://msdn.microsoft.com/vbasic/downloads/samples/default.aspx

 

and compile & run the sample "VB.NET - Windows Forms - How-To Automate Office", you will see the exact same problem!! Actually all the examples about automating Office/Excel that I found in msdn has this problem. (You can find them here:

http://support.microsoft.com/common/canned.aspx?R=d&H=Visual%20Basic%20.NET%20How%20To%20Articles&LL=kbvbnetSearch&SZ=kbhowtomaster

)

 

I think the problem is MS's garbage collector, not the way the code is written. And yes, you CAN kill the Excel process, but IMHO thats not really a nice way of closing processes.

[Derek Stone]

The examples that I have looked at from MSDN and the 101 Code Samples for Viusal Basic .NET download are coded incorrectly as far as I'm concerned. There are various steps that need to be taken when it comes to COM interop, and they simply aren't addressed in those samples.

[bob01900]

Oh okay. Do you mind giving me a link to a correct guideline regarding to this topic? Thanks. Because I've tried to do what you said, but it simply didn't work.

[YaYa]

Hello,

 

I use following method---kill process.

First, it shows access is denied.

 

Then, I change Microsoft EXCEL's setting in dcomcnfg--

aspnet has access right,

it shows another error message:

Process performance counter is disabled, so the requested operation cannot be performed.

 

What does it mean?

And How to solve it?

 

Thanks.

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

 

 

Nars

 

Privte Sub Nars(o as Object)
Try 
  System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch
Finally 
   o = nothing
End Sub

 

I have to say that would not work well either..so this is what I did

 

Private Sub KillExcelPr()
Dim mp As Process() = Procss.GetProcessesByName("EXCEL")

Dim p As Process
For Each P in mp 
  If P.Responding Then
     If p.MainWindowTitle = "" Then
        p.Kill()
     End If
  Else
     p.Kill()
  End If
Next p
End Sub

 

and there it is folks. that will kill any excel running in memory that does not have a title window. ( meaning if the user has a spreadsheet open otherwise, it won't get killed)

 

Peace.