0

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:

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 publish code, i.e copy my personal.xlsb to server: (might be useful for others, hence 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 laste version, which also needs to use 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
skatun
  • 856
  • 4
  • 17
  • 36
  • You might want to have a look at the following Q&A as I believe that you have several instances of Excel running: http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel – Ralph Mar 01 '16 at 10:23
  • Thanks Ralph, seems like a lot of code to shut down all instances. The upgrade algorithm is run on every startup of excel, and hence no workbooks can be open, the publish is run by me, and i can manually shut down alll other then personal.xlsb, so this mean that I might rather just implement this in the batch files I am generating: 'taskkill /f /im Excel.exe' – skatun Mar 01 '16 at 10:39

1 Answers1

2

try this... (Be careful, following code closes also 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
Herry Markowitz
  • 208
  • 3
  • 15