Excel VBA在某些系统中需要文件扩展名以供工作簿参考 [英] Excel VBA requiring file extension for workbook reference in some systems
问题描述
我有一个简单的excel VBA，它可以在处理之前引用多个文件并将信息复制到主服务器上.在我自己的系统上进行构建时，工作簿参考(工作得很好)被编写为:
I have a simple excel VBA that is referring multiple files and copying information across to a master before processing. While building this on my own system a workbook reference (working perfectly fine) was written as:
Workbooks("key").Sheets("Sheet1").Range("A1:X57").Copy
这里的密钥是一个.xlsx文件
Here key is a .xlsx file
虽然在另一个系统中使用它却无法正常工作，并且在每次调用中都明确要求文件扩展名.
While using this in another system this does not work and it explicitly requires the file extension in every call.
Workbooks("key.xlsx").Sheets("Sheet1").Range("A1:X57").Copy
尽管我想了解为什么会发生这种变化，但对我来说并不是很困难，我可以定义一个不需要我做的选择(猜测！)吗?
为什么在运行相同脚本时系统之间的行为会有差异?
任何帮助将不胜感激.对我来说，这似乎像VBA一样有自己的想法.
Any help would be much appreciated. For me this seems like VBA having a mind of its own.
推荐答案
如果两个系统上都保存了 key.xlsx 文件，包括在引用 Workbook时的文件扩展名隐藏扩展名设置，strong> objects是更安全的选择:
If the key.xlsx file is saved on both systems, including the file extension when referring to the Workbook objects is the safer option because of Windows hide extensions setting:
工作簿集合对象
如果隐藏扩展名设置无效(表示 扩展名确实显示在Windows中)，您必须包含 xls 您在工作簿集合中引用工作簿时的扩展名. 例如，如果您打开一个名为 Book1.xls 的工作簿，则必须使用
If the hide extensions setting is not in effect (meaning that extensions are indeed displayed in Windows), you must include the xls extension when you reference a workbook in the Workbooks collection. For example, if you have open a workbook named Book1.xls, you must use
Workbooks("Book1.xls").Activate
Workbooks("Book1.xls").Activate
而不是
Workbooks("Book1").Activate
Workbooks("Book1").Activate
引用 Book1 工作簿.上面的第二行代码，不带 xls 扩展名将失败，并显示错误9，下标超出范围， 因为没有名称为 Book1 的工作簿.如果隐藏 扩展名设置生效，您可以省略 xls 扩展名并使用 以下两行代码之一.
to refer to the Book1 workbook. The second line of code above, without the xls extension, will fail with an error 9, Subscript Out Of Range, because there is no workbook with the name Book1. If the hide extensions setting is effect, you can omit the xls extension and use either of the following lines of code.
Workbooks("Book1").Activate
Workbooks("Book1").Activate
Workbooks("Book1.xls").Activate
Workbooks("Book1.xls").Activate
这些代码行假定您未打开两个未保存的代码 名称为 Book1 的工作簿和名称为已保存的工作簿 Book1.xls .启用隐藏扩展程序设置后( 扩展名隐藏在Windows中)，上面的两行代码是 功能上等效.根据良好的编程习惯，您 在工作簿名称中应始终包含 xls 扩展名.这 确保您参考正确的工作簿，无论 隐藏扩展属性的值.
These lines of code assume that you do not have open both an unsaved workbook with the name Book1 and a saved workbook with the name Book1.xls. With the hide extensions setting enabled (so that extensions are hidden in Windows), the two lines of code above are functionally equivalent. As a matter of good programming practice, you should always include the xls extension in a workbook name. This ensures that you reference the correct workbook regardless of the value of the hide extensions property.
来自 cpearson.com 的更多详细信息，用于 查看全文
