Application.Quit doesn't work - EXCEL.EXE still visible in Task Manager

I

insomniac_ut

New Member
Joined
Sep 15, 2005
Messages
36
Hello,

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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
As you wish:

Embedded in the Workbook_Open event of the "calling workbook"
Code: 
Option Explicit

Private Const XLLPath = "C:\Users\elvis\Documents\Old Files\Programming\CSharp\MathXL\MathXL\bin\Release"
Private Const XLLAddin = "C:\Users\elvis\Documents\Old Files\Programming\CSharp\MathXL\MathXL\bin\Release\MathXL.xll"


Private Sub Workbook_Open()

    If Environ("AUTOCOPY") = True Then
        ChDir XLLPath
        Application.RegisterXLL XLLAddin
        Application.Calculation = xlCalculationManual
        Application.CalculateBeforeSave = False
        Application.DisplayAlerts = False
        Call OpenUp
        Application.Quit
    End If
    
End Sub

The OpenUp method:
Code: 
Option Explicit

Public Sub OpenUp()

    Dim wbSource As Workbook
    Dim wbDest As Workbook
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    
    Set wbSource = Workbooks.Open("C:\Users\elvis\Desktop\Book2.xls")
    Set wsSource = wbSource.Worksheets(1)
    
    Set wbDest = Application.Workbooks.Add
    Set wsDest = wbDest.Worksheets.Add
    
    wsSource.Cells.Copy
    wsDest.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    wsDest.Name = "Test123"
    
    Application.CutCopyMode = False
     
    wbDest.SaveAs Filename:="C:\Users\elvis\Desktop\Book3.xls"
    wbDest.Close
    
    wbSource.Save
    wbSource.Close

    Set wsDest = Nothing
    Set wbDest = Nothing
    Set wsSource = Nothing
    Set wbSource = Nothing

End Sub

The Workbook_Open event of Book2:
Code: 
Option Explicit

Private Sub Workbook_Open()

    Call CalcAll

End Sub

The CalcAll method of Book2:
Code: 
Option Explicit

Public Sub CalcAll()

    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.UsedRange.Calculate
    Next ws

End Sub

Finally the batch script that makes everything fit together:
Code: 
@ECHO OFF
SET AUTOCOPY=TRUE
START EXCEL "C:\Users\elvis\Desktop\Book1.xls"
 
Upvote 0
Got me but I'm curious why do you need to do this registering in a workbook open event? Also, why put the code in two workbooks - I'm not sure why you think you need to open book2 from book1 instead of just opening book2 (and using it's open event)? You could also just leave book2 running invisibly and just schedule whatever it is you're doing every hour.

Personally I've never put a quit command in an open event so no experience on that. But you could also try scheduling a quit after 5 minutes or whatever seems reasonable. I prefer to use a vbs script to open a workbook and run a sub in it in a standard module rather than using open events (don't know if it makes any difference).
 
Last edited:
Upvote 0
Hi Xenou,

My thinking was as follows: Book2 is the sheet that does all the calculations, nothing else. Book1 is the 'control' sheet, it opens up Book2, makes it calculate, does the copying/pasting, etc.

The 5-minute delayed task won't work either, because there's no clear way to identify what processes to kill without the PID.

I have another set of tasks I run between 12am and 6am. It's easy to memory manage those because I run a C# class that identifies start times and kills any EXCEL threads that started between those two times.

It's a bit trickier to do this intra-working-day as I also have other Excel files open on the machine.

Thanks.
 
Upvote 0
I don't understand what you're doing with registering an xll every time you open the file so you'll have to educate me on how that's involved here. In general, all this talk about processes seems out of place. You open Excel, run your sub, and close it. By a 5 minute delay, I mean the workbook closes "itself" ... there's no need to know any PIDs. Though it may be a problem buried in what your workbooks are doing rather than this (rather simple) code that merely opens and closes the workbooks.
 
Last edited:
Upvote 0
Hi,

Yes - that's correct, from what I've seen it's not an uncommon problem when using XLLs. Why I embed the XLL file into the Workbook_Open event is to load the library of functions required to do the computation. Just out of convenience.
 
Upvote 0
Hmm. So you suspect there is a problem with the xll? Care to elaborate? I was only wondering why it must be registered every time you open the file.
 
Upvote 0
As you are opening various inter-related workbooks under the same excel instance try to find out which code of which workbook finishes exccuting last and put the the Application.Quit line at the end of that code.
 
Upvote 0
What's going on with error handling?

Are you sure all the code is being executed because I think the following should cause an error:
Code: 
    wsDest.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

There's no Paste argument for the worksheet.PasteSpecial method...only range.PasteSpecial.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Task Scheduled Macro Workbooks not running
Replies
0
Views
934
john316swan
J
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
399
AndyEd
A
H
  • Question
Prevent VBE from idling when user action is required (Win32 APIs)
2
Replies
12
Views
3K
Jaafar Tribak
Jaafar Tribak
L
  • Question
VBA works on work PC but not home PC
Replies
3
Views
2K
rollis13
rollis13
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,215,008
Messages
6,122,672
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top