Excel·VBA合并工作簿2

这篇具有很好参考价值的文章主要介绍了Excel·VBA合并工作簿2。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

  • 其他合并工作簿的方法,见之前的文章《Excel·VBA合并工作簿》

8,合并文件夹下所有工作簿中所有工作表,按表头汇总

8.1,打开工作簿方法

与之前的文章《Excel·VBA合并工作簿(3,合并文件夹下所有工作簿中所有工作表)》类似,但是按照表头名称,将表格数据汇总至合并表格,表头名称相同的在同一列

Sub 合并文件夹下所有工作簿中所有工作表_按表头汇总()
    '文件夹下所有工作簿wb所有工作表ws合并保存至新建工作表(但不含子文件夹),并按表头汇总数据,默认只有1行表头
    Dim dict As Object, fso As Object, write_ws As Worksheet, wb As Workbook, sht As Worksheet
    Dim write_row&, write_col&, sht_row&, file_path$, file_name$, old_name As Boolean, arr, i&, k
'--------------------参数填写:file_path,待合并工作簿所在的文件夹;old_name
    file_path = "E:\测试\拆分表\合并工作簿8\"
    file_name = Dir(file_path & "*.xlsx")
    old_name = True    '写入原工作簿、工作表名称,是/否
    Application.ScreenUpdating = False  '关闭屏幕更新,加快程序运行
    Application.DisplayAlerts = False   '不显示警告信息
    Set dict = CreateObject("scripting.dictionary"): tm = Timer
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Do While file_name <> ""
        Set wb = Workbooks.Open(file_path & file_name)
        For Each sht In wb.Worksheets
            If WorksheetFunction.CountA(sht.UsedRange.Cells) <> 0 Then  '非空工作表
                If write_ws Is Nothing Then
                    sht.Copy: Set write_ws = ActiveSheet  '整体复制工作表
                    write_ws.Name = "合并表": write_ws.Columns("a:b").Insert '插入列
                    write_ws.[a1].Resize(1, 2) = Array("原工作簿名称", "原工作表名称")
                    write_row = write_ws.UsedRange.Rows.Count
                    write_ws.[a2].Resize(write_row - 1, 2) = Array(fso.GetBaseName(file_name), sht.Name)
                    write_col = write_ws.UsedRange.Columns.Count: arr = write_ws.[a1].CurrentRegion
                    For i = 1 To UBound(arr, 2)
                        dict(arr(1, i)) = i  '记录表头名称及列号
                    Next
                Else
                    write_row = write_ws.UsedRange.Rows.Count + 1
                    sht_row = sht.UsedRange.Rows.Count: arr = sht.[a1].CurrentRegion
                    For i = 1 To UBound(arr, 2)
                        k = arr(1, i)
                        If Not dict.Exists(k) Then  '表头不存在,更新至列号+1,复制表头
                            write_col = write_col + 1: dict(k) = write_col
                            sht.Cells(1, i).Copy write_ws.Cells(1, write_col)
                        End If
                        sht.Cells(2, i).Resize(sht_row - 1, 1).Copy write_ws.Cells(write_row, dict(k))
                    Next
                    write_ws.Cells(write_row, "a").Resize(sht_row - 1, 2) = Array(fso.GetBaseName(file_name), sht.Name)
                End If
            End If
        Next
        wb.Close (False)  '关闭工作簿
        file_name = Dir   '下一个文件名
    Loop
    '保存文件
    If Not old_name Then write_ws.Columns("a:b").Delete  '无需写入原工作簿、工作表名称
    write_ws.Parent.SaveAs filename:=file_path & "合并表.xlsx"
    write_ws.Parent.Close (False)
    Application.ScreenUpdating = True: Application.DisplayAlerts = True
    Debug.Print "文件夹合并完成,用时:" & Format(Timer - tm, "0.00")
End Sub

举例

  • 共5个工作簿13个工作表,并且改变了C、D列的顺序
    Excel·VBA合并工作簿2,# Excel,VBA,excel,vba,纵向合并,横向合并,多表合并
    Excel·VBA合并工作簿2,# Excel,VBA,excel,vba,纵向合并,横向合并,多表合并
    Excel·VBA合并工作簿2,# Excel,VBA,excel,vba,纵向合并,横向合并,多表合并
  • 合并结果
    Excel·VBA合并工作簿2,# Excel,VBA,excel,vba,纵向合并,横向合并,多表合并

8.2,SQL方法

与之前的文章《Excel·VBA使用ADO合并工作簿》合并工作簿的速度提升了数倍,那么是否可以使用同样的方法解决本问题?

Sub 合并文件夹下所有工作簿中所有工作表_按表头汇总ADO()
    '不打开工作簿方法;title_str中的表头必须每个工作表都包含,否则报错
    Dim cnn As Object, rs As Object, fso As Object, write_ws As Worksheet, old_name As Boolean
    Dim title_str$, write_row&, file_path$, file_name$, s$, ss$, ws, wss, sqlstr$, trr
'--------------------参数填写:file_path,待合并工作簿所在的文件夹;old_name;title_str
    file_path = "E:\测试\拆分表\合并工作簿8\"
    file_name = Dir(file_path & "*.xlsx")
    old_name = True    '写入原工作簿、工作表名称,是/否
    title_str = "序号,姓名,worksheet,属地"  '需要的表头名称及顺序,用英文逗号分隔
    Application.ScreenUpdating = False  '关闭屏幕更新,加快程序运行
    Set fso = CreateObject("Scripting.FileSystemObject"): delimiter = Chr(28): tm = Timer
    Set cnn = CreateObject("ADODB.Connection"): Set rs = CreateObject("ADODB.Recordset")
    
    Do While file_name <> ""
        cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended properties='Excel 12.0 Xml;Hdr=yes;IMEX=1';data source=" & file_path & file_name
        Set rs = cnn.OpenSchema(20): ss = ""
        Do Until rs.EOF  '获取所有工作表名称
            If rs.Fields("TABLE_TYPE") = "TABLE" Then
                s = Replace(rs("TABLE_NAME").Value, "'", "")
                If Right(s, 1) = "$" Then s = Left(s, Len(s) - 1): ss = ss & delimiter & s
            End If
            rs.MoveNext
        Loop
        rs.Close: wss = Split(Mid(ss, 2), delimiter): sqlstr = ""  '工作表名称数组
        For Each ws In wss
            sqlstr = sqlstr & " union all select " & title_str & " FROM [" & ws & "$]"
        Next
        sqlstr = Mid(sqlstr, 12)  '去除第1个select之前的内容
        If write_ws Is Nothing Then
            Set write_ws = Workbooks.Add.ActiveSheet: write_ws.Name = "合并表"  '新建工作表
            With write_ws  '写入表头、数据、原工作簿名称
                trr = Split(title_str, ","): .[b1].Resize(1, UBound(trr) + 1) = trr
                .[a1] = "原工作簿名称": .[b2].CopyFromRecordset cnn.Execute(sqlstr)
                .[a2].Resize(.[b1].End(xlDown).row - 1, 1) = fso.GetBaseName(file_name)
            End With
        Else
            With write_ws  '写入、数据、原工作簿名称
                write_row = .UsedRange.Rows.Count + 1: s = fso.GetBaseName(file_name)
                .Cells(write_row, 2).CopyFromRecordset cnn.Execute(sqlstr)
                .Cells(write_row, 1).Resize(.[b1].End(xlDown).row - write_row + 1, 1) = s
            End With
        End If
        cnn.Close: file_name = Dir   '下一个文件名
    Loop
    '保存文件
    If Not old_name Then write_ws.Columns("a:a").Delete  '无需写入原工作簿名称
    write_ws.Parent.SaveAs filename:=file_path & "合并表.xlsx"
    write_ws.Parent.Close (False)
    Set rs = Nothing: Set cnn = Nothing: Application.ScreenUpdating = True
    Debug.Print "文件夹合并完成,用时:" & Format(Timer - tm, "0.00")
End Sub
  • 优点:运行速度提升了数倍
  • 缺点:title_str参数中的表头名称必须每个待合并的工作表都包含,否则报错

举例

与上面的举例相同,但title_str参数中的表头顺序不同文章来源地址https://www.toymoban.com/news/detail-811173.html

  • 合并结果
    Excel·VBA合并工作簿2,# Excel,VBA,excel,vba,纵向合并,横向合并,多表合并

到了这里,关于Excel·VBA合并工作簿2的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • VBA:按照Excel工作表中的名称列自动汇总多个工作薄中对应sheet中所需要的数据

    需求如下: B列为产品名为合并单元格,C列为供应商名,G、H列为金额数据; 数据源放在同一个文件夹内,B列产品名来源于工作薄名称中间的字符串,C列供应商名来源于工作薄中的sheet名; G、H列金额数据来源于工作薄中sheet中固定单元格P25:Q25的数值; 根据B列产品名自动打

    2024年02月11日
    浏览(40)
  • EXCEL VBA从入门到精通 第九章:Excel VBA高级编程技巧

    介绍Excel VBA中的API编程,以及如何利用它们来访问Windows系统的功能。 Excel VBA提供了访问Windows系统API的功能,通过调用API函数可以访问Windows系统底层的功能和资源,例如操作系统、文件系统、网络、注册表等。API函数可以是Windows操作系统内置的函数,也可以是Windows DLL文件中

    2024年02月02日
    浏览(49)
  • EXCEL VBA从入门到精通 第一章:VBA入门

    介绍VBA的定义,作用和优点。 VBA(Visual Basic for Applications)是一种编程语言,是微软Office套件中的一个重要组成部分,主要用于自动化处理Office中的各种操作,包括Excel、Word、PowerPoint、Outlook等。 VBA是一种基于VB(Visual Basic)语言的编程语言,它具有简单易学、功能强大、灵

    2024年02月07日
    浏览(58)
  • 掌握 Microsoft Excel 宏和 Excel VBA

    掌握 Microsoft Excel 宏和 Excel VBA 基于项目的 Excel VBA(Visual Basic for Applications)和 Excel 宏课程 课程英文名:Master Microsoft Excel Macros and Excel VBA 此视频教程共27.0小时,中英双语字幕,画质清晰无水印,源码附件全 课程编号:286 百度网盘地址:https://pan.baidu.com/s/1ivLIGKt_3R0wncmCswMTR

    2024年02月05日
    浏览(44)
  • Excel+VBA帮助

     目录 一、VBA 入门知识 1、激活工作簿 2、创建新工作簿 3、打开工作簿 4、用编号引用工作表 5、用名称引用工作表 6、将文档保存为 Web 页 7、用 A1 样式的记号引用单元格和单元格区域 8、用编号引用单元格 9、引用行或列 10、用快捷记号引用单元格 11、引用命名单元格区域

    2024年02月05日
    浏览(48)
  • Excel VBA 语法基础

    VBA(Visual Basic for Applications)是一种用于宏编程和自动化任务的编程语言,广泛应用于 Microsoft Office 套件中的各种应用程序,如 Excel、Word 和 PowerPoint。掌握 VBA 基础语法可以帮助您通过编写自定义的宏来增强和自动化这些应用程序的功能。 本文将介绍 Excel VBA 的基础语法,帮

    2024年02月09日
    浏览(43)
  • 【一】Excel VBA开发 初探

    1、点击Excel左上角的【文件】,然后点击文件下方的【选项】 2、【Excel选项】中点击【自定义功能区】,右边勾选【开发工具】,点击【确定】  3、返回excel sheet 页面。点击【开发工具】,我们将看到三个我们开发过程中必用的按钮,分别是:【Visual Basic】、【宏】、【录制

    2024年02月11日
    浏览(46)
  • VBA技术资料MF36:VBA_在Excel中排序

    【分享成果,随喜正能量】一个人的气质,并不在容颜和身材,而是所经历过的往事,是内在留下的印迹,令人深沉而安谧。所以,优雅是一种阅历的凝聚;淡然是一段人生的沉淀。时间会让一颗灵魂,变得越来越动人。控制自己的脾气,做一个有修养的人。。 我给VBA的定义

    2024年02月15日
    浏览(50)
  • VBA技术资料MF35:VBA_在Excel中过滤数据

    【分享成果,随喜正能量】好马好在腿,好人好在嘴。不会烧香得罪神,不会讲话得罪人。慢慢的你就会发现,一颗好心,永远比不上一张好嘴。。 我给VBA的定义:VBA是个人小型自动化处理的有效工具。利用好了,可以大大提高自己的工作效率,而且可以提高数据的准确度。

    2024年02月14日
    浏览(37)
  • VBA技术资料MF43:VBA_Excel中自动填充

    【分享成果,随喜正能量】以时寝息,当愿众生,身得安隐,心无动乱。愿我们都能,梦见幸福!在踉跄中前进,在跌倒后跃进,逐渐强大.。 我给VBA的定义:VBA是个人小型自动化处理的有效工具。利用好了,可以大大提高自己的工作效率,而且可以提高数据的准确度。我的

    2024年02月12日
    浏览(32)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包