Credit to fuglede for bringing this to my attention:
Is this a COM bug?
I open Excel workbook A, then open workbook B (both of which have a bit of identifying text in cell A1).
Then I attempt to open workbook A again and save that reference in a new variable a2
. But a2
now points to workbook B!
This exact same behavior is reproduced in Python using win32com. So this isn't a VBA issue specifically, bur rather a COM issue more generally. (i.e. presumably also in C# etc., though not confirmed yet.)
What is going on?
VBA code: (Python win32com code is similar.)
'Open a couple of workbooks
Dim a As Workbook, b As Workbook, a2 As Workbook
Debug.Print "Set a = a.xlsx..."
Set a = Application.Workbooks.Open("H:\a.xlsx")
Debug.Print "a: " & a.Name
Debug.Print "Set b = b.xlsx..."
Set b = Application.Workbooks.Open("H:\b.xlsx")
Debug.Print "b: " & b.Name
Debug.Print "a: " & a.Name
'Attempt to re-open workbook a.xlsx and keep reference in a new variable
Debug.Print "Set a2 = a.xlsx..."
Set a2 = Application.Workbooks.Open("H:\a.xlsx") '<---- should open A, right?
Debug.Print "a2: " & a2.Name '<------------------------------------------------ !!!
Debug.Print "b: " & b.Name
Debug.Print "a: " & a.Name
'Read workbook content
Debug.Print "Contents of cell A1..."
Debug.Print "From a: " & a.Sheets("Sheet1").Range("A1").Value
Debug.Print "From b: " & b.Sheets("Sheet1").Range("A1").Value
Debug.Print "From a2: " & a2.Sheets("Sheet1").Range("A1").Value '<------------- !!!
Resulting output in the Immediate window: (similar in Python console)
Set a = a.xlsx...
a: a.xlsx
Set b = b.xlsx...
b: b.xlsx
a: a.xlsx
Set a2 = a.xlsx...
a2: b.xlsx <--------------- WTF?!
b: b.xlsx
a: a.xlsx
Contents of cell A1...
From a: I'm in A
From b: I'm in B
From a2: I'm in B <--------------- WTF?!
This is a bug indeed. Apparently this line:
Set a2 = Application.Workbooks.Open("H:\a.xlsx")
(unexpectedly) works the same as
Application.Workbooks.Open "H:\a.xlsx"
Set a2 = ActiveWorkbook
Because a.xlsx is already open b.xlsx is (and remains) the activeworkbook, so then a2 gets a reference to b.xlsx.
In order to prevent the problem from happening you should test whether a workbook is already open in Excel before trying to open it again.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With