使用Excel VBA高效地将多个电子表格导入到一个主表中

Sub ImportData()
    Dim mainSheet As Worksheet
    Dim sourceSheet As Worksheet
    Dim sourceBook As Workbook
    Dim fileDialog As FileDialog
    Dim selectedFiles As Variant
    Dim lastRow As Long
    Dim i As Long
    
    ' 设置主表
    Set mainSheet = ThisWorkbook.Sheets("主表")
    
    ' 打开文件对话框，选择要导入的电子表格文件
    Set fileDialog = Application.FileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    If fileDialog.Show = -1 Then
        selectedFiles = fileDialog.SelectedItems
    Else
        Exit Sub
    End If
    
    ' 循环导入每个选中的文件
    For i = LBound(selectedFiles) To UBound(selectedFiles)
        ' 打开选中的文件
        Set sourceBook = Workbooks.Open(selectedFiles(i))
        ' 获取源表
        Set sourceSheet = sourceBook.Sheets(1)
        ' 获取源表最后一行
        lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row
        ' 将源表数据复制到主表
        sourceSheet.Range("A1").CurrentRegion.Copy mainSheet.Cells(mainSheet.Cells(mainSheet.Rows.Count, 1).End(xlUp).Row + 1, 1)
        ' 关闭源表
        sourceBook.Close False
    Next i
    
    ' 清理对象
    Set mainSheet = Nothing
    Set sourceSheet = Nothing
    Set sourceBook = Nothing
    Set fileDialog = Nothing
    
    MsgBox "导入完成！"
End Sub