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程序
标签:文件,arr,Set,oBk,Dim,Object,汇总,对话框,oXl From: https://www.cnblogs.com/dogingate/p/16636871.html