遍历文件,读取文件夹下所有文件,汇总至Excel

Sub 遍历文件()
Dim mfile As String
Dim mpath As String
Dim mpath2 As String
Dim wzg_name As String
Dim pp As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim num As Long
mpath = InputBox("Enter pathname:")
Worksheets("sheet3").Activate
Range("a1").Select
i = 0
j = 0
k = 0
m = 0
num = 0
wzg_name = ""
mfile = Dir(mpath, 16)
If mpath = "" Then
Exit Sub
End If
If mfile = "" Then
Exit Sub
End If
Do While mfile <> ""
Do While mfile = "." Or mfile = ".."
mfile = Dir
Loop
If mfile = "" Then
Exit Do
End If
Selection.Offset(i, j).Value = mpath & mfile & "\"
mfile = Dir
i = i + 1
Loop
j = 1
Do While m <= 19
k = 0
Do
mpath = Selection.Offset(k, m).Value
If Selection.Offset(0, j).Value = "" Or Selection.Offset(0, j).Value = 0 Then
i = 0
Else: i = Selection.Offset(1000000, j).End(3).Row
End If '重新给i赋值
mpath2 = Left(mpath, Len(mpath) - 1)
If Dir(mpath2) = Dir(mpath2, 16) Then
Selection.Offset(i, j).Value = mpath
i = i + 1
GoTo DD
End If
mfile = Dir(mpath, 16)
CC:
If mfile = "" Then
Selection.Offset(i, j).Value = mpath
i = i + 1
GoTo DD
End If
Do While mfile = "." Or mfile = ".."
mfile = Dir
Loop
If mfile = "" Then
GoTo CC
End If
Do While mfile <> ""
Selection.Offset(i, j).Value = mpath & mfile & "\"
mfile = Dir
i = i + 1
Loop
DD:
k = k + 1
mpath = Selection.Offset(k, m).Value
If mpath = "" Then GoTo ff
mpath2 = Left(mpath, Len(mpath) - 1)
If Dir(mpath2) = Dir(mpath2, 16) Then
Selection.Offset(i, j).Value = mpath
i = i + 1
GoTo DD
End If
ff:
Loop While k < Selection.Offset(1000000, m).End(3).Row
j = j + 1
m = m + 1
If Selection.Offset(1000000, m).End(3).Row = Selection.Offset(1000000, m - 1).End(3).Row Then Exit Do
Loop
End Sub