I have three workbooks, a parent book, a library book and a log book.
The parent book opens a library, and then closes the library. Coincidentally the library has a provision in the workbooks on open and before close events to write to a log book whenever it is opened or closed.
My issue is that when the library is opened programmatically from the parent book, the code to open the log book before close is skipped over. I get no errors, it just simply evaluates the line and produces no result.
CODE:
From my parent book I run this line:
...
Workbooks(parts_library_name).Close savechanges:=True
...
Which runs this code in the library book:
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
The problem is that when it gets to this line:
Workbooks.Open "somepath\usage_library.xlsx"
the compiler simply skips over the command.
That is only the case however if I get to the beforeClose event that originated from another Book. If I close the book from itself, the macro runs fine and opens the log book fine.
I have tried a with statement from the parent book, like
With library.xlsx
.RunAutoMacros xlAutoClose
.Close
End With
but have the same results, it evaluates the line and then skips over it.
Thoughts?