'Can a workbook, opened via VBA from another workbook, in turn open a third workbook via VBA?

Just describing the issue hurt my Monday AM brain. Below is an outline of what the basis of my problem is. For ease of this example, all files are in the same directory.

  1. First workbook ('WB1') has a Sub ('Sub1') that opens another workbook ('WB2')
  2. WB1 does stuff to WB2
  3. WB1 Sub1 calls Save of WB2
Public Sub Sub1()

Dim WB1 As Workbook
Dim WB2 As Workbook

Set WB1 = ThisWorkbook

Set WB2 = Workbooks.Open(WB1.Path & "\WB2.xlsm") '1.
WB2.Sheets(1).Cells(1, 1) = "Hello World" '2.
WB2.Close True '3.

End Sub
  1. WB2's "BeforeSave" Sub is called, which in turn calls a Sub ('Sub2') from a Module within WB2
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ThisWorkbook.Saved = False Then
    Call Sub2
End If

End Sub
  1. Sub2 calls to open yet another workbook ('WB3') **
Public Sub Sub2()

Dim WB2 As Workbook
Dim WB3 As Workbook

Set WB2 = ThisWorkbook

Set WB3 = Workbooks.Open(WB2.Path & "\WB3.xlsm")
WB3.Sheets(1).Cells(1, 1) = "Hello World" '=== Error, WB3 wasn't opened
WB3.Close True

End Sub

at step 5, I get a failure in that WB3 does not get opened.

In the 'real life' code,

  1. I have confirmed that I can open WB3 from WB2 if Sub2 is called directly from WB2 (i.e. if WB1 is out of the picture), so it's not an error in the path for WB3 or anything like that.
  2. I can also call for the opening of WB3 from WB1, which is my current work around, but in our current system, this workaround would take a bit of doing to correct all the sheets that would be affected by this new add of "workbook.open-ception" -- the opening of WB3 is a new layer added to update an "overview" workbook from individual "log" workbooks.

If this behavior is by design, so be it. But if there's any way to make it work as is, that'd be great.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source