vba 我如何一一打开所有excel文件并运行宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3375175/
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
how do i open ALL the excel files one by one and run a macro
提问by l--''''''---------''''''''''''
I need to write a macro in VBA that will open every file in a given directory one by one and run a macro on them.
我需要在 VBA 中编写一个宏，它将一个一个地打开给定目录中的每个文件并在它们上运行一个宏。
so far i have something like
到目前为止，我有类似的东西
for i = 1 to number_of_files
open Dir("C:\yourPath\*.*", vbNormal)
call some_macro
close file
next i
回答by GSerg
By calling the Dir()function with an appropriate filter, such as "c:\folder\*.xls", you start enumeration and get the first file name.
After that, repeatedly calling the Dir()function without any parameters, you will get all *.xlsfile names, one for each call.
通过Dir()使用适当的过滤器（例如 ）调用该函数"c:\folder\*.xls"，您可以开始枚举并获取第一个文件名。
之后，Dir()不带任何参数重复调用该函数，您将获得所有*.xls文件名，每次调用一个。
You open a workbook by calling Workbooks.Open(full_path). This gives you a Workbookobject, against which you can run a macro.
您可以通过调用打开工作簿Workbooks.Open(full_path)。这为您提供了一个Workbook对象，您可以针对该对象运行宏。
The .Close()method of this Workbookobject closes the workbook. You can use .Close(SaveChanges:=True)to save changes, .Close(SaveChanges:=False)to discard changes, or omit the parameter to have the user decide.
.Close()此Workbook对象的方法关闭工作簿。您可以使用.Close(SaveChanges:=True)来保存更改、.Close(SaveChanges:=False)放弃更改或省略参数以让用户决定。
回答by Lance Roberts
Here's the easy VBA object way to do it:
这是执行此操作的简单 VBA 对象方法：
Dim fs As FileSearch
Dim i As Integer
Dim wbk As Workbook
Set fs = Application.FileSearch
With fs
.LookIn = ThisWorkbook.Path
.FileName = "*.xls"
For i = 1 to .Execute()
Set wbk = Workbooks.Open(.FoundFiles(i))
''//RUN MACRO HERE
wbk.Close(SaveChanges:=True)
Next i
End With
