CODEWITHSUNDEEP
vbaexcel
user4467707
user4467707

Reputation: 75

vba workbooks before close skipping over open command

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?

Upvotes: 3

Views: 806

Answers (3)

Boovember
Boovember

Reputation: 26

I've identified the issue to be inherent with application.screenupdating = false. It works just fine if you turn this off right before your workbook open command.

Upvotes: 0

user4467707
user4467707

Reputation: 75

Here is my solution as of right now:

...
Application.Run (parts_library_name & "!backup_exit_size")
Workbooks(parts_library_name).Close savechanges:=True
...

this works but may not be ideal.

Upvotes: 0

Kyle
Kyle

Reputation: 564

I'm seeing the same behavior. I even tried using WithEvents.

As a workaround, you can open the new workbook in a new instance of 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

Upvotes: 1

Related Questions