Kill all instances of Excel (except current)

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Kill all instances of Excel (except current)

Post by Rudi »

Hi,

I have a user that runs an app that exports reports to Excel. This app seems to open additional instances of Excel each time a report is sent (and never closes that instance again). After exporting a dozen reports, there as a dozen instances of Excel that bog down memory etc...

How can I set up a macro to kill all instances of Excel except the instance that contains the macro in question?

I see a lot of code across forums and pages and they all seem different?
For example:

Code: Select all

Public Sub CloseAllExcel()
On Error GoTo handler
 
   Dim xl As Excel.Application
   Dim wb As Excel.Workbook
 
   Do While xl Is Nothing
      Set xl = GetObject(, "Excel.Application")
      For Each wb In xl.Workbooks
         wb.Save
         wb.Close
      Next
      xl.Quit
      Set xl = Nothing
   Loop
   Exit Sub
   
handler:
   If Err <> 429 Then 'ActiveX component can't create object
      MsgBox Err.Description, vbInformation
   End If
 
End Sub
AND...

Code: Select all

Sub Close_Excel()
     Dim strClsExl As String
     strClsExl = "TASKKILL /F /IM Excel.exe"
     Shell strClsExl, vbHide
  if strClsExl
End Sub
...and this page which finds (counts) instances, maybe this can be modified to close them?: Finding Other Instances of Excel in a Macro

I need assistance to develop some code to close all these instances except the one which stores this macro.
TX.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Kill all instances of Excel (except current)

Post by HansV »

You could create a batch file with contents

Code: Select all

@echo off
taskkill /f /im excel.exe
When you double-click the batch file, it will end ALL instances of Excel. Since you don't run it from Excel, you don't have to try to keep one instance running.

Alternatively, run the Close_Excel macro from another application, for example from Word.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Kill all instances of Excel (except current)

Post by Rudi »

TX,

I'll present these to the user and see if they are OK with the options.
Appreciated.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Kill all instances of Excel (except current)

Post by rory »

Try this:

Code: Select all

Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long

Sub FindAndTerminate()
   Dim objWMIService, objProcess, colProcess
   Dim strComputer
   strComputer = "."
   Set objWMIService = GetObject("winmgmts:" _
                                 & "{impersonationLevel=impersonate}!\\" _
                                 & strComputer & "\root\cimv2")
   Set colProcess = objWMIService.ExecQuery _
                    ("Select * from Win32_Process Where Name = 'excel.exe' And ProcessID <> " & GetCurrentProcessId)
   If colProcess.Count > 0 Then
      For Each objProcess In colProcess
         objProcess.Terminate
      Next objProcess
   End If
End Sub
Regards,
Rory

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Kill all instances of Excel (except current)

Post by Rudi »

Wow...
That looks like a bit of Cobol mixed with a bit of C++ interweaved with VB nested inside some Fortram or Assembly :grin:

I'll give it a try
TX Rory!
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

pietg
NewLounger
Posts: 1
Joined: 04 Mar 2021, 13:01

Re: Kill all instances of Excel (except current)

Post by pietg »

rory wrote:
22 May 2014, 12:50
Try this:
...
Holy moly rory, after hours of googling, this post saved my day. Apparently still works in Excel365.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Kill all instances of Excel (except current)

Post by ChrisGreaves »

HansV wrote:
22 May 2014, 09:27
You could create a batch file with contents ...
Thanks Hans.
I have elected to borrow this (rather than create it) :thankyou:
Cheers
Chris
An expensive day out: Wallet and Grimace

clovis
NewLounger
Posts: 2
Joined: 10 Feb 2023, 09:22

Re: Kill all instances of Excel (except current)

Post by clovis »

rory wrote:
22 May 2014, 12:50
Try this:

Code: Select all

Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long

Sub FindAndTerminate()
   Dim objWMIService, objProcess, colProcess
   Dim strComputer
   strComputer = "."
   Set objWMIService = GetObject("winmgmts:" _
                                 & "{impersonationLevel=impersonate}!\\" _
                                 & strComputer & "\root\cimv2")
   Set colProcess = objWMIService.ExecQuery _
                    ("Select * from Win32_Process Where Name = 'excel.exe' And ProcessID <> " & GetCurrentProcessId)
   If colProcess.Count > 0 Then
      For Each objProcess In colProcess
         objProcess.Terminate
      Next objProcess
   End If
End Sub
And here we are, years later and this is still working fine!
Only change is regarding the delaration of the GetCurrentProcessId, now it needs to be done as following:
Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
Thank you very much! I only lost a full day before I found your solution!

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Kill all instances of Excel (except current)

Post by rory »

clovis wrote:
10 Feb 2023, 09:30
Only change is regarding the delaration of the GetCurrentProcessId, now it needs to be done as following:

Code: Select all

Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
It only needs that if you are using 64bit Office, although adding the PtrSafe will allow it to work with any version from 2010 onwards. To cover all bases, we could use:

Code: Select all

#If VBA7 then
Declare PtrSafe Function GetCurrentProcessId Lib "kernel32" () As Long
#Else
Declare Function GetCurrentProcessId Lib "kernel32" () As Long
#End If
Regards,
Rory