VBA - 尝试打开文件夹中的所有工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21583678/
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 - Trying to open all workbooks in a folder
提问by user3276264
I'm trying to loop through and open all files in a folder named (BU) located in the same directory as the sheet where my macro is. I am able to see the myfile get the first file name correctly, but I am getting a run time error 1004 when the workbook tries to open. Any help would be appreciated.
我正在尝试遍历并打开名为 (BU) 的文件夹中的所有文件，该文件夹与我的宏所在的工作表位于同一目录中。我能够看到 myfile 正确获取第一个文件名，但是当工作簿尝试打开时出现运行时错误 1004。任何帮助，将不胜感激。
Sub LoopAndOpen()
Dim myfile As String, Sep As String, stringA As String, path1 As String
Sep = Application.PathSeparator
path1 = ActiveWorkbook.Path & Sep & "BU" & Sep
myfile = Dir(path1 & "*.xlsm")
Do While myfile <> ""
Workbooks.Open myfile
myfile = Dir()
Loop
End Sub
Edit: I ended up using Unicco's procedure and it worked perfectly.
编辑：我最终使用了 Unicco 的程序，并且效果很好。
回答by Unicco
You can use this procedure instead.
您可以改用此过程。
Modify "ThisWorkbook.Path" and ".xlsm" to your desired purpose. Use InStr(objFile, ".xlsm") Or InStr(objFile, ".xlsx") if you want to open both standard aswell as Excelfiles with macros.
将“ThisWorkbook.Path”和“.xlsm”修改为您想要的目的。如果要使用宏打开标准文件和 Excel 文件，请使用 InStr(objFile, ".xlsm") 或 InStr(objFile, ".xlsx")。
Option Explicit
Sub OpenAllFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)
For Each objFile In objFolder.Files
If InStr(objFile, ".xlsm") Then
Workbooks.Open (objFile)
End If
Next
End Sub
回答by Tim Williams
Dir()only returns the file name, not the full path: you need to pass the full path to Open()unless the current directory happens to be the one you're searching through. It's best never to rely on that being the case.
Dir()只返回文件名，而不是完整路径：Open()除非当前目录恰好是您正在搜索的目录，否则您需要将完整路径传递给。最好永远不要依赖这种情况。
