Excel 365 Version 1902 ( Build 11328.20420)
I encountered this problem whilst rewriting code in an existing spreadsheet application at work. The new code opens two excel files concurrently setting each excel workbook object to a file. The first time the method runs the expected behaviour is exhibited., the two objects point to their respective files. On the second and subsequent runs the first object seems to be overwritten by the second object and both objects point to the same file. I recreated this in a test method see below.
Option Explicit
Sub Main()
On Error GoTo err_handler
Dim Book1 As Workbook
Dim Book2 As Workbook
Set Book1 = Application.Workbooks.Open("C:\Test\Book1.xlsx")
Set Book2 = Application.Workbooks.Open("C:\Test\Book2.xlsx")
Debug.Print Book1.Name
Debug.Print Book2.Name
Exit Sub
err_handler:
MsgBox Err.Description
End Sub
The code produces this set of results
Run 1:
Book1.xlsx
Book2.xlsx
Run 2:
Book2.xlsx
Book2.xlsx
This issue is specific to Office 365. I tested the same code in Office 2016 and there is no issue.
Office 2016 results:
Run 1:
Book1.xlsx
Book2.xlsx
Run 2:
Book1.xlsx
Book2.xlsx
Is this a known issue that has been corrected in later versions of Office 365 or something new?
Many thanks,
Simon.
