文科生也能学会的Excel VBA 宏编程入门（三）——合并文件

2022-09-02 18:17:50 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

任务介绍

在日常工作中，我们经常会遇到需要汇总多个表格的数据，将它们合并到一个表格里的情况。虽然复制粘贴大法好，但如果让你汇总几十人填报的个人信息并做成汇总表格，估计你也膜不动了。因此，这一次我们就通过VBA程序完成这个任务，从此妈妈再也不担心我数数到头秃。

程序基本思路

  1. 将要合并的Excel文件放到同一个文件夹中；
  2. 在文件夹中新建一个Excel文件用于汇总并运行VBA程序；
  3. 通过VBA程序获取这个文件夹中所有文件的路径；
  4. 依次通过程序自动打开各个文件，并将数据复制粘贴到汇总表中。

VBA编程

  1. 文件目录如下：
  1. 其中1.xlsx和2.xlsx的内容如下：
  1. 打开“合并.xlsm”文件，依次点击【开发工具】→【Visual Basic】，【右键】【插入模块】进入编程页面。
  1. 编写如下程序：
代码语言：javascript复制
Sub 合并当前目录下所有工作簿的全部工作表()
Dim filePath, fileName, thisName
Dim wb, cwb As Workbook
Dim WbN As String
Dim G As Long
Dim Num As Long
Dim firstFile As Boolean '用于判断是否第一个文件，第一个文件需要把表头也复制，而后面的不需要
firstFile = True

Application.ScreenUpdating = False '关闭屏幕刷新，这样频繁开关excel文件可以提高速度

filePath = ActiveWorkbook.Path     '合并文件所在文件夹
fileName = Dir(filePath & "" & "*.xlsx") '给Dir函数传入一个路径通配符，它就还你一个符合的文件路径。其中*为通配符，代表任意字符，例如：C:*.xlsx，也就是C盘根目录下的所有xlsx文件

thisName = ActiveWorkbook.Name '合并文件的文件名

Set cwb = ActiveWorkbook   '记录下当前激活的excel文件，也就是合并文件。因为后面会同时打开多个excel文件，先记录下来程序才不会弄混不同的文件
cwb.ActiveSheet.UsedRange.Clear '将合并文件的内容清空，还你一个清清白白的汇总表

Num = 0
Do While fileName <> "" '判断是否文件夹里的文件都遍历完了
    If fileName <> thisName Then  '如果该文件不是我们的合并汇总文件，那么就一定是需要合并的文件了
        Set wb = Workbooks.Open(filePath & "" & fileName) 'Open函数用于打开这个文件，并用wb这个变量记住它，免得程序找不到
        Num = Num   1
        
            
        For G = 1 To Sheets.Count '从第一个sheet循环到最后一个sheet，这样文件有多个sheet也能合并到汇总文件的相应sheet里面
            If cwb.Sheets.Count < G Then '如果汇总表里sheets数量不够就添加一个
                cwb.Sheets.Add after:=cwb.Sheets(G - 1)
            End If
            
        
            With cwb.Sheets(G) 'with 表达式...end with 就是个偷懒小技巧，...部分可以用“.”表示“表达式.”。所以下面“.Cells”相当于“cwb.Sheets(G)”
                If firstFile Then
                    wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row   1, 1)    'copy函数之前说过了就不再说了，这里usedRange是指sheet中所有用过的单元格，“End(xlUp)”后面再说
                Else
                    wb.Sheets(G).Rows(2 & ":" & wb.Sheets(G).Range("A65536").End(xlUp).Row).Copy .Cells(.Range("A65536").End(xlUp).Row   1, 1)
                End If
            End With
        Next
        
        WbN = WbN & Chr(13) & wb.Name '记录一下合并的过的文件的名字，用于最后弹提示框用
        
        firstFile = False '让它等于False，这样下一个文件就不会复制表头了
        wb.Close False '关闭Excel文件并不保存，用True就是关闭并保存
            
        
    End If
    fileName = Dir '这里不给Dir函数传参数会自动使用上面传过的参数，并返回符合要求的下一个文件路径
    
Loop
Range("A1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"
End Sub
  • 下面对一些新内容进行特别说明，首先filePath = ActiveWorkbook.Path，这个其实大家应该能猜到，就是获取当前激活的excel文件所在路径，对于本文就是”合并.xlsm”文件的文件夹路径，我是这个：D:Sync文档合并文件。
  • fileName = Dir(filePath & "" & "*.xlsx")其实就是fileName = Dir("D:Sync文档合并文件*.xlsx")，*是通配符，那么符合的文件路径不就是D:Sync文档合并文件1.xlsx和D:Sync文档合并文件2.xlsx嘛。Dir函数会按文件在文件夹中的实际排序依次返回这两个文件的文件名，也是说你第一次调用Dir("D:Sync文档合并文件*.xlsx")的时候返回1.xlsx，后面直接调用Dir会返回2.xlsx，如果文件夹里还有xlsx文件的话，再调用一次Dir又会继续返回下一个。如果所有文件都遍历一遍了，那么Dir会返回""。所以我们可以用""来判断是否到最后一个文件了。但是，如果你调用Dir("D:Sync文档合并文件*.xlsx")又会重新开始。
  • Set cwb = ActiveWorkbook里面用到了Set这个语句，Set其实是VBA里的赋值语句，正常情况下基本数据类型赋值也应该是Set a=1这样，但是基本数据类型可以省略Set。而这里ActiveWorkbook是一个对象类型，所以不能省略Set。至于什么是对象，什么是基本数据类型，那就说来话长了。。。可以简单理解为对象是一堆基本数据类型捆绑在一起之后的高级数据类型。这里用cwb这个变量记录下当前激活的workbook是为了后面能让程序找到它，不然后面打开多个excel之后，程序怎么知道我要操作哪一个？而Set cwb = ActiveWorkbook之后cwb这个变量就等价于当前激活的这个Excel文件了，也就是汇总文件，即便后面它的激活状态被其他新打开的文件抢走了，cwb依旧等价于原来这个。
  • Set wb = Workbooks.Open(filePath & "" & fileName)，这里也类似，让wb这个变量指向新打开的Excel文件。filePath & "" & fileName里&是字符串拼接，拼接起来第一次是D:Sync文档合并文件1.xlsx，第二次是什么相信大家都知道，毕竟后面fileName被重新赋值了。
  • Sheet1.Range("A65536").End(xlUp).Row，这个估计是本程序里最难理解的部分，如果不想去理解那就记住，这个语句代表的就是sheet1中使用过的最后一行的行号。因为我们要在汇总文件里依次拼接内容，因此就需要知道当前最后一行在哪，免得覆盖了已有的内容。下面我解释一下具体机理，可以参考这篇文章。Range("A65536")大家都知道，A列第65536行的意思嘛，这里其实取巧了，认为表格不会多于65536行。然后End(xlUp)是从65536行开始向上Up寻找第一个不为空的行，.Row就是获取这行的行号。相应的，还有End(xlDown)，End(xlToLeft)这种亲戚。
  • 获取最后一行行号的方法有很多，各有优缺点，可以参考这篇文章。
  1. 在用于汇总的Excel文件里点击绿色小三角运行宏，或者点击【宏】找到这个宏并执行都可以运行这段程序。写在【模块】里的宏没有ThisWorkbook前缀。
  1. 结果如下：

其中第一行会有一个空行，可以事后删掉，或者你们自己想办法改改程序来解决吧！加几个逻辑判断就好了。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/139740.html原文链接：https://javaforall.cn

vba

0 人点赞