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在开放声明之后是没有的(我想提供更好的错误信息,但不知道我是怎么做的;我为真正的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:
单步执行时的图像:
7 个解决方案
#1
5
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
在自定义函数中使用VBA关键字
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宏或函数中使用的表达式之外,不允许自定义函数执行任何操作。例如,自定义函数无法调整窗口大小,编辑单元格中的公式或更改单元格中文本的字体,颜色或图案选项。如果在函数过程中包含此类“动作”代码,则该函数返回#VALUE!错误。
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.
我发现的唯一解决方法是通过普通的宏调用这种代码。类似于选择要应用它的单元格,然后循环选择等。
#2
1
To get data from Workbook without is open, you can use this, with ADO connection.
要在没有打开的情况下从Workbook获取数据,可以使用此方法与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
#3
1
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.
我有一个类似的问题,但在我的情况下,它是一个“Workbooks.Open(filename)”命令,在Workbook_BeforeSave中嵌入的一个小例程的开头。 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打开更多文件,我想这样做是有充分理由的,因为File> Open选项仍然可以在File菜单中看到,但是无法单击它。奇怪的是,打开工具栏图标/按钮仍然有效,所以虽然我可以从那里手动打开文件,但我想知道是不是因为从VBA代码调用此操作是不可能的,这就是为什么他们允许它?
#4
1
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的第二个实例将毫无干扰地完成工作。
#5
0
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
#6
0
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:当然要避免使用全局变量,某种容器会比直接全局变量更好。
#7
0
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
#1
5
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
在自定义函数中使用VBA关键字
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宏或函数中使用的表达式之外,不允许自定义函数执行任何操作。例如,自定义函数无法调整窗口大小,编辑单元格中的公式或更改单元格中文本的字体,颜色或图案选项。如果在函数过程中包含此类“动作”代码,则该函数返回#VALUE!错误。
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.
我发现的唯一解决方法是通过普通的宏调用这种代码。类似于选择要应用它的单元格,然后循环选择等。
#2
1
To get data from Workbook without is open, you can use this, with ADO connection.
要在没有打开的情况下从Workbook获取数据,可以使用此方法与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
#3
1
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.
我有一个类似的问题,但在我的情况下,它是一个“Workbooks.Open(filename)”命令,在Workbook_BeforeSave中嵌入的一个小例程的开头。 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打开更多文件,我想这样做是有充分理由的,因为File> Open选项仍然可以在File菜单中看到,但是无法单击它。奇怪的是,打开工具栏图标/按钮仍然有效,所以虽然我可以从那里手动打开文件,但我想知道是不是因为从VBA代码调用此操作是不可能的,这就是为什么他们允许它?
#4
1
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的第二个实例将毫无干扰地完成工作。
#5
0
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
#6
0
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:当然要避免使用全局变量,某种容器会比直接全局变量更好。
#7
0
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