Close all excel workbooks(several instances of excel might be open)
<p>I am trying to close all excel workbooks when I either publish a new personal.xlsb or when the user needs to upgrade to the latest version. Below is my code for closing all excel workbooks, however it only closes PERSONAL.XLSB when its run:</p>

<pre><code>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
</code></pre>

<p>here is the publish code, i.e copy my personal.xlsb to server: (might be useful for others, hence posted here)</p>

<pre><code>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) &amp; "." &amp; Right(excelMakroVersjon, Len(excelMakroVersjon) - 1)

    FileContents1 = "ping -n 5 127.0.0.1 &gt; nul " &amp; vbNewLine _
        &amp; "echo f | xcopy /f /y /z ""%APPDATA%\Microsoft\Excel\XLSTART\PERSONAL.XLSB""  ""Z:\Dokumentstyring\LatestVersion\PERSONAL_" &amp; versionNumber &amp; ".XLSB"" "

    Open strFile1 For Binary As #1
    Put #1, , FileContents1
    Close #1
    Shell "cmd.exe /k " &amp; strFile1
    Call CloseAll
End Sub
</code></pre>

<p>and here is the code to check if you have the laste version, which also needs to use closeAll method:</p>

<pre><code>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 &gt; val(Settings.excelMakroVersjon) Then
        Select Case MsgBox("Upgrade to latest Version: " &amp; 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
</code></pre>