打开对话框选择多个文件进行汇总

1、打开对话框选择文件，可以多选

2、将选中的文件进行循环，逐一打开将其中的数据汇总到总表里，然后关闭这个文件

3. 将copy来的数据在总表里向下增加

 

Sub 导入多工作簿()
Dim oWb As Object: Set oWb = ThisWorkbook
Dim intFilesCount As Integer
Dim strFilePath As String
Dim rs
Application.ScreenUpdating = False    '屏幕闪烁关闭
Set sh = ThisWorkbook.Worksheets("学籍")
sh.UsedRange.Offset(1) = Empty
Dim oFd As Object
Set oFd = Application.FileDialog(msoFileDialogFilePicker)
With oFd
    .AllowMultiSelect = True
    .Filters.Add "Excel文档", "*.csv; *.csv; *.csv", 1
    .FilterIndex = 1
    .InitialFileName = oWb.Path
    .InitialView = msoFileDialogViewDetails
   
    If .Show = -1 Then
        intFilesCount = .SelectedItems.Count
        For i = 1 To intFilesCount
            strFilePath = .SelectedItems(i)
            Set Wb = Workbooks.Open(strFilePath)
            ar = Wb.Worksheets(1).[a1].CurrentRegion.Offset(1)
            rs = sh.[a65536].End(xlUp).Row + 1
            sh.Cells(rs, 1).Resize(UBound(ar), UBound(ar, 2)) = ar
            Wb.Close False
        Next i
    Else
        Exit Sub
    End If
   
End With

Application.ScreenUpdating = True '屏幕闪烁打开

End Sub

　　使用vba将其他数据导出到excel

Dim oXl As Object: Set oXl = CreateObject("excel.application"): oXl.Visible = True'打开excel并使得可见
    Dim oBk As Object: Set oBk = oXl.Workbooks.Add'打开一个工作簿
    Dim oSht As Object: Set oSht = oBk.Worksheets.Add'打开一个工作表
    oSht.Range("a1").Resize(UBound(arr, 1) - LBound(arr, 1) + 1, UBound(arr, 2) - LBound(arr, 2) + 1) = arr'小标从0开始
    oBk.Save'保存工作簿
    oBk.Close'关闭工作簿
    oXl.Quit'退出excel程序