|
- Sub Macro1()
- Dim mypath$$, wj$$, wb As Workbook, arr, i&, s&
- Dim sht As Worksheet, brr(1 To 50000, 1 To 5)
- Application.ScreenUpdating = False
- mypath = ThisWorkbook.Path & ""
- wj = Dir(mypath & "*.xls")
- Do While wj <> ""
- If wj <> ThisWorkbook.Name Then
- With GetObject(mypath & wj)
- Set sht = .Sheets(.Sheets.Count)
- arr = sht.UsedRange
- bm = Mid(arr(3, 1), 6)
- mc = Mid(arr(4, 1), 6)
- n = IIf(sht.Cells(5, 1).MergeCells, 3, 2)
- For i = 6 To UBound(arr)
- If arr(i, n + 3) <> "" And Not sht.Cells(i, n).MergeCells Then
- s = s + 1
- brr(s, 1) = bm
- brr(s, 2) = mc
- brr(s, 3) = arr(i, n + 9)
- brr(s, 4) = arr(i, n)
- brr(s, 5) = arr(i, n + 3)
- End If
- Next
- .Close 0
- End With
- End If
- wj = Dir
- Loop
- [e:e].NumberFormatLocal = "@"
- Range("a2").Resize(s, 5) = brr
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|