宏的 VBA 打开工作簿失败“找不到（路径和文件名）。检查拼写...”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7589149/
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
VBA Open Workbook from Macro fails "(path & filename) could not be found. Check the spelling..."
提问by whitaay
I have the following code, which should simply open a workbook from a master workbook to allow me to paste some information which I will later retrieve from a database. I receive the error in the title every timeI try to open a workbook. I have used similar code before with no problem at all. The files are kept on a server away from my desktop, but the path has been correct each time. I've checked over and over again. What could be the culprit?
我有以下代码，它应该简单地从主工作簿中打开一个工作簿，以允许我粘贴一些我稍后将从数据库中检索的信息。每次尝试打开工作簿时，我都会收到标题中的错误。我以前使用过类似的代码，完全没有问题。这些文件保存在远离我桌面的服务器上，但每次路径都是正确的。我一遍又一遍地检查。罪魁祸首可能是什么？
Sub copyDealerDataToWorkbook(ByVal targetID As Integer)
Dim mainWB As Workbook
Dim directory As String
Dim fn As String
Dim aFile As Excel.Application
Set aFile = CreateObject("Excel.Application")
Set mainWB = ActiveWorkbook
directory = ActiveWorkbook.Path
'append a "\"
If InStrRev(directory, "\") < Len(directory) Then
directory = directory & "\"
End If
fn = targetID & ".xls"
aFile.Application.Visible = True
aFile.Workbooks.Open directory & fn 'ERROR ON THIS LINE
aFile.Parent.Windows(1).Visible = True
End Sub
I have also tried the following code...
我也试过下面的代码...
Sub copyDealerDataToWorkbook(ByVal targetID As Integer)
Dim foreignWB As Workbook
Dim mainWB As Workbook
Dim directory As String
Dim fn As String
Set mainWB = ActiveWorkbook
directory = ActiveWorkbook.Path
'append a "\"
If InStrRev(directory, "\") < Len(directory) Then
directory = directory & "\"
End If
fn = targetID & ".xls"
Set foreignWB = Workbooks.Open(fileName:=directory & fn) 'ERROR ON THIS LINE
End Sub
回答by Bruno Leite
First use hard code with name of file and test if is open.
首先使用带有文件名的硬代码并测试是否打开。
For sample
样品用
Workbooks.open "\Oesdfiles\Users\FName LName\Reports\test_data\monthly EDS reports\Number by dealer Per importer\test_201111-01January\YourFileHere.xls"
If Yes, your directory or fn var is wrong.
如果是，您的目录或 fn var 是错误的。
If No, try use office diagnostic in office button/ excel options/Resources/Diagnostic
如果否，请尝试在 office 按钮/excel 选项/资源/诊断中使用 office 诊断
And try again.
然后再试一次。
If not work's , your excel need's reinstall.
如果不行，你的excel需要重新安装。
[]'s
[] 的
回答by ray
If Dir(directory & fn) <> "" Then
'do the magic
Else
MsgBox "Directory and file not found:" & vbCrLf & directory & fn, vbCritical, "Invalid Directory"
End If
回答by n8.
targetID As Integer
This only works if your Excel workbook has a numeric file name without leading zeroes. Also, In this context there's no need to make targetID an Integer since you aren't iterating or performing calculations on the value, I would make it instead:
这仅适用于 Excel 工作簿的数字文件名不带前导零的情况。此外，在这种情况下，没有必要将 targetID 设为整数，因为您没有对值进行迭代或执行计算，我会改为：
targetID As String
Edit: I just realized this was posted 2 years ago. :P
编辑：我刚刚意识到这是 2 年前发布的。:P
