我尝试使用VBA将多个工作簿中的数据复制到一个主文件中，而不知道每个工作簿的名称

Sub CopyDataFromWorkbooks()
    Dim mainWorkbook As Workbook
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim mainWorksheet As Worksheet
    Dim filePath As String
    Dim fileName As String
    
    ' 设置主文件
    Set mainWorkbook = ThisWorkbook
    Set mainWorksheet = mainWorkbook.Sheets("Sheet1") ' 修改为您要复制数据的工作表名称
    
    ' 设置工作簿路径
    filePath = "C:\Path\To\Workbooks\" ' 修改为您存储工作簿的文件夹路径
    
    ' 循环处理工作簿
    fileName = Dir(filePath & "*.xlsx") ' 修改为您的工作簿文件类型
    Do While fileName <> ""
        ' 打开工作簿
        Set sourceWorkbook = Workbooks.Open(filePath & fileName)
        
        ' 复制数据到主文件
        Set sourceWorksheet = sourceWorkbook.Sheets("Sheet1") ' 修改为您要复制数据的工作表名称
        sourceWorksheet.UsedRange.Copy mainWorksheet.Range("A" & mainWorksheet.Cells(Rows.Count, 1).End(xlUp).Row + 1)
        
        ' 关闭工作簿
        sourceWorkbook.Close SaveChanges:=False
        
        ' 继续下一个工作簿
        fileName = Dir
    Loop
    
    ' 清理对象
    Set mainWorksheet = Nothing
    Set mainWorkbook = Nothing
    Set sourceWorksheet = Nothing
    Set sourceWorkbook = Nothing
    
    MsgBox "数据复制完成！"
End Sub