VBA：打开多个文件并对所有打开的文件执行宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16318876/
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 multiple Files and perform macro on all opened Files
提问by i.abdulo
I want to write a macro that will open 30 excel files which all have the same structure.
我想编写一个宏来打开 30 个具有相同结构的 excel 文件。
The macro should do operation on all files, and take the results from every file and put it in another excel file.That means: all Results (Values) will be copied into the destination file.
宏应该对所有文件进行操作，并将每个文件的结果放入另一个excel文件中。这意味着：所有结果（值）都将被复制到目标文件中。
How do I write VBA code to open multiple files?
How do I take the results from every file and place them in my destination.xls file?
如何编写 VBA 代码来打开多个文件？
如何从每个文件中获取结果并将它们放在我的 destination.xls 文件中？
回答by Ash
Try checking out FileSearch.LookIn Property
modify that example to something similar. (This would require that all your 30 files are in one folder though)
尝试查看FileSearch.LookIn 属性
，将该示例修改为类似的内容。（不过，这将要求所有 30 个文件都在一个文件夹中）
Dim WrkBook as Workbook
Set fs = Application.FileSearch
With fs
.SearchSubFolders = False
.LookIn = "C:\My Documents" 'Path of folder to search
.FileName = "*.xls" 'Limit to excel files
If .Execute > 0 Then
Debug.Print "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
WrkBook.Worksheets(1).Select
ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
Next i
Else
Debug.print "There were no files found."
End If
End With
You could also try
你也可以试试
Workbooks("Destination.xls").Worksheets("Sheet1").Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
回答by Fandango68
I think you're after a solution, that requires the user to choose the files to open and process?
我认为您正在寻求解决方案，即要求用户选择要打开和处理的文件？
Try this...
尝试这个...
Option Explicit
Sub opening_multiple_file()
Dim i As Integer
'Opening File dialog box
With Application.FileDialog(msoFileDialogFilePicker)
'Enabling multiple files select
.AllowMultiSelect = True
.Filters.Clear
'Only Excel files can be selected
.Filters.Add "Excel Files", "*.xls*"
If .Show = True Then
For i = 1 To .SelectedItems.Count
'Opening selected file
Workbooks.Open .SelectedItems(i)
'etc do other things with it
Next i
End If
End With
End Sub
