|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
jjmysjg 发表于 2014-11-25 10:36
zhaogang1960版主你好:
能在汇总表的前面添加文件夹和工作簿名称吗 - Sub 多表合并()
- Dim Fso As Object, Folder As Object, arrf$(), mf&
- Dim sh As Worksheet, arr, brr(0 To 100000, -2 To 50), w As WorksheetFunction
- Dim d As Object, i&, j&, m&, n&, c As Range, MyPath$
- With Application.FileDialog(msoFileDialogFolderPicker)
- .InitialFileName = ThisWorkbook.Path & ""
- If .Show = False Then Exit Sub
- MyPath = .SelectedItems(1)
- End With
- Application.ScreenUpdating = False
- Set d = CreateObject("scripting.dictionary")
- Set w = Application.WorksheetFunction
- Set Fso = CreateObject("Scripting.FileSystemObject")
- Set Folder = Fso.GetFolder(MyPath)
- Call GetFiles(Folder, arrf, mf)
- For l = 1 To mf
- With GetObject(arrf(2, l) & "" & arrf(1, l))
- For Each sh In .Worksheets
- If w.CountA(sh.UsedRange) Then
- Set c = sh.UsedRange
- arr = c.Value
- For j = 1 To UBound(arr, 2)
- If Len(arr(1, j)) Then
- If Not d.Exists(arr(1, j)) Then
- n = n + 1
- d(arr(1, j)) = n
- brr(0, n) = arr(1, j)
- End If
- End If
- Next
- For i = 2 To UBound(arr)
- m = m + 1
- If m > 1048575 Then
- MsgBox "超出最大行数1048576,无法合并"
- Exit Sub
- End If
- brr(m, d(arr(1, 1))) = arr(i, 1)
- brr(m, -2) = Split(arrf(2, l), "")(UBound(Split(arrf(2, l), "")))
- brr(m, -1) = arrf(1, l)
- brr(m, 0) = sh.Name
- For j = 2 To UBound(arr, 2)
- If Len(arr(1, j)) Then brr(m, d(arr(1, j))) = arr(i, j)
- Next
- Next
- End If
- Next
- .Close False
- End With
- Next
- Cells.ClearContents
- brr(0, -2) = "文件夹名"
- brr(0, -1) = "工作簿名"
- brr(0, 0) = "表名"
- If m Then [a1].Resize(m + 1, n + 3) = brr
- Set Folder = Nothing
- Set Fso = Nothing
- Application.ScreenUpdating = True
- End Sub
- Sub GetFiles(ByVal Folder As Object, ByRef arrf$(), ByRef mf&)
- Dim SubFolder As Object
- Dim File As Object
- For Each File In Folder.Files
- If File.Name Like "*.xlsx" Then
- mf = mf + 1
- ReDim Preserve arrf(1 To 2, 1 To mf)
- arrf(1, mf) = File.Name
- arrf(2, mf) = Folder.Path
- End If
- Next
- For Each SubFolder In Folder.SubFolders
- Call GetFiles(SubFolder, arrf, mf)
- Next
- End Sub
复制代码 |
|