VBA 工作簿合并

最新推荐文章于 2024-09-05 23:18:03 发布

sijiexia

最新推荐文章于 2024-09-05 23:18:03 发布

阅读量928

点赞数

文章标签：大数据

本文链接：https://blog.csdn.net/sijiexia/article/details/125437339

版权

本文介绍了如何使用VBA来批量合并多个Excel工作簿，包括从选定文件和整个文件夹中进行汇总，采用递归方式遍历文件夹内的所有文件。

摘要由CSDN通过智能技术生成

窗体设计如下i：

1.1、工作簿汇总（选择文件：可以选择多个文件

Sub combineworkbooks()

Dim fd As FileDialog
Dim vsi As Variant
Dim wb As Workbook
Dim sht As Worksheet
Dim tm As Variant
Dim dt As Variant
Dim n As Variant
Dim wbName As Variant
Dim wbSheetCount As Variant
Dim newSheetFullName As Variant
Dim newSheetName As Variant

Application.DisplayAlerts = False

'tm = Hour(Time) & Minute(Time) & Second(Time)
tm = Format(Time, "hhmmss")
dt = Format(Date, "yyyymmdd")


Set fd = Application.FileDialog(msoFileDialogFilePicker)      '允许用户选择文件
With fd
    .Filters.Clear                                            '清除现有文件类型
    .Title = "选择需要合并的工作簿"
    .Filters.Add "Excel文件", "*.xls;*.xlsx"                  '添加文件格式
    .AllowMultiSelect = True                                  '允许选择多个文件

    If .Show = -1 Then                                        '显示窗体，且选择文件

starttime = Timer
        
        Set wb = Workbooks.Add
        Set sht = wb.Worksheets(1)
        sht.Name = "文件目录"
        sht.Range("A1：B1") = Array("文件名", "地址(超链接)")             '表头
        wb.SaveAs ThisWorkbook.path & "\文件汇总表" & "(" & dt & "-" & tm & ")" & ".xlsx"
        wbName = "文件汇总表" & "(" & dt & "-" & tm & ")" & ".xlsx"

        n = 2
        For Each vsi In .SelectedItems                                                  '遍历选择的文件
'            sht.Range("b"