打开多个子文件夹并将其中的所有excel文件复制到另一个文件夹- VBA

Sub CopyExcelFiles()
    Dim sourceFolder As String
    Dim destinationFolder As String
    Dim fso As Object
    Dim sourcePath As Object
    Dim destinationPath As Object
    Dim file As Object
    
    ' 设置源文件夹和目标文件夹的路径
    sourceFolder = "C:\SourceFolder"
    destinationFolder = "C:\DestinationFolder"
    
    ' 创建文件系统对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 获取源文件夹的路径
    Set sourcePath = fso.GetFolder(sourceFolder)
    
    ' 遍历源文件夹中的所有文件
    For Each file In sourcePath.Files
        ' 检查文件是否为Excel文件
        If InStr(1, file.Name, ".xls") > 0 Or InStr(1, file.Name, ".xlsx") > 0 Then
            ' 构建源文件和目标文件的完整路径
            Set sourcePath = fso.GetFile(file.Path)
            Set destinationPath = fso.BuildPath(destinationFolder, file.Name)
            
            ' 复制文件
            sourcePath.Copy destinationPath
        End If
    Next file
    
    ' 释放对象
    Set fso = Nothing
    Set sourcePath = Nothing
    Set destinationPath = Nothing
    Set file = Nothing
    
    MsgBox "所有Excel文件已成功复制到目标文件夹。"
End Sub