COM错误？两次打开工作簿会导致引用损坏 [英] COM bug? Opening a workbook twice leads to broken reference

问题描述

要引起我的注意，请 fuglede ：

这是COM错误吗？

我打开Excel工作簿A，然后打开工作簿B（两个工作簿在单元格A1中都有一些标识文本）。

I open Excel workbook A, then open workbook B (both of which have a bit of identifying text in cell A1).

然后我尝试再次打开工作簿A，并将该引用保存到新变量 a2 中。但是 a2 现在指向工作簿B！

Then I attempt to open workbook A again and save that reference in a new variable a2. But a2 now points to workbook B!

在Python中，使用win32com复制了完全相同的行为。因此，这不是具体的VBA问题，而是更一般的COM问题。 （即大概也在C＃等中，尽管尚未得到确认。）

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.)

发生了什么事？

VBA代码：（Python win32com代码与此类似。）

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 '<------------- !!!

即时窗口中的结果输出：（与Python控制台类似）

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") 

（出乎意料）与

Application.Workbooks.Open "H:\a.xlsx"
Set a2 = ActiveWorkbook

因为a.xlsx已经打开b.xlsx是（并且仍然是）活动工作簿，所以a2得到对b.xlsx的引用

Because a.xlsx is already open b.xlsx is (and remains) the activeworkbook, so then a2 gets a reference to b.xlsx.

为了防止发生此问题，您应在尝试再次打开工作簿之前测试工作簿是否已在Excel中打开。

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.

这篇关于COM错误？两次打开工作簿会导致引用损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！