Close all excel books (multiple excel instances may be open)

Question

I try to close all excel books when I either publish a new personal.xlsb file or when a user needs to update it to the latest version. Below is my code for closing all excel books, however it only closes PERSONAL.XLSB at startup:

Public Sub CloseAll() Dim wb As Workbook For Each wb In Application.Workbooks() wb.Close True ' Or False if you don't want changes saved Next wb Application.Quit End Sub 

here is the publication code, i.e. copy your personal.xlsb file to the server: (may be useful for others, therefore, posted here)

 Sub publish() Call Settings.init 'Contains excelMakroVersjon="101" Dim hFile As Long Dim FileContents1 As String Dim versionNumber As String Dim strFile1 As String strFile1 = "Z:\Dokumentstyring\LatestVersion\CopyMacro.bat" Kill strFile1 Kill "Z:\Dokumentstyring\LatestVersion\PERSONAL*" versionNumber = Left(excelMakroVersjon, 1) & "." & Right(excelMakroVersjon, Len(excelMakroVersjon) - 1) FileContents1 = "ping -n 5 127.0.0.1 > nul " & vbNewLine _ & "echo f | xcopy /f /y /z ""%APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLSB"" ""Z:\Dokumentstyring\LatestVersion\PERSONAL_" & versionNumber & ".XLSB"" " Open strFile1 For Binary As #1 Put #1, , FileContents1 Close #1 Shell "cmd.exe /k " & strFile1 Call CloseAll End Sub 

and here is the code to check if you have the latest version, which should also use the closeAll method:

 Sub checkLatestVersion() Dim temp, temp2 As Variant Call Settings.init temp = Dir("Z:\Dokumentstyring\LatestVersion\Personal*") temp = Mid(temp, 8, 4) temp2 = val(Replace(temp, ".", "")) If temp2 > val(Settings.excelMakroVersjon) Then Select Case MsgBox("Upgrade to latest Version: " & temp, vbYesNo) Case vbYes Shell "cmd.exe /k Z:\Dokumentstyring\LatestVersion\updateExcel.bat" Call CloseAll Case vbNo 'Do nothing. End Select End If End Sub 

Answer 1

try this ... (Be careful, the following code also closes the current excel file!)

 Dim oServ As Object Dim cProc As Variant Dim oProc As Object Set oServ = GetObject("winmgmts:") Set cProc = oServ.ExecQuery("Select * from Win32_Process") For Each oProc In cProc If oProc.Name = "EXCEL.EXE" Then MsgBox "Close?" errReturnCode = oProc.Terminate() End If Next