WPS2012如何合并同一个文件夹下的多个表格
一个文件夹中有很多相同类型但是数值不同的表格,如何能够快速的把这些表格合并成一张表格呢? 方法步骤是——
粘贴代码后,点击菜单栏的“运行”,并在下拉菜单中点选“运行子过程/用户窗体 F5”等一会就OK了。(特别提示:重复操作时,点选“运行子过程/用户窗体 F5”后,会出现“宏”对话框(见下图),在对话框中再点按“运行”就OK了。)
用于复制粘贴的程序语言代码:
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) = right(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
特别提示: “.Cells(.Range("B65536").End(xlUp).Row + 2, 1) = right(MyName, Len(MyName) - 4)” 这句程序语言中要用“right”函数才是对的;网络上介绍用的是“left”函数可能运行不通。 |
|