Dim d, w, arr, brr(100000, 50), m&, n%
Sub Macro1()
Set d = CreateObject("scripting.dictionary")
Set w = Application.WorksheetFunction
Application.ScreenUpdating = False
Cells.ClearContents
m = 0: n = 0
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then zdir .SelectedItems(1) & "\"
End With
brr(0, 0) = "表名"
If m Then [A1].Resize(m + 1, n + 1) = brr
Application.ScreenUpdating = True
End Sub
Sub zdir(p)
Dim fs As Object
Set fs = CreateObject("scripting.filesystemobject")
For Each f In fs.GetFolder(p).Files
If f <> ThisWorkbook.FullName Then
With GetObject(f)
For Each sh In .Worksheets
If w.CountA(sh.UsedRange) Then
arr = sh.UsedRange.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, 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 0
End With
End If
Next
For Each fd In fs.GetFolder(p).SubFolders
zdir fd
Next
End Sub