Excel 关闭时运行的 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15567524/
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
VBA Running when Excel closed
提问by maximladus
I have a challenge, for me at least, I cannot deal with apparently. Can somebody help me or advise on how to make the macro run when Excel is closed?
我有一个挑战，至少对我来说，我显然无法应对。有人可以帮助我或建议如何在 Excel 关闭时运行宏吗？
How can I make the macro run when the Excel is closed via VBA?
当通过 VBA 关闭 Excel 时，如何使宏运行？
Sub Upload0()
' Upload Webpage content
Application.OnTime Now + TimeValue("00:00:15"), "Upload0"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://cetatenie.just.ro/ordine/articol-11", Destination:=Range("A1"))
.Name = "CetatenieOrdine"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 1
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
' Deletes empty cells
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
' Adjust column width and delet useless rows
Rows("1:31").Select
Selection.Delete Shift:=xlUp
Range("B28").Select
Selection.End(xlDown).Select
Rows("17:309").Select
Selection.Delete Shift:=xlUp
End Sub
Many Thanks to all!
非常感谢大家！
采纳答案by MattCrum
1: Define a boolean flag in a module Public blnClosedVBA as Booleanand set it to true in your VBA routine before closing the workbook. Then in your Workbook_BeforeClose event handler (under ThisWorkbook), do this:
1：在模块中定义一个布尔标志，Public blnClosedVBA as Boolean并在关闭工作簿之前在 VBA 例程中将其设置为 true。然后在您的 Workbook_BeforeClose 事件处理程序中（在 ThisWorkbook 下），执行以下操作：
If blnClosedVBA = True Then
Cancel = True 'Stops the workbook from closing
'Rest of your code here
blnClosedVBA = False ' Set so next time you try to close the workbook, it actually closes
'Workbook.Close or ThisWorkbook.Close - depends on your answer to my question below
That will run the routine only if you have triggered the close event yourself. At the end of the routine, setting the flag to False and triggering another Workbook.Closewill close the workbook
只有当您自己触发了关闭事件时，才会运行例程。在例程结束时，将标志设置为 False 并触发另一个Workbook.Close将关闭工作簿
2: Which workbook should it work for? Should it be 'ThisWorkbook' (the one from which you're running the code), 'ActiveWorkbook' (the one activated), or another one?
2：它应该适用于哪个工作簿？它应该是“ThisWorkbook”（您从中运行代码的那个）、“ActiveWorkbook”（激活的那个）还是另一个？
回答by Mike Woodhouse
1.How can I make the macro run when the workbook is closed via VBA?
1.如何在通过VBA关闭工作簿时运行宏？
Short answer: you can't do that. Your code is part of the workbook and it can't run unless the workbook is loaded in Excel. You might be able to move the code to a separate add-in workbook that you elect to load by default (using "Excel Options|Add-Ins") when Excel starts. But Excel would still need to be running somewhere on your computer.
简短的回答：你不能那样做。您的代码是工作簿的一部分，除非在 Excel 中加载工作簿，否则它无法运行。您可以将代码移动到您选择在 Excel 启动时默认加载的单独加载项工作簿（使用“Excel 选项|加载项”）。但是 Excel 仍然需要在您的计算机上的某个地方运行。
You could write a completely separate program that does what you want, writing the results of the web query into an Excel workbook. I'm assuming here that you want the workbook to always contain up-to-date data when it's referenced by yourself (when Excel is running of course) or some other resource. If you can acquire a copy of Visual Basic 6 (it may not be possible to achieve this legally) then that's mostly syntactically the same as VBA. Next closest would be VB.Net. This is going to be technically somewhat complex.
您可以编写一个完全独立的程序来执行您想要的操作，将 Web 查询的结果写入 Excel 工作簿。我在这里假设您希望工作簿在您自己（当然在 Excel 运行时）或其他资源引用时始终包含最新数据。如果您可以获得 Visual Basic 6 的副本（可能无法合法地实现这一点），那么这在语法上与 VBA 基本相同。下一个最接近的是 VB.Net。这在技术上会有些复杂。
2.Also, I have issue making this macro working ONLY for one workbook but not active ones:
2.另外，我有问题让这个宏只适用于一个工作簿而不是活动的工作簿：
This one we candeal with: this line:
这个我们可以处理：这一行：
With ActiveSheet.QueryTables.Add(Connection:= _
means that the following code will always run against the worksheet that has the focus (i.e. is "active" - the sheet that would receive keyboard input if you typed something). That's what ActiveSheetdoes. Try replacing `ActiveSheet with something likeThisWorkbook.Sheet1, whereSheet1` is the name for the sheet that you see in the "Properties" window in the VBA editor where it says "(Name)".
意味着以下代码将始终针对具有焦点的工作表运行（即“活动”-如果您键入内容将接收键盘输入的工作表）。这就是ActiveSheet它的作用。尝试替换`ActiveSheet with something likeThisWorkbook.Sheet1 , whereSheet1` 是您在 VBA 编辑器的“属性”窗口中看到的工作表的名称，其中显示“（名称）”。
