Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

COM bug? Opening a workbook twice leads to broken reference

Tags:

excel

vba

com

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?!
like image 833
Jean-François Corbett Avatar asked Jan 24 '18 16:01

Jean-François Corbett


1 Answers

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.

like image 146
jkpieterse Avatar answered Oct 22 '22 06:10

jkpieterse