VBA实现将execl变成execl中的sheet

Sub MergeFiles()
    Dim targetWorkbook As Workbook
    Dim targetWorksheet As Worksheet

    ' 打开目标文件
    Set targetWorkbook = Workbooks.Open("目标文件路径")

    ' 创建一个新的工作表来存储合并后的数据
    Set targetWorksheet = targetWorkbook.Sheets.Add(After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count))
    targetWorksheet.Name = "合并工作表"

    Dim fileDialog As FileDialog
    Dim selectedFiles As Variant

    ' 创建文件对话框
    Set fileDialog = Application.FileDialog(msoFileDialogFilePicker)

    ' 允许选择多个文件
    fileDialog.AllowMultiSelect = True

    ' 显示文件对话框
    If fileDialog.Show = -1 Then
        ' 获取所选文件的路径
        selectedFiles = fileDialog.SelectedItems

        ' 遍历所选文件
        For Each file In selectedFiles
            ' 处理每个文件
        Next file
    End If

    ' 遍历所选文件
    For Each file In selectedFiles
        ' 打开要合并的Excel文件
        Set sourceWorkbook = Workbooks.Open(file)

        ' 确定要复制的数据区域
        Set sourceWorksheet = sourceWorkbook.Sheets(1) ' 假设要复制第一个工作表的数据
        lastRow = sourceWorksheet.Cells(sourceWorksheet.Rows.Count, 1).End(xlUp).Row ' 获取最后一行的行号
        lastColumn = sourceWorksheet.Cells(1, sourceWorksheet.Columns.Count).End(xlToLeft).Column ' 获取最后一列的列号

        ' 复制数据到目标文件的合并工作表中
        targetWorksheet.Range("A1").Resize(lastRow, lastColumn).Value = sourceWorksheet.Range("A1").Resize(lastRow, lastColumn).Value

        ' 关闭要合并的Excel文件
        sourceWorkbook.Close SaveChanges:=False
    Next file

    ' 保存并关闭目标文件
    targetWorkbook.Close SaveChanges:=True
End Sub