关闭之前的 vba 工作簿跳过打开命令

Question

我有三本作业簿、一本家长书、一本图书馆书和一本日志。

父书打开一个库，然后关闭该库。巧合的是，图书馆在工作簿中提供了有关打开和关闭之前事件的规定，以便在打开或关闭时写入日志。

我的问题是，当从父簿以编程方式打开库时，会跳过关闭之前打开日志簿的代码。我没有收到任何错误，它只是简单地评估该行并且不产生任何结果。

代码:

从我的家长书中，我运行了这一行:

...
Workbooks(parts_library_name).Close savechanges:=True
...

它在图书馆书中运行此代码:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set libwkb = ThisWorkbook
'last call to backup usage sheet
backup_exit_size
'exit
End Sub

Sub backup_exit_size()
Dim bypass_close_usage_library As Boolean

Application.DisplayAlerts = False
file_size_at_close = FileLen(ThisWorkbook.Path & "\" & ThisWorkbook.Name)
For Each Workbook In Workbooks
    If Workbook.Name = "usage_library.xlsx" Then bypass_close_usage_library = True
Next
Application.ScreenUpdating = False
If bypass_close_usage_library = False Then
    Workbooks.Open "somepath\usage_library.xlsx"
End If
Workbooks("usage_library.xlsx").Worksheets("usage").Cells(next_row_usage_library, 3) = file_size_at_close
Workbooks("usage_library.xlsx").Close savechanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

问题是当它到达这一行时:

    Workbooks.Open "somepath\usage_library.xlsx"

编译器只是跳过该命令。

但是，只有当我到达源自另一本书的 beforeClose 事件时才会出现这种情况。如果我自行关闭日志，则宏可以正常运行并可以正常打开日志。

我尝试了父书中的 with 语句，例如

With library.xlsx
 .RunAutoMacros xlAutoClose 
 .Close 
End With

但有相同的结果，它会评估该行，然后跳过它。

想法？

Answer 1

我看到了同样的行为。我什至尝试使用WithEvents。

作为解决方法，您可以在新的 Excel 实例中打开新工作簿:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim xlapp As New Excel.Application
    Dim wb As Workbook
    Set wb = xlapp.Workbooks.Open("\\...\test.xlsb")
    xlapp.Visible = True 'This line just proves that it works and can be removed'
    'do stuff
    xlapp.Quit
    Set wb = Nothing
    Set xlapp = Nothing

End Sub