How to close hidden Excel instances?

Thank you Leith!
that's some awesome piece of code you made
next time my computer is crowded with loose excel instances I'll give it a shot, it certainly must do the trick!

Originally Posted by Leith Ross

Hello bagullo,

This macro will close all other separate instances of Excel (hidden or visible) that are running. Only the instance that contains the macro will remain running.

'Written: April 17, 2011
'Author:  Leith Ross

'Declare API constants used.
 Private Const GW_CHILD As Long = 5
 Private Const GW_HWNDNEXT = 2
 Private Const MAX_SIZE As Long = 260
 Private Const WM_CLOSE As Long = &H10

'API calls
 Private Declare Function GetWindow _
   Lib "user32.dll" _
     (ByVal hWnd As Long, _
      ByVal wCmd As Long) _
   As Long

 Private Declare Function GetDesktopWindow _
   Lib "user32.dll" () As Long

 Private Declare Function GetWindowThreadProcessId _
   Lib "user32.dll" _
     (ByVal hWnd As Long, _
      ByRef lpdwProcessId As Long) _
   As Long
     
 Private Declare Function GetClassName _
   Lib "user32.dll" Alias "GetClassNameA" _
     (ByVal hWnd As Long, _
      ByVal lpClassName As String, _
      ByVal nMaxCount As Long) _
   As Long

    
 Private Declare Function GetWindowText _
   Lib "user32.dll" Alias "GetWindowTextA" _
     (ByVal hWnd As Long, _
      ByVal lpString As String, _
      ByVal nMaxCount As Long) _
   As Long
 
 Private Declare Function PostMessageA _
   Lib "user32.dll" _
     (ByVal hWnd As Long, _
      ByVal wMsg As Long, _
      ByVal wParam As Long, _
      ByVal lparam As Long) _
   As Long


'VBA Macro
Sub CloseWorkbooks()
 
 'Closes all other workbooks opened in separate instances of Excel.
 
  Dim ClassName As String
  Dim hWnd As Long
  Dim hWndMain As Long
  Dim hWnds() As Variant
  Dim L As Long
  Dim N As Long
  Dim Pid As Long
  Dim RetVal As Long
  Dim WbkName As String
  
     'Save the hWnd of this workbook running the macro
      hWndMain = Application.hWnd
      
     'Start with the Top most window on the Desktop
      hWnd = GetWindow(GetDesktopWindow, GW_CHILD)

     'Loop while the hWnd returned by GetWindow is valid.
      While hWnd <> 0
      
        If hWnd <> hWndMain Then
          'Get the Window Class name
           ClassName = String(MAX_SIZE, Chr(0))
           L = GetClassName(hWnd, ClassName, MAX_SIZE)
           ClassName = IIf(L > 0, Left(ClassName, L), "")
           
          'Save the window handles of all Excel instances except this one.
           If ClassName = "XLMAIN" Then
              ReDim Preserve hWnds(N)
              hWnds(N) = hWnd
              N = N + 1
           End If
        End If
           
        'Get the next Window
         hWnd = GetWindow(hWnd, GW_HWNDNEXT)
         
        'Process other events
         DoEvents
      Wend

     'Close the workbooks
     'Since window handles are reused, it is safer to use a second loop to close
     'the windows rather than close them in the search loop.
      For I = 0 To UBound(hWnds)
        RetVal = PostMessageA(hWnds(I), WM_CLOSE, 0&, 0&)
          If RetVal = 0 Then
             WbkName = String(MAX_SIZE, Chr(0))
             L = GetWindowText(hWnds(I), WkbName, MAX_SIZE)
             WkbName = IIf(L > 0, Left(WkbName, L), "")
             MsgBox "Workbook '" & WkbName & "' failed to Close."
          End If
      Next I
      
End Sub

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

tried it out and works like a charm!

thnks!

Hi Leith,

just a small question...

would it be possible to close those hidden workbooks as "savechanges:=false" so that everything happens behind the scenes?

sometimes users have told me they feel very confused when excel asks if they want to save chaneges of files they did not open, neither see on their screen.

I tried to do it myself, but your code is way beyond what I can handle!

thanks!!

hi roy,
actually I use that to be able to open other excel workbooks without macros enabled. That makes reading their data much quicker. Plus, I can show user progress, without having them to stare a static screen throughout the process

Originally Posted by snb

Did you ever try opening a workbook like this ? ( in the same Excel instance).

Sub snb()
 Application.ScreenUpdating = False
 GetObject ("E:\OF\adressen 002.xls")
End Sub

Nope, never tried, is it better than workbook open? does it open without macros?
how to you store that in a workbook variable?

And the simplest method I know to close all Excel instances except the one that is triggering this procedure is:

Sub snb()
 For Each tsk In CreateObject("Word.Application").Tasks
  If InStr(tsk, "Microsoft Excel") Then
   If Split(tsk.Name, "-")(1) <> ThisWorkbook.Name Then tsk.Close
  End If
 Next
End Sub

did not know this way of closing workbooks -- does this work as welll for separate excel applications? I'll keep it in mind.