打开多个Excel实例会导致VBA问题吗? [英] Can having multiple instances of Excel open cause VBA issues?

问题描述

我在工作簿A中打开了代码，并对工作簿B进行了处理.当工作簿A和B是唯一打开的excel文件(或者如果工作簿A是唯一打开的文件)时，代码可以正常运行.但是，如果我打开任何其他工作簿(称为工作簿C)，则宏将无法正确运行.它不会引起错误消息，它只会运行到完成而无需执行任何应做的工作"(这些工作基本上是在工作簿B中找到东西并将其粘贴到工作簿A中).

I have code in workbook A that opens and does stuff to workbook B. Code runs fine when workbook A and B are the only excel files open (or if workbook A is the only file open). However, if I open up any additional workbook (call it workbook C), the macro does not run correctly. It doesn't cause an error message, it just runs to completion without doing any of the "stuff" it's supposed to do (the stuff is basically finding things in workbook B and pasting them into workbook A).

FWIW，我已经做了以下简单的实验:

FWIW, I have done the following simple experiment:

1. 打开所有3个工作簿(A，B和C)
2. 选择工作簿C，使其处于活动状态，并选择前窗口
3. 运行代码workbookB.sheet1.activate(这不是逐字记录的，我知道这段编写的代码将失败)

当我进行上述测试时，它甚至没有使工作簿B成为活动工作簿.同样，它不会导致excel引发错误消息，它只会运行并将工作簿C保留为活动工作簿.

When I do the above test, it doesn't even make workbook B the active workbook. Again, it doesn't cause excel to throw an error message, it just runs and leave workbook C as the active workbook.

我已经进行了更多的测试，下面的代码应该更改工作簿B中单元格的值，但是将其放入工作簿C中.我非常困惑，因为未以任何方式引用工作簿C(该模块在工作簿A)中

I've tested more, the code below should change the value of a cell in workbook B, but instead is putting the value into workbook C. I am very confused, since workbook C is not referenced in any way (the module is in workbook A)

Sub test()
    Dim wb As Workbook
    Set wb = Workbooks.Open("U:\workbookB.xlsx")
    wb.Worksheets("ED").Range("Z1").Value = "TEST"
End Sub

当工作簿A和B已打开几个小时，而工作簿C最近已打开时，就会出现此问题.我关闭了工作簿B，然后重新运行了代码，它可以正常工作.这使我相信，excel打开的多个实例存在某种问题.虽然这希望是低风险的，但我仍然好奇是否有人可以通过某种方式围绕它编写代码，以防万一?谢谢！

Edit 2: The issue was occurring when Workbook A and B had been open for several hours, and Workbook C was recently opened. I closed workbook B then re-ran the code and it is working correctly. This leading me to believe that there IS some sort of issue with multiple instances of excel opening. While this is hopefully low-risk, I am still curious if anyone has some way I could code around it as a precaution? Thanks!

推荐答案

有一个细微的错误(我很乐意称呼它，但这就是它的外观)，需要当心.

There is a subtle bug (I hesitate to call it that, but that's what it looks like) you need to watch out for.

如果您尝试通过代码打开的工作簿已经打开，那么偶尔您会看到一些意外的行为(例如 Workbooks.Open()的返回值)>被分配给 ThisWorkbook 而不是您期望的文件).

If the workbook you're trying to open via code is already open then occasionally you will see some unexpected behavior (such as the return value from Workbooks.Open() being assigned to ThisWorkbook instead of the file you expect).

例如下面的代码在"Tester.xlsm"中运行并打开"EmptyTest.xlsx"，但是如果该文件已经打开，则 Workbooks.Open 调用将无法正确进行分配 wb 变量，它最终指向"Tester.xlsm" .这可能会导致问题.

For example the code below runs in "Tester.xlsm" and is opening "EmptyTest.xlsx", but if that file is already open, the Workbooks.Open call fails to correctly assign the wb variable, and it ends up pointing at "Tester.xlsm". That can cause problems.

要复制，

• 打开Excel
• 打开"EmptyTest.xlsx"
• 打开"Tester.xlsm"
• 运行测试程序"子项

测试代码:

Sub Tester()

    Dim wb As Workbook

    'with "EmptyTest" already open
    Set wb = Workbooks.Open("C:\Tester\EmptyTest.xlsx")
    Debug.Print wb.Name '>> Tester.xlsm  -  oops!

    'close"EmptyTest" before proceeding
    Workbooks("EmptyTest.xlsx").Close False

    'with  "EmptyTest" closed
    Set wb = Workbooks.Open("C:\Tester\EmptyTest.xlsx")
    Debug.Print wb.Name '>>EmptyTest.xlsx - OK

End Sub

在我的系统上完全可复制(win10/Office 365)

Totally reproducible on my system (win10/Office 365)

这篇关于打开多个Excel实例会导致VBA问题吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！