6

I have a small excel file that is launched by a scheduling app every 15 minutes.

Functions in the excel cells read data from various places on the network and stores it in cells in this excel file. That all works perfectly.

VBA code then saves the file and does an Application.Quit.

In the previous version of excel, it worked great. Since upgrading to 2016, Application.Quit closes the "excel interface" but Task manager shows a hundred "Excel.exe"'s still sitting there using up 40MB of memory each.

The attached picture shows only five excel.exe's in Task Manager since the computer had only been running less than two hours at this point. But after 24 hours there are about 100 of them. So the computer crashes within a few days.

Excel.exe still running

Thanks for the speedy assistance. I'm not sure if I'm supposed to put my responses here by editing my original question.

Here is the code. The actual updating of cell values is performed in the cells themselves. There is an Add In that reads values from some PLCs in our factory. That all works fine.

Private Sub Workbook_Open()
    Application.CalculateFull
    ActiveWorkbook.Save
    Application.Quit
End Sub

When the excel file it launched, it updates and closes. This has worked for many years until we upgraded to 2016. Application.Quit would make excel go away completely. Not now.

Davidfox789
  • 63
  • 1
  • 5
  • 4
    Where is your code? Please include that in your question. – braX Mar 23 '18 at 13:42
  • 1
    Why aren't you running the code from a single instance that opens and closes workbooks as necessary? –  Mar 23 '18 at 13:42
  • Are any add-ins loaded? – Mathieu Guindon Mar 23 '18 at 13:54
  • 1
    @Vityata - No, multiple workbooks are still open in the same application instance, just in different aaplication windows. –  Mar 23 '18 at 14:03
  • 2
    I'll second @braX' request for code. A simple [mcve] would do. We need to see what your code is doing, otherwise it's all just guesswork. – Mathieu Guindon Mar 23 '18 at 14:06
  • @Jeeped - Yup, you are right, I read this here - https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/excel-2016-one-instance/7d4c3391-df2c-43ae-9c98-b23a18f52b53 that made me think the other way for about 10 minutes. However I have managed to make the the `?Application.Workbook.Count` return 2. – Vityata Mar 23 '18 at 14:10
  • I posted the code in my original question above. I'm not sure if that's where it is supposed to go, or down here in the comments. Sorry such a newb. – Davidfox789 Mar 23 '18 at 14:12
  • @Davidfox789 - you got to be kidding ... The needed code is the one that opens the new Excel Worksheets. – Vityata Mar 23 '18 at 14:14
  • I don't want to open the worksheets from within Excel. There are many of these small excel files and we already have a scheduling app outside of excel that runs them at the correct times. I don't want to write a scheduling app inside every one of these files. It originally took 1 minute to write the VBA portion of these files and I'd like to keep it that simple. I'd just like to know what command to use to close Excel completely from within VBA. – Davidfox789 Mar 23 '18 at 14:19

3 Answers3

3

Before Excel 2016, Excel had the possibilities to have multiple Excel files in a single window.

In Excel 2016, it is one window per application.

The problem with your code is that it closes an instance. Based on the fact how the Excel files were opened, this would be either enough or not. E.g., if Excel files were opened in the same instance this would be quite enough.

Vityata
  • 39,812
  • 7
  • 40
  • 77
  • Thanks for the response. Most of the time there should be no instances of excel running. Several times per hour a scheduling app launches an excel file that does a recalc, a save and a quit. After that, there should once again be no instances of excel running. Apparently Application.Quit is not enough to make excel go away completely. – Davidfox789 Mar 23 '18 at 14:22
  • 1
    @Davidfox789 - this looks like a working example, if you remove the `MsgBox` - https://stackoverflow.com/a/35734389/5448626 – Vityata Mar 23 '18 at 14:39
  • I will give that a try. It's weird there isn't a simple command to completely close excel from within VBA. Seems like a Band-Aid to have to do house cleaning after the fact. Thank you for your time and effort! – Davidfox789 Mar 23 '18 at 15:01
2

A bit of an amateur myself and I realize this is a bit of an old thread, but I am wondering if you save the workbook (as you do) but also close the workbook and quit Excel, it may clear up the task manager. I notice you save the workbook but don't actually close the workbook, so it stays open. I ran into a similar issue before and I think this finally what fixed it.

This is code I use every time I want to quit Excel. Usually I have 2 books open, one is a template (which I don't save) and the other is one that was created with data from the template.

ActiveWorkbook.Close SaveChanges:=True
Application.Quit
ActiveWorkbook.Close SaveChanges:=False
Chris Murrell
  • 21
  • 1
1

This answer worked for me: Excel.Application Object .Quit leaves EXCEL.EXE running

Basically, you need to hold onto your workbook objects. Then after closing them, set the workbooks to Nothing before calling quit or the workbook won't be garbage collected and keep the EXCEL.EXE instance open. There may be objects other than workbooks that can keep excel open too. Just be aware of these other objects and clean up before you quit. I tell my kids that all the time, but they never listen. Honestly I think it's silly that Application.Quit doesn't clean up after itself, but c'est la vie.

Jroonk
  • 1,308
  • 9
  • 8