vba 在已知文件位置使用 Workbooks.Open 会产生“找不到运行时错误 1004 <文件>”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21007683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Workbooks.Open on a known file location yields "Run-time error 1004 <file> could not be found"
提问by Instant Breakfast
I am building a utility to grab data from specific files and transfer that data to another file. The source files all have the same naming convention which includes an identical string that can be used to differentiate them from other files in a directory. The intent is to use that string with wildcards to open the file and pull the required data.
我正在构建一个实用程序来从特定文件中获取数据并将该数据传输到另一个文件。源文件都具有相同的命名约定，其中包括一个相同的字符串，可用于将它们与目录中的其他文件区分开来。目的是使用带有通配符的字符串来打开文件并提取所需的数据。
I thought I had Workbook.Openworking with wildcards yesterday, after consulting this StackOverflow postand this entry in the Office Dev Center, but today I try running my test code and VBA claims not to find the file (though the text suggests otherwise).
Workbook.Open在查阅了这篇 StackOverflow 帖子和Office Dev Center 中的这个条目后，我以为我昨天使用了通配符，但今天我尝试运行我的测试代码，VBA 声称找不到该文件（尽管文本另有说明）。
There is not much to go wrong in my test code:
我的测试代码没有太多错误：
Dim strFilePath As String
strFilePath = "C:\...\KMMacros\DeepLinkTransferFromSabaForm\"
Dim strFileName As String
strFileName = "*SabaEntryForm.xlsx"
Workbooks.Open FileName:=Dir$(strFilePath & strFileName), ReadOnly:=True
The error reads
错误读取
'TEST_KM6.7-3_BRSTA_SabaEntryForm.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.
找不到“TEST_KM6.7-3_BRSTA_SabaEntryForm.xlsx”。检查文件名的拼写，并验证文件位置是否正确。
and debug highlights
和调试亮点
Workbooks.Open FileName:=Dir$(strFilePath & strFileName), ReadOnly:=True
Because the error specifically mentions the file I expected to open, and because my code does not give the specific file name, it seems like VBA has found the file, even though the error states otherwise.
因为错误特别提到了我希望打开的文件，并且因为我的代码没有给出特定的文件名，所以 VBA 似乎已经找到了该文件，即使错误说明不然。
How can VBA be finding the name of the file and then claim that it cannot find it? What am I doing wrong?
VBA 如何找到文件的名称，然后声称找不到它？我究竟做错了什么？
回答by Tim Williams
Dir()only returns the filename, not the whole path, so if your current directory is different from the folder you pass to Dir then you will have this problem.
Dir()只返回filename，而不是整个路径，所以如果你的当前目录与你传递给 Dir 的文件夹不同，那么你就会遇到这个问题。
Dim strFilePath As String
strFilePath = "C:\...\KMMacros\DeepLinkTransferFromSabaForm\"
Dim strFileName As String
strFileName = "*SabaEntryForm.xlsx"
Workbooks.Open FileName:=strFilePath & Dir$(strFilePath & strFileName), _
ReadOnly:=True
