Excel关闭时VBA运行

Question

我有一个挑战，至少对我来说，我显然无法应对。有人可以帮助我或建议如何在 Excel 关闭时运行宏吗？

通过 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

非常感谢大家!

Answer 1

1:在模块中定义 bool 标志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

仅当您自己触发了关闭事件时，才会运行该例程。在例程结束时，将标志设置为 False 并触发另一个 Workbook.Close将关闭工作簿

2:它应该适用于哪个工作簿？它应该是“ThisWorkbook”(您从中运行代码的那个)、“ActiveWorkbook”(激活的那个)还是另一个？