|
- Sub BJF() 'by: bajifeng
- Dim brr(), arr, cn, cw()
- Set sho = ThisWorkbook.ActiveSheet
- sho.[A2:D9999].Clear
- fl = ListFile(ThisWorkbook.Path, True, "*.xls?")
- Application.ScreenUpdating = False
- For l = 0 To UBound(fl)
- If fl(l) <> "" Then
- If InStr(fl(l), "汇总") = 0 Then
- Workbooks.Open fl(l)
- '+++++++++++++++++++++++++++++++++++++++
- For Each sht In Worksheets
- n = n + 1
- If sho.[a2] = "" Then
- sht.Range("a4:d" & [b65536].End(3).Row).Copy sho.[a2]
- Else
- sht.Range("a4:d" & [b65536].End(3).Row).Copy sho.[a65536].End(3).Offset(2)
- End If
- Next
- If n = 1 Then
- cn = 4
- ReDim cw(1 To cn)
- For i = 1 To cn
- cw(i) = Columns(i).ColumnWidth
- Next
- End If
- '+++++++++++++++++++++++++++++++++++++++
- ActiveWorkbook.Close True
- End If
- End If
- Next
- '[a2:f999] = ""
- '[a2].Resize(n, 5) = Application.Transpose(brr)
- 'Columns("a:f").AutoFit
- For k = 1 To cn
- Columns(k).ColumnWidth = cw(k)
- Next
- Application.ScreenUpdating = True
- MsgBox "处理完毕!"
- End Sub
- Private Function ListFile(MuLu As String, Zi As Boolean, Optional LeiXing As String = "")
- Dim MyFile As String, ms As String
- Dim arr, brr, x
- Dim i As Integer
- Set d = CreateObject("Scripting.Dictionary")
- If Left(MuLu, 1) <> "" Then MuLu = MuLu & ""
- d.Add MuLu, ""
- i = 0
- Do While i < d.Count
- brr = d.keys
- MyFile = Dir(brr(i), vbDirectory)
- Do While MyFile <> ""
- If MyFile <> "." And MyFile <> ".." Then
- If (GetAttr(brr(i) & MyFile) And vbDirectory) = vbDirectory Then d.Add (brr(i) & MyFile & ""), ""
- End If
- MyFile = Dir
- Loop
- If Zi = False Then Exit Do
- i = i + 1
- Loop
- If LeiXing = "" Then
- ListFile = Application.Transpose(d.keys)
- Else
- For Each x In d.keys
- MyFile = Dir(x & LeiXing)
- Do While MyFile <> ""
- ms = ms & x & MyFile & ","
- MyFile = Dir
- Loop
- If Zi = False Then Exit For
- Next
- If ms = "" Then ms = "没有符合要求的文件,"
- 'ListFile = Application.Transpose(Split(ms, ",")) '下标从1开始
- ListFile = Split(ms, ",") '下标从0开始
- End If
- End Function
复制代码 |
|