Excel VBA 无法打开工作簿

Excel VBA can't open Workbook

提问by Voo

First: I'm using Excel 2007, but the code has to work for Excel 2003 as well.

第一：我使用的是 Excel 2007，但代码也必须适用于 Excel 2003。

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.

可悲的是， wbk 在 open 语句之后是 Nothing （我很想提供更好的错误消息，但不知道我会怎么做；我会为真正的 IDE 和有用的语言提供什么：/）。绝对路径正确并指向有效的 excel xlsx 文件。

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:

步进时的图像：

回答by purple_arrows

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)

我相信这是设计使然（报价适用于 XL 2003，但同样的事情发生在 XL 2010 上）

Using VBA keywords in custom functions

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

在自定义函数中使用 VBA 关键字

您可以在自定义函数中使用的 VBA 关键字数量小于您可以在宏中使用的数量。除了将值返回到工作表中的公式或另一个 VBA 宏或函数中使用的表达式之外，不允许自定义函数执行任何操作。例如，自定义函数无法调整窗口大小、编辑单元格中的公式或更改单元格中文本的字体、颜色或图案选项。如果您在函数过程中包含此类“操作”代码，则该函数将返回 #VALUE！错误。

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

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.

我发现的唯一解决方法是通过普通宏调用这种代码。类似于选择要应用的单元格，然后循环选择等。

回答by Tom

You can use this (similar to what Bruno Leite proposed, but much simpler to write):

您可以使用它（类似于 Bruno Leite 的建议，但编写起来更简单）：

Dim excelApp As New Excel.Application
excelApp.Visible = False
Set WB = excelApp.Workbooks.Open(FileName, xlUpdateLinksNever, True)

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.

由于 UDF 被重复调用，您应该确保在退出函数之前执行 excelApp.Quit（以及之前执行 WB.close(False)）以避免在您的机器上运行无数 Excel 实例。

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.

我对此进行了一些思考，得出的结论是，您不能在执行 UDF 时弄乱当前 excel 实例的工作簿。另一方面，打开 excel 的第二个实例将在没有干扰的情况下完成工作。

回答by baldmosher

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.

将我的例程放入工作簿模块中的单独宏并从 Workbook_BeforeSave 代码调用该宏的解决方法似乎已经成功。

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.

我遇到了类似的问题，但在我的情况下，它是嵌入在 Workbook_BeforeSave 中的一个小例程开始时的“Workbooks.Open(filename)”命令。VBA 只是直接跳过代码行，就好像它不存在一样，它甚至不报告 Err.Code 或 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.

对我来说唯一的线索是它是 Workbook_BeforeSave 例程的一部分，上面函数的限制似乎表明这可能是一个可能的原因。所以我进一步挖掘以找到更多细节。

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?

似乎 Workbook_BeforeSave 禁止 Excel 打开更多文件，我想这样做是有充分理由的，因为文件 > 打开选项在文件菜单中仍然可见，但无法单击。奇怪的是，打开工具栏图标/按钮仍然有效，所以虽然我可以从那里手动打开文件，但我想知道这是不是因为从 VBA 代码调用这个操作是不可能的，这就是他们允许的原因？

回答by Bruno Leite

To get data from Workbook without is open, you can use this, with ADO connection.

要在未打开的情况下从工作簿获取数据，您可以通过 ADO 连接使用它。

To use in Excel 2007 change this

要在 Excel 2007 中使用，请更改此

Microsoft.Jet.OLEDB.4.0

to

到

Provider=Microsoft.ACE.OLEDB.12.0

and

和

Extended Properties=\"Excel 8.0;HDR=Yes;\

to

到

Extended Properties=\"Excel 12.0;HDR=Yes;\

[]'s

[] 的

回答by TheFuzzyGiggler

You don't have to "Set" a cell, It's part of the workbook class (as far as I know). Just use the following...

您不必“设置”单元格，它是工作簿类的一部分（据我所知）。只需使用以下...

foo = wbk.Worksheets("Sheet1").Range("A1").Value

回答by nicolas

I would suggest that you open you the new workbook upon opening the calling workbook, in the worbook_open event.

我建议您在 worbook_open 事件中打开调用工作簿时打开新工作簿。

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.

PS：当然要避免使用全局变量，某种容器会比直接全局变量更好。

回答by RVicente

You can check the error in a proper way by using the following code:

您可以使用以下代码以正确的方式检查错误：

filelocation = c:\whatever\file.xlsx

On Error GoTo Handler 'this is key as if the next row returns an error while opening the file it will jump to the Handler down there.
Set wkb2 = Workbooks.Open(filelocation, ReadOnly)

Handler:
MsgBox "File " & filelocation & " does not exist or cannot be reached, please review and try again"

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

干杯，房车