Excel多个文件批量使用一个宏以及合并汇总多个Excel文件

分配到一个任务,从某管理系统中将出操人数不到10次的学生找出来做成Excel,再将每个班的名单做成Excel。本来很简单的工作,结果发现系统只能一个班一个班的选择一下再手动把数据导出,也就是说不能全部一下所有学生数据导出来,很多其他数据处理功能也是形同虚设,我也没有数据库的权限,好吧我就一个一个导了两百个班的数据,手快抽筋啦。

导航目录

  1. Excel多个文件批量使用一个宏
  2. 合并汇总多个Excel文件

Excel多个文件批量使用一个宏

关键问题来了,我将每个班的Excel文件筛选一下少于十次的名单大概需要20秒,这里有两百个班的数据,我按这速度不间断的操作也得一个多小时,天呐!

机智的我一下就想到了简便方法,果断录制一个宏,然后打开每个文件应用一下宏,这样大概也许哦不,好像也得半个小时。机智的我又灵机一动,写个宏脚本将自动将每个文件应用一下筛选的操作,应该用不了3分钟吧。等等,好像忘记了我不会写excel的宏耶!!不要紧,百度谷歌一套打出,宏脚本赫然出现在眼前:

Sub 多个文件应用宏()
'
' 选择一个目录对每个文件应用宏

    Dim Path$, File$
    Dim Wb As Workbook, sht As Worksheet
    Application.DisplayAlerts = False
    With Application.FileDialog(msoFileDialogFolderPicker)

        If .Show Then

            Path = .SelectedItems(1)
            File = Dir(Path & "\*.xls")
            Application.EnableEvents = False
            Application.Calculation = xlCalculationManual
            Do Until LenB(File) = 0

                Set Wb = Workbooks.Open(Filename:=Path & "\" & File)
                '打开路径下的文件

                For Each sht In Wb.Worksheets

        '下面学生表一改成自己的表名
                    If sht.Name = "学生表一" Then

                        'TO DO SOMETHING,这里填写每个Excel工作薄文件要操作的宏,不会写可以录制一个宏,然后把录制的宏的代码填在这里,具体可以看后面

                    End If
                  Next
                Wb.SaveAs Filename:=Path & "\" & File
                '保存替换原文件

                   Wb.Close True
                   '关闭文件

                File = Dir
            Loop
            Application.DisplayAlerts = True
            '关闭提示,与前面的FALSE对应
            Application.EnableEvents = True
            Application.Calculation = xlCalculationAutomatic
        End If
    End With
End Sub

身为半个程序猿,excel的vba宏还真不会写咧。所以这里要把对一个文件的操作(我这里就是筛选十次以下名单的操作)录制成一个宏,然后查看一下录制的宏的代码,然后把代码填到上面代码该填的地方去,再运行一下上面的代码,就会弹出一个文件夹框,选择一个文件夹后,就会对该文件夹下面所有的excel文件执行我们刚刚录制好的操作(宏)了。下面是具体操作:

  • 打开要操作的excel文件之一,点击视图->宏->录制宏

    01.png

  • 输入一个宏的名字,选择宏的保存位置和快捷键,注意:如果保存在个人宏工作薄,那么这个宏在所有文件里都可以应用,保存在当前工作薄则只在当前Excel工作薄文件有效,保存在新工作薄就是应用宏在一个新建工作薄。我只是为了要刚才那些操作对应的宏代码,所以这里保存位置无所谓咯
    02.png

  • 然后进行你要的操作,比如我要筛选出出操十次以下的名单(当然你的操作这里肯定不一样)
    03.png
    04.png

  • 等操作完成后,点击视图->宏->停止录制

  • 好了录制完成,接着按ALT+F11进入Microsoft Visual Basic For Application(VBA)编辑界面,也可以通过视图->宏->查看宏->编辑 进入该界面这里应该会出现刚才录制的宏的代码:
    05.png

  • 把Sub和End Sub两行中间所有的代码拷贝到的上面多个文件应用宏的代码中TO DO SOMETHING那条注释的下一行,然后关闭这个要操作之一的excel文件并且不要保存更改,不然后面再批处理一次,这个文件就要重复操作两次了:
    06.png

  • 然后桌面或者其他位置新建一个Excel文件,打开、按Alt+F11进入VBA界面,点击菜单栏插入->模块,然后复制之前整理好的代码进去:
    07.png

  • 再点击菜单栏运行->运行子程序:
    08.png

  • 接着会弹出一个文件夹选择框,选择一个文件夹后将对文件夹下面所有的excel文件进行我们之前录制的操作,喝口水等宏脚本执行完就收工咯。

合并汇总多个Excel文件

其实最后任务还要把所有excel文件汇总到一个excel文件的,坑爹的某管理系统哟。参照上面的经验,网上搜索一下,一大片汇总多个文件的宏代码出现在眼前,貌似这个用得有点多,找了个能用的:

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

这里就参照上面的步骤应用宏脚本合:

  1. 把需要合并(汇总)的各个文件放在一个文件夹下面,当然每个工作薄表格格式都应该是一样的;
  2. 然后在该文件夹下面新建一个excel文件,打开之;
  3. 然后再像之前那样,按Alt+F11进入VBA界面,点击插入->模块;
  4. 然后粘贴上面的代码,再然后按F5(或者点击运行->运行子过程);
  5. 然后再喝杯茶,之后文件就汇总合并到新建的excel文件里咯。

一些细节:
出现“无法在未启用的工作薄中保存以下功能:VB项目”的提示框:点击否,另存为“启用宏的工作薄(*.xlsm)”格式
出现“不能在隐藏工作薄中编辑宏”:点击excel菜单栏的视图->窗口->取消隐藏窗口

标签: excel, , 合并文件

已有 4 条评论

  1. 李磊 李磊

    大师级的,很好很实用

  2. 李磊 李磊

    大师级的,很好很实用

  3. jell jell

    如果是csv的怎么办,请问

  4. Simon Simon

    多谢,正在找类似的脚步

添加新评论