写VBA宏 - 练成之后，用户已经关闭了Excel(Write a Macro in VBA - e

我需要写在Excel VBA中，终止在Windows任务运行后对Excel已经倒闭的过程的宏。我试了一下就事件做这个workbook_BeforeClose

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

凡为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

这工作，但如果有工作簿中所做的更改，Excel将用户选项“你想救你‘Sheet1.xlsx’所做的更改？保存，不保存，取消

如果用户点击取消，Excel不退出（按设计），但是，唉，这一进程被终止，因为它是在一个“BeforeClose”事件。我怎么能写这样的代码，以便它在Excel中关闭后打？

Answer 1:

以用户决策的控制权。这是简单的代码，如果需要，其可以改善。

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

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

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

Answer 2:

一些其他的想法是好的，我同意节约优先，但你应该问他们的准许。这也允许用户先保存，因为它首先检查它是否保存。

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

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