VBA Excel如何自动将多个文件与一个主文件进行比较，然后复制/粘贴结果

Sub CompareFiles()
    Dim mainWorkbook As Workbook
    Dim mainWorksheet As Worksheet
    Dim mainRange As Range
    Dim compareWorkbook As Workbook
    Dim compareWorksheet As Worksheet
    Dim compareRange As Range
    Dim resultWorkbook As Workbook
    Dim resultWorksheet As Worksheet
    Dim resultRange As Range
    Dim mainFilePath As String
    Dim compareFilePath As String
    
    ' 设置主文件路径
    mainFilePath = "主文件路径.xlsx"
    
    ' 打开主文件
    Set mainWorkbook = Workbooks.Open(mainFilePath)
    Set mainWorksheet = mainWorkbook.Worksheets("Sheet1") ' 设置主文件的工作表名和索引
    
    ' 设置结果文件路径
    resultFilePath = "结果文件路径.xlsx"
    
    ' 创建结果文件
    Set resultWorkbook = Workbooks.Add
    Set resultWorksheet = resultWorkbook.Worksheets("Sheet1") ' 设置结果文件的工作表名和索引
    
    ' 遍历需要比较的文件
    compareFilePath = Dir("需要比较的文件路径\*.xlsx") ' 设置需要比较的文件所在文件夹路径
    
    Do While compareFilePath <> ""
        ' 打开需要比较的文件
        Set compareWorkbook = Workbooks.Open("需要比较的文件路径\" & compareFilePath)
        Set compareWorksheet = compareWorkbook.Worksheets("Sheet1") ' 设置需要比较的文件的工作表名和索引
        
        ' 比较数据并复制/粘贴结果
        ' 这里可以根据具体需求编写比较逻辑，例如使用循环遍历每个单元格进行比较
        
        ' 关闭需要比较的文件
        compareWorkbook.Close SaveChanges:=False
        
        ' 继续下一个文件
        compareFilePath = Dir
    Loop
    
    ' 保存并关闭主文件
    mainWorkbook.Close SaveChanges:=True, Filename:=mainFilePath
    
    ' 保存结果文件
    resultWorkbook.SaveAs resultFilePath
    
    ' 关闭结果文件
    resultWorkbook.Close SaveChanges:=False
End Sub