Excel-VBA:从文件名以"xxx“开头的多个已关闭工作簿中提取数据

Sub ExtractDataFromClosedWorkbooks()
    Dim FilePath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim TargetWorkbook As Workbook
    Dim TargetWorksheet As Worksheet
    Dim LastRow As Long
    
    ' 设置文件路径和文件名
    FilePath = "C:\Path\To\Files\" ' 替换为实际的文件路径
    FileName = Dir(FilePath & "xxx*.xlsx") ' 替换为以"xxx"开头的文件名
    
    ' 打开目标工作簿
    Set TargetWorkbook = Workbooks.Add
    
    ' 循环处理所有符合条件的文件
    Do While FileName <> ""
        ' 打开已关闭的工作簿
        Set wb = Workbooks.Open(FilePath & FileName, ReadOnly:=True)
        
        ' 提取数据
        Set ws = wb.Sheets(1) ' 假设数据在第一个工作表中
        LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row ' 获取最后一行
        Set TargetWorksheet = TargetWorkbook.Sheets(1) ' 假设将数据复制到目标工作簿的第一个工作表中
        ws.Range("A1:C" & LastRow).Copy Destination:=TargetWorksheet.Cells(TargetWorksheet.Rows.Count, 1).End(xlUp).Offset(1, 0) ' 复制数据到目标工作簿
        
        ' 关闭已关闭的工作簿
        wb.Close SaveChanges:=False
        
        ' 获取下一个符合条件的文件名
        FileName = Dir
    Loop
    
    ' 保存目标工作簿
    TargetWorkbook.SaveAs "C:\Path\To\TargetWorkbook.xlsx" ' 替换为实际的保存路径和文件名
    
    ' 关闭目标工作簿
    TargetWorkbook.Close SaveChanges:=False
    
    ' 清除对象引用
    Set TargetWorksheet = Nothing
    Set TargetWorkbook = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub