vba 错误 1004 - 无法打开文件。当我尝试打开文件夹中的工作簿时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21123531/
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
Error 1004 -Cannot open file . when I try to open workbooks in a folder
提问by Doldrums
Alright I am trying to extract data from several workbooks in a folder and put them all in one file. When the code hits Workbooks.Open, error 1004 pops up and I am sure that the files are not corrupted.
好吧，我正在尝试从文件夹中的几个工作簿中提取数据并将它们全部放在一个文件中。当代码点击 Workbooks.Open 时，会弹出错误 1004，我确定文件没有损坏。
Sub Extract_Class_Data()
' Extract_ERP_Class_Data Macro
' Goes into all the files in the folder and extracts the data from each of them.
Dim MyFile As String
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim sourceSheetSum As Worksheet
Set sourceBook = ActiveWorkbook
Set sourceSheet = sourceBook.Sheets("Sheet1")
Dim erow
Dim Filepath As String
Filepath = ThisWorkbook.Path
MyFile = Dir(Filepath & "\*.xlsx")
Do While Len(MyFile) > 0
If MyFile = "ZZZ.xlsm" Then
Exit Sub
End If
**Workbooks.Open (MyFile)**
Worksheets(Data).Activate
Range("B6:B12").Select
Selection.Copy
ActiveWorkbook.Close savechanges:=False
sourceSheet.Select
ActiveSheet.Paste
Range("B1").Select
ActiveCell.Offset(0, 1).Select
MyFile = Dir
Loop
End Sub
采纳答案by Joe
This works:
这有效：
Public Sub openaworkbook()
Dim filen As String, filepath As String, myfile As String
filen = "temp.xlsx"
filepath = "c:\temp\"
myfile = Dir(filepath & filen)
Workbooks.Open (filepath & myfile)
End Sub
You need filepath in the open command, as DIRdoes not return the path, only the filename.
您需要在 open 命令中使用 filepath ，因为DIR不返回路径，只返回文件名。
