在VBA中编写宏 - excel在用户关闭Excel之后

I need to write a macro in Excel VBA, that terminates a process running in windows tasks AFTER the excel has been closed down. I tried it doing this on event workbook_BeforeClose

我需要在Excel VBA中编写一个宏，它在关闭excel之后终止在windows任务中运行的进程。我尝试在事件workbook_BeforeClose上执行此操作

Private Sub Workbook_BeforeClose(CANCEL As Boolean)
    Run "MacroCloseProcess"
End Sub

Where as MacroCloseProcess is defined like this

MacroCloseProcess定义如下

Private Sub MacroCloseProcess()
    Dim oWMT As Object, oProcess As Object
    Set oWMT = GetObject("winmgmts://")
    For Each oProcess In oWMT.InstancesOf("Win32_Process")
        If (oProcess.name) = pWcfHostApp Then

            If oProcess.Terminate() = 0 Then Exit Sub
        End If
    Next
End Sub

This works, BUT, if there are changes made in the workbook, excel gives the user option to "Do you want to save the changes you made to 'Sheet1.xlsx' ? Save, Don't Save, Cancel

这有效，但是，如果工作簿中有更改，excel会向用户提供“您是否要保存对'Sheet1.xlsx所做的更改'的选项？”保存，不保存，取消

If user clicks cancel, Excel does not exit ( as per design) but oh, the process has been terminated because it was in a "BeforeClose" event. How can i write this code so that it hits after the excel closes ?

如果用户单击取消，Excel不会退出（根据设计）但是，该过程已终止，因为它处于“BeforeClose”事件中。我怎样才能编写这段代码，以便在excel关闭后点击它？

3 个解决方案

#1

Take control of the user decision. This is simple code which could be improved if needed.

控制用户决策。这是一个简单的代码，可以根据需要进行改进。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'take control of what user can do:
If MsgBox("Do you want to save and exit?", vbYesNo) = vbYes Then
    Application.DisplayAlerts = False
        ThisWorkbook.Save
        'call your MacroCloseProcess here
    Application.DisplayAlerts = True
Else
    Cancel = True
End If
End Sub

*EDIT * Better and more elegant option:

*编辑*更好，更优雅的选择：

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'take control of what user can do:
    Application.DisplayAlerts = False
    Dim filePath As Variant

    If Len(ThisWorkbook.Path) > 0 Then
        'has been already saved therefore just ask
        'this would be rarely meet, only whan call for the first time
        'or if this solution was placed in class module for all doc

        If MsgBox("Do you want to save and exit?", vbYesNo) = vbYes Then
            ThisWorkbook.Save
            'call your MacroCloseProcess here
            MsgBox "exit" '<-- to remove, kept for tests
        Else
            Cancel = True
        End If
    Else
        'document was not saved before- show standard file dialog

        filePath = Application.GetSaveAsFilename()
        If VarType(filePath) = vbString Then

            ActiveWorkbook.SaveAs Filename:=filePath
            'call your MacroCloseProcess here
            MsgBox "exit" '<-- to remove, kept for tests

        Else
            Cancel = True
        End If
    End If
    Application.DisplayAlerts = True
End Sub

#2

Some of the other ideas are ok and I agree with saving first, but you should ask for their permission beforehand. This also allows the user to save first as it checks first if it's saved.

其他一些想法是可以的，我同意先保存，但你应事先征得他们的同意。这也允许用户首先保存，因为它首先检查它是否已保存。

You should invoke the user to save the workbook before you terminate. E.g.

您应该在终止之前调用用户保存工作簿。例如。

Private Sub Workbook_BeforeClose(CANCEL As Boolean)

If Not Me.Saved Then 
    Msg = "Do you want to save the changes you made to " 
    Msg = Msg & Me.Name & "?" 
    Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) 
    Select Case Ans
        Case vbYes 
            Me.Save 
        Case vbNo 
            Me.Saved = True 
        Case vbCancel 
            Cancel = True 
            Exit Sub 
    End Select
End If

Run "MacroCloseProcess"

End sub

#3

Maybe you could offer the user a do you wish to save option before you close the excel and then suppress any further dialogs for the user by

也许你可以在关闭excel之前为用户提供你想要保存的选项，然后通过以下方式禁止用户进一步的对话框

Application.DisplayAlerts=False or

Application.DisplayAlerts = False或

ThisWorkbook.Close (False)

ThisWorkbook.Close（False）

#1