Excel Won't DIE..please help!

[melegant]

I cant 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 WONT 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 dosnt 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]

Its all clear that your code doesnt work properly.

            MyExcel = Nothing
NARS(MyExcel)

You set MyExcel to nothing before passing it to NARS.

 

[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]Youll need to search through your code for more occurences that need fixing. I didnt 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 cannt 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.
Thats all.

 

[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 shouldnt work either, except the compiler considers time, weather and location... (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 cant 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 wont 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 doesnt 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...To Articles&LL=kbvbnetSearch&SZ=kbhowtomaster
)

I think the problem is MSs 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 Im concerned. There are various steps that need to be taken when it comes to COM interop, and they simply arent addressed in those samples.

 

[bob01900]

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

 

[YaYa]

Hello,

I use following method---kill process.
First, it shows access is denied.

Then, I change Microsoft EXCELs 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.
----------------------------------------------------------------------

Originally posted by 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 wont get killed)

Peace.