使用VB脚本快速将多个Excel多个Sheet下的数据归并到一个Excel

我不是女神ヾ 2022-05-09 10:42 544阅读 0赞

当遇到要将多个Excel下的数据归并时,特别是Excel数据量达到GB级别时,复制粘贴操作就显得很LOW,这种傻瓜操作要做几天几夜都没法处理完,外行人只能看Excel发叹.这时开发工具下的VB编辑器就起作用了.这里秀一波操作,便利做财务,做数据分析的大众使用.

![70][]

![70 1][]

打开huizong.xlsx,然后点击VB编辑器

![70 2][]

在空白的编辑器出输入以下代码:

Sub 合并当前目录下所有工作簿的全部工作表()
    Dim MyPath, MyName, AWbName
    Dim Wb As Workbook, WbN As String
    Dim G As Long
    Dim Num As Long
    Dim BOX As String
    Application.ScreenUpdating = False
    MyPath = ActiveWorkbook.Path
    MyName = Dir(MyPath & "\" & "*.xls")
    AWbName = ActiveWorkbook.Name
    Num = 0
    Do While MyName <> ""
    If MyName <> AWbName Then
    Set Wb = Workbooks.Open(MyPath & "\" & MyName)
    Num = Num + 1
    With Workbooks(1).ActiveSheet
    .Cells(.Range("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
    For G = 1 To Sheets.Count
    Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)
    Next
    WbN = WbN & Chr(13) & Wb.Name
    Wb.Close False
    End With
    End If
    MyName = Dir
    Loop
    Range("B1").Select
    Application.ScreenUpdating = True
    MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"
    End Sub

好了,点击F5或运行按钮,就可以将三张Excel表的数据汇总到一张huizong.xlsx表中了

![70 3][]

![70 4][]

[70]: /images/20220509/d44296b90e10465c80e607e940205de7.png
[70 1]: /images/20220509/6fa4504ada29456caf6457405a98fade.png
[70 2]: /images/20220509/118b9fbe836c4f2296ad7a63bccf15f6.png
[70 3]: /images/20220509/5b688c072c234bbeb2ac5d80cf3aae6f.png
[70 4]: /images/20220509/26c48b0580c34d9f89cb3581c7fd2bf4.png