VBA遍历文件夹以从某个工作表的多个工作簿中获取数据，但工作表名称在不同的工作簿中有所不同

Sub GetDataFromMultipleWorkbooks()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim targetWorkbook As Workbook
    Dim targetWorksheet As Worksheet
    Dim targetRange As Range
    
    ' 设置目标工作簿和工作表
    Set targetWorkbook = ThisWorkbook ' 当前工作簿
    Set targetWorksheet = targetWorkbook.Sheets("目标工作表名称")
    Set targetRange = targetWorksheet.Range("目标数据范围")
    
    ' 创建FileSystemObject对象
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' 获取文件夹对象
    Set folder = fso.GetFolder("文件夹路径")
    
    ' 遍历文件夹中的文件
    For Each file In folder.Files
        ' 检查文件扩展名是否为Excel文件
        If fso.GetExtensionName(file.Path) = "xls" Or fso.GetExtensionName(file.Path) = "xlsx" Then
            ' 打开工作簿
            Set wb = Workbooks.Open(file.Path)
            
            ' 根据工作表名称获取工作表对象
            On Error Resume Next
            Set ws = wb.Sheets("工作表名称")
            On Error GoTo 0
            
            ' 检查工作表是否存在
            If Not ws Is Nothing Then
                ' 复制数据到目标工作表
                Set dataRange = ws.Range("数据范围")
                dataRange.Copy targetRange
                
                ' 调整目标数据范围
                Set targetRange = targetRange.Offset(dataRange.Rows.Count)
                
                ' 关闭工作簿
                wb.Close False
            End If
        End If
    Next file
    
    ' 清理对象
    Set targetRange = Nothing
    Set targetWorksheet = Nothing
    Set targetWorkbook = Nothing
    Set dataRange = Nothing
    Set ws = Nothing
    Set wb = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub