Excel VBA 无法打开工作簿
首先：我使用的是 Excel 2007，但代码也必须适用于 Excel 2003。
我的问题如下：我需要访问不同工作簿中的单元格，该工作簿可能已关闭。可以在网络上找到以下代码：
Function Foo()
Dim cell As Range
Dim wbk As Workbook
Set wbk = Workbooks.Open("correct absolute path")
' wbk is Nothing here so the next statement fails.
Set cell = wbk.Worksheets("Sheet1").Range("A1")
Foo = cell.Value
wbk.Close
End Function
遗憾的是，wbk 在 open 语句之后什么都没有（我很想给出更好的错误消息，但不知道我会如何做到这一点；我会给出真正的IDE 和一种有用的语言:/)。绝对路径正确并指向有效的 Excel xlsx 文件。
另外，我认为执行此操作的最佳方法是“缓存”工作簿，而不是每次调用函数时都打开/关闭它？任何可能的问题（除了必须处理工作簿显然已经打开的情况之外）？
单步执行时的图像： 
First: I'm using Excel 2007, but the code has to work for Excel 2003 as well.
My problem is the following: I need to access cells in a different workbook, which may be closed. The following code can be found all around the web:
Function Foo()
Dim cell As Range
Dim wbk As Workbook
Set wbk = Workbooks.Open("correct absolute path")
' wbk is Nothing here so the next statement fails.
Set cell = wbk.Worksheets("Sheet1").Range("A1")
Foo = cell.Value
wbk.Close
End Function
sadly, wbk is Nothing after the open statement (I'd love to give a better error message, but no idea how I'd do that; what I'd give for a real IDE and an useful language :/). The absolute path is correct and points to a valid excel xlsx file.
Also I assume the best way to do this, is to "cache" the workbook and not open/close it every time the function is called? Any possible problems with that (apart from having to handle the situation when the workbook is already open obviously)?
Image while stepping through:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我可以重现这个问题。仅当我尝试将此代码粘贴到用户定义的函数中时，才会发生这种情况。
我相信这是设计使然（引用的是 XL 2003，但同样的事情也发生在 XL 2010 上）
http://office.microsoft.com/en -us/excel-help/creating-custom-functions-HA001111701.aspx
我发现的唯一解决方法是通过普通宏调用此类代码。比如选择要应用它的单元格，然后循环选择等等。
I can reproduce this problem. It only happens to me when I attempt to paste this code into a user-defined function.
I believe this is by design (the quote is for XL 2003, but the same thing happens to me on XL 2010)
http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx
The only workaround I've found is to call this kind of code via a normal macro. Something like selecting the cells to apply it to, then looping over Selection or the like.
您可以使用这个（类似于 Bruno Leite 提出的，但编写起来更简单）：
由于重复调用 UDF，您应该确保在退出函数之前执行 excelApp.Quit （并在之前执行 WB.close(False) ）以避免在您的机器上运行无数的 Excel 实例。
我对此进行了一些思考，得出的结论是，在执行 UDF 时不能乱搞当前 excel 实例的工作簿。另一方面，打开 Excel 的第二个实例将在不受干扰的情况下完成这项工作。
You can use this (similar to what Bruno Leite proposed, but much simpler to write):
As UDFs are called repeatedly, you should make sure to do an excelApp.Quit before exiting the function (and a WB.close(False) before) to avoid having countless Excel instances running on your box.
I spent some thoughts on it and came to the conclusion that you cannot mess around with the workbooks of the current instance of excel while executing a UDF. On the other hand, opening a second instance of excel will do the job without interference.
要在未打开的情况下从工作簿获取数据，您可以使用此，以及ADO 连接。
要在 Excel 2007 中使用，请将其更改
和
[
为
]'s
To get data from Workbook without is open, you can use this, with ADO connection.
To use in Excel 2007 change this
to
and
to
[]'s
将我的例程放入工作簿模块中的单独宏中，然后从 Workbook_BeforeSave 代码中调用该宏的解决方法似乎已经成功了。
我也遇到过类似的问题，但就我而言，它是嵌入在 Workbook_BeforeSave 中的小例程开始处的“Workbooks.Open(filename)”命令。 VBA 只是跳过该代码行，就好像它不存在一样，它甚至不报告 Err.Code 或 Err.Description。
对我来说唯一的线索是它是 Workbook_BeforeSave 例程的一部分，并且上述函数的限制似乎表明这可能是一个可能的原因。所以我进一步挖掘以找到更多细节。
看来 Workbook_BeforeSave 会禁止 Excel 打开更多文件，我想这样做是有充分理由的，因为 File > “打开”选项在“文件”菜单中仍然可见，但无法单击。奇怪的是，“打开”工具栏图标/按钮仍然有效，因此虽然我可以从那里手动打开文件，但我想知道是否是因为无法从 VBA 代码调用此操作，这就是他们允许这样做的原因？
The workaround of putting my routine into a separate macro in the workbook module, and calling that macro from the Workbook_BeforeSave code, seems to have done the trick.
I've had a similar issue, but in my case it's a "Workbooks.Open(filename)" command at the start of a small routine embedded in Workbook_BeforeSave. VBA just skips right over the line of code as if it weren't there, it doesn't even report an Err.Code or Err.Description.
The only clue for me was that it's part of the Workbook_BeforeSave routine, and the limits with Functions above seem to indicate that could be a possible cause. So I dug around further to find more details.
It seems that Workbook_BeforeSave disables Excel from opening more files, and I guess there's a good reason for doing that, since the File > Open option is still visible in the File menu, but it can't be clicked. Strangely, the Open toolbar icon/button still works, and so whilst I can manually open the file from there, I wonder if it's because it's impossible to call this action from VBA code and that's why they allowed it?
您不必“设置”单元格，它是工作簿类的一部分（据我所知）。只需使用以下...
You don't have to "Set" a cell, It's part of the workbook class (as far as I know). Just use the following...
我建议您在 worbook_open 事件中打开调用工作簿时打开新工作簿。
然后，您将新的工作簿引用存储在全局变量中。
然后，您的单元格调用的函数将使用所述全局变量，而不是尝试打开新的工作簿。这样你就可以绕过限制。
PS：当然要避免全局变量，某种容器比直接全局变量更好。
I would suggest that you open you the new workbook upon opening the calling workbook, in the worbook_open event.
You then store the new workbook reference in a global variable.
Then the function called by your cell uses the said global variable instead of trying to open a new workbook. This way you go around the limitations.
PS : Of course global variable are to be avoided, some sort of container would be better than a direct global variable.
您可以使用以下代码以正确的方式检查错误：
我知道这不能回答问题（这就是为什么我也进入这个线程，因为我无法打开文件并且无法理解为什么会这样）
干杯，
房车
You can check the error in a proper way by using the following code:
I know that this does not answer the question (that's why I also landed in this thread, as I cannot open the file and can't understand why is that so)
Cheers,
RV