Excel Session Left Open on Server 1

[jennuhw]

Can someone please explain to me why this is leaving an Excel session open on the server? I must be missing something little. Thanks!

Call fso.CopyFile(otherfile, fname)

        ' create the Excel object
        Dim objXlApp = Server.CreateObject("Excel.Application")

        objXlApp.visible = True

        ' open the spreadsheet file
        Dim books = objXlApp.Workbooks.open(fname)

        ' get the worksheet
        Dim objXlSheets = books.Worksheets(1)

        'getting customer quote information
        Dim CQArray As New ArrayList()
        Label1.Text = Request.QueryString("Item")
        CQArray.AddRange(CQPull(Request.QueryString("item")))

        objXlSheets.range("A1").value = "Order Number"





        'objXlSheets = Nothing
        books.Close(True)
        objXlApp.quit()
        objXlApp = Nothing

        fso = Nothing

 

[bigtimmin]

The reason for this behavior is that Excel itself opens multiple objects "under the covers" that don't get released.
First add an import statement:

Imports System.Runtime.InteropServices.Marshal

If running from a client, after the quit statement, release all the DIM objects, then call the garbage collector

ReleaseComObject(objXLSheets)
ReleaseComObject(books)
ReleaseComObject(objXL)
GC.Collect()

If running from a sever, it's not a good ideal to use the garbage collector. Dim a statement for each object that Excel opens (application, workbooks, workbook, sheets, worksheet, range, xlcells, xlsheet, xlsheets, xlbook, xlbooks, xlApp - check with microsoft incase I missed some) then after the quit statement release each one with the releasecomobject statement.

Maybe this world is another planet’s Hell.
Aldous Huxley

 

[jennuhw]

That did it!! Thanks so much!

 

[jennuhw]

It did work at one time, now it has stopped. I can't figure out why.

' create the Excel object
        Dim objXlApp = Server.CreateObject("Excel.Application")

        objXlApp.visible = True

        ' open the spreadsheet file
        Dim books = objXlApp.Workbooks '.open(fname)
        Dim book = books.open(fname)

        ' get the worksheet
        Dim objXlSheets = book.Worksheets(2)

        'getting customer quote information
        Dim CQArray As New ArrayList()
        Label1.Text = Request.QueryString("Item")
        CQArray.AddRange(CQPull(Request.QueryString("item")))

        objXlSheets.range("A1").value = "Order Number"
        objXlSheets.range("B1").value = "Order Date"
        objXlSheets.range("C1").value = "Customer ID"
        objXlSheets.range("D1").value = "Customer Name"
        objXlSheets.range("E1").value = "Item ID"
        objXlSheets.range("F1").value = "Order Qty"
        objXlSheets.range("G1").value = "Required Date"
        objXlSheets.range("H1").value = "Unit Price"
        objXlSheets.range("I1").value = "Extended Price"

        For x = 0 To CQArray.Count - 1
            objXlSheets.range("A" & (x + 2)).value = CQArray(x)(0)
            objXlSheets.range("B" & (x + 2)).value = CQArray(x)(8)
            objXlSheets.range("C" & (x + 2)).value = CQArray(x)(1)
            objXlSheets.range("D" & (x + 2)).value = CQArray(x)(2)
            objXlSheets.range("E" & (x + 2)).value = CQArray(x)(3)
            objXlSheets.range("F" & (x + 2)).value = CQArray(x)(4)
            objXlSheets.range("G" & (x + 2)).value = CQArray(x)(5)
            objXlSheets.range("H" & (x + 2)).value = CQArray(x)(6)
            objXlSheets.range("I" & (x + 2)).value = CQArray(x)(7)
        Next

        fso = Nothing

        ReleaseComObject(objXlSheets)
        book.Close(True)
        objXlSheets = Nothing
        book = Nothing
        ReleaseComObject(books)
        books = Nothing
        ReleaseComObject(book)
        objXlApp.quit()
        ReleaseComObject(objXlApp)
        objXlApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()

 

[SqueakinSweep]

Welcome to the unpredictable world of com objects!!
The following code may work for you

objXlApp.Quit()
While System.Runtime.InteropServices.Marshal.ReleaseComObject(objXlApp) <> 0
End While

Sweep
...if it works dont f*** with it

 

[jennuhw]

I used that code, but the application just hangs. Any other ideas? Thanks.

 

[SqueakinSweep]

Sorry...that should be

objXlApp.Quit()
While System.Runtime.InteropServices.Marshal.ReleaseComObject(objXlApp) > 0
End While


Sweep
[b]...if it works dont f*** with it[/b]
[img]http://www.tipmaster.com/images/curse.gif[/img]

 

[jennuhw]

The application stopped hanging which is great. Still isn't killing the Excel session on the server. Here is the code I have now:

 ' create the Excel object
        Dim objXlApp = Server.CreateObject("Excel.Application")

        objXlApp.visible = True

        ' open the spreadsheet file
        Dim books = objXlApp.Workbooks '.open("\\esp7\kadb$\temp\" & fname)
        Dim book = books.open("\\esp7\kadb$\temp\" & fname)

        ' get the worksheet
        Dim objXlSheets = book.Worksheets(2)

        'getting customer quote information
        Dim CQArray As New ArrayList()
        Label1.Text = Request.QueryString("Item")
        CQArray.AddRange(CQPull(Request.QueryString("item")))

        objXlSheets.range("A1").value = "Order Number"
        objXlSheets.range("B1").value = "Order Date"
        objXlSheets.range("C1").value = "Customer ID"
        objXlSheets.range("D1").value = "Customer Name"
        objXlSheets.range("E1").value = "Item ID"
        objXlSheets.range("F1").value = "Order Qty"
        objXlSheets.range("G1").value = "Required Date"
        objXlSheets.range("H1").value = "Unit Price"
        objXlSheets.range("I1").value = "Extended Price"
        ReleaseComObject(objXlSheets)
        book.Close(True)
        objXlSheets = Nothing
        ReleaseComObject(book)
        book = Nothing
        ReleaseComObject(books)
        books = Nothing
        objXlApp.quit()

        While ReleaseComObject(objXlApp) > 0
        End While
        'ReleaseComObject(objXlApp)
        objXlApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()

I am using this in an ASP.net application on the page_load event. Could there be a better way of doing this? Thanks so much for all of your time so far.