在Visual Basic中打开两个工作簿时，工作簿变量未正确分配 [英] When two workbooks are opened in visual basic, workbook variables do not get assigned correctly

问题描述

运行下面的代码时，根据打开的工作簿，我得到不同的结果.该子模块位于与 Master Sheet.xlsm

When running the code below, I get different results depending on which workbooks are open. The sub is in a module associated with Master Sheet.xlsm

1. 如果仅打开 Master Sheet.xlsm ，则该代码将正确运行，即消息框显示(其中逗号分隔第一和第二个消息框):Master Sheet，已转移案例03-09-18

1. If just the Master Sheet.xlsm open then the code runs correctly, i.e. the Message Boxes say (where comma separates the first and second message box): Master Sheet, transferred cases 03-09-18

如果同时打开了和已转移案例03-09-18.xlsx ，但已转移已转移案例03-09-18.xlsx 打开第二个，然后消息框显示:已转移案例03-09-18，已转移案例03-09-18

If both Master Sheet.xlsm and transferred cases 03-09-18.xlsx are open but transferred cases 03-09-18.xlsx was open second then the message boxes say: transferred cases 03-09-18, transferred cases 03-09-18

如果 Master Sheet.xlsm 和已转移案件03-09-18.xlsx 均已打开，但 Master Sheet.xlsm 打开第二个，然后消息框显示:Master Sheet，Master Sheet

If both Master Sheet.xlsm and transferred cases 03-09-18.xlsx are open but Master Sheet.xlsm was open second then the message boxes say: Master Sheet, Master Sheet

---

Sub foo()
    Dim x As Workbook
    Dim y As Workbook

   '## Open both workbooks first:
   Set x = Workbooks.Open("C:\Users\owner\Documents\ExelatecOutput\Master Sheet.xlsm")
   Set y = Workbooks.Open("C:\Users\owner\Documents\ExelatecOutput\transferred cases 03-09-18.xlsx")

   'Now, copy what you want from x:
   MsgBox x.Name
   MsgBox y.Name

End Sub

为什么变量x和y分配不正确.

Why do the variables x and y not get assigned correctly.

推荐答案

Workbooks.Open 始终返回最后打开的文件(即使它不是传入的参数).这可能是错误的文档，也可能是excel IMO中的错误.

Workbooks.Open always return the last opened file (even if it is not the one passed in parameter). This is either bad documentation or bug in excel IMO.

您不需要检查文件是否已打开，因为打开已经打开的文件不会引发错误，但是您稍后需要设置变量:

You don't need to check if the file is opened, since opening an already open file does not raise an error, but you need to set the variable later:

Workbooks.Open "C:\Users\owner\Documents\ExelatecOutput\Master Sheet.xlsm": Set x = Workbooks("Master Sheet.xlsm") ' or Set x = ActiveWorkbook since Open will activate it
Workbooks.Open "C:\Users\owner\Documents\ExelatecOutput\transferred cases 03-09-18.xlsx": Set y = Workbooks("transferred cases 03-09-18.xlsx") ' or Set y = ActiveWorkbook since Open will activate it

这篇关于在Visual Basic中打开两个工作簿时，工作簿变量未正确分配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！