Application.Quit - EXCEL.EXE is still visible in the Task Manager

  • I am running a process via scheduled task where a batch file opens up an Excel
    session, loads an XLL, calculates a bunch of numbers, saves the values to a
    separate file with the date/time stamp, and closes down.


    This process
    works fine apart from the closing down bit. Though Excel itself is not visible
    any longer, an EXCEL.EXE is still running (with a large memory footprint) in the
    Task Manager.


    The problem is that since this process is repeated on an
    hourly basis, the scheduled task encounters memory overflow errors due to the
    ghost Excel threads, unless I physically terminate all the ghsot threads every
    few hours. This is really inconvenient.


    I've researched this a lot, and
    it looks like it's not an uncommon problem. So I was thinking that one
    workaround could be to log the PIDs of the processes kicked off by the scheduled
    task and then run another scheduled task every few hours to go and kill those
    PIDs.


    Can anyone suggest another technique? If not, could anyone point me
    to some sample code as I don't really know how or where to start.


    Thank
    you.

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    Unfortunately it's a commerical XLL so I can't upload the code. Apols for the crosspost. Now back to the question:
    1. Can anybody suggest another way of ensuring the errant threads are purged from memory?
    2. Is there any sample code I can look at log the PIDs.


    Thxx

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    Of course if this is too complex and you all would rather answer questions about how to convert letters to uppercase and and conditional formatting, please do let me know and I'll take my query elsewhere.

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    Quote

    Of course if this is too complex and you all would rather answer questions about how to convert letters to uppercase and and conditional formatting, please do let me know and I'll take my query elsewhere.

    You already have. Lose the attitude.

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    What attitude? Your reply did not in any way seek to address the question I posed, but was a curt remark about the cross posting rules. I'd suggest a bit more courtesy towards newer members in the future.

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    Quote

    and you all would rather answer questions about how to convert letters to uppercase and and conditional formatting

    That attitude. Post your code here if you want help from here.


    Quite a few Possible Answers below.

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    This batch script kicks everything off. This is the script that's wrapped into the Windows Scheduler.


    Code
    @ECHO OFF
    SET AUTOCOPY=TRUE
    START EXCEL "C:\Users\elvis\Desktop\Book1.xls"


    Books1.xls serves as the "calling" workbook. The XLL is loaded into Excel's memory via the Workbook_Open event once the AUTOCOPY envrionment variable is set.



    Once the XLL is cached, the OpenUp method (included in Module1 of Book1) causes Book2 (which does all the heavy calculations) to open up. Once Book2 stops calculating (after its Workbook_Open event) the rest of this module ia just logic to copy/paste value/number formats into the final workbook (Book3). Book3 is the file that's saved down with date/timestamp.



    The Workbook_Open event of Book2 doesn't really do much apart from calling CalcAll, a method in Module1 of Book2.


    Code
    Option Explicit
    Private Sub Workbook_Open()
        Call CalcAll
    End Sub


    The CalcAll method of Book2 loops through all the worksheets in Book2 and does all the relevant calculations. Depending on the complexity of the calculations (which depends on the time of day), this can take between 6 and 45 minutes.


    Code
    Option Explicit
    Public Sub CalcAll()
        Dim ws As Worksheet
        For Each ws In Worksheets
            ws.UsedRange.Calculate
        Next ws
    End Sub


    Once Book2 has finished calculating, it is saved via Module1 of Book1 and the application is terminated.


    Only it isn't as I can see a ghost EXCEL.EXE thread still running in the Task Manager.


    Thank you in advance.

  • Re: Application.Quit - EXCEL.EXE is still visible in the Task Manager


    Just saw AAE's reply. When I'd originally posted there were no possible answers that came up.


    I've spent a lot of time thinking/researching this problem and I'm completely baffled. Initially I was running a set of scheduled tasks at night. This was easy enough to deal with, I wrote a C# class that uses the System.Process class to terminate EXCEL.EXE processes that begin between 12am and 6am. This worked fine.


    The problem I now have is that in addition to the nightly sched tasks, I am running an hourly one and I don't want to take a sledge hammer approach of terminating all threads. Which is why I started barking up this PID tree.


    That being said, AAE's links look *very* promising.

  • Re: Application.Quit doesn't work - EXCEL.EXE is still visible in the Task Manager


    When I did a quick example, it closed ok. Hopefully, the suggestions posted by AAE will fix your problem.


    What I would recommend is that you use a VBS file rather than a BAT file. In that way, you can close the instance of Excel or just the workbook and leave an existing instance open. A user might not like you closing their instance. There can be other problems with the locking of the personal workbook using the BAT method with Excel already open.


    Don't let being a new user told the rules bother you too much. The people here and the other forums can help you a lot if you are patient.


    Here is how I did a quick check:
    In the BAT file:

    Code
    @ECHO OFF
    SET AUTOCOPY=TRUE
    START EXCEL "x:\t\Book1.xlsm"
    SET AUTOCOPY=FALSE


    In the XLSM:

    Code
    Private Sub Workbook_Open()
        If Environ("AUTOCOPY") = True Then
            Sheet1.Range("A1").Value2 = Sheet1.Range("A1").Value2 + 1
            ThisWorkbook.Save
            Application.Quit
        End If
    End Sub


    For kicks, you might find that passing command line parameters to be handy. http://www.dailydoseofexcel.com/archives/2006/01/25/command-line-arguments-in-vba/

  • Re: Application.Quit - EXCEL.EXE is still visible in the Task Manager


    I do something very similar, when I set the scheduled task though on the settings tab I change “Stop the task if it runs for:” to 0 hours and 3 minutes.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!