excel - 打开多个文件并对所有打开的文件执行宏

Question

我想编写一个宏来打开 30 个结构相同的 excel 文件。

宏应该对所有文件进行操作，并从每个文件中获取结果并将其放入另一个 excel 文件中。这意味着:所有结果(值)将被复制到目标文件中。

如何编写 VBA 代码来打开多个文件？

如何从每个文件中获取结果并将它们放入我的destination.xls 文件中？

Answer 1

试试看FileSearch.LookIn Property
将该示例修改为类似的内容。 (这需要你所有的 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

你也可以试试

Workbooks("Destination.xls").Worksheets("Sheet1").Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value