在 VBA 中编写宏 - 用户关闭 Excel 后的 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15947082/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write a Macro in VBA - excel AFTER the user has closed the Excel
提问by Hamda Binte Ajmal
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 关闭后点击？
采纳答案by Kazimierz Jawor
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
回答by glh
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
回答by K_B
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=Falseor
Application.DisplayAlerts=False或者
ThisWorkbook.Close (False)
ThisWorkbook.Close (False)
