|
- Private Sub tq()
- Dim i%, num%, arr, reg As Object
- Application.ScreenUpdating = False
- Set fso = CreateObject("scripting.filesystemobject")
- Set f = fso.getfolder(ThisWorkbook.Path & "")
- Set ff = f.Files
- num = ff.Count
- ReDim arr(1 To num - 2, 1 To 2)
- Set reg = CreateObject("vbscript.regexp")
- reg.Global = True
- reg.Pattern = "[0-9]+"
- For Each kkk In ff
- If kkk.Name <> ThisWorkbook.Name And reg.test(kkk.Name) Then
- k = k + 1
- arr(k, 1) = kkk.Name
- arr(k, 2) = reg.Execute(kkk.Name)(0)
- End If
- Next
- Sheet2.Range("A2").Resize(UBound(arr), UBound(arr, 2)) = arr
-
- '----排序--以下为录制宏代码
- Range("A2:B13").Select
- ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
- ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("B2:B13") _
- , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
- With ActiveWorkbook.Worksheets("Sheet2").Sort
- .SetRange Range("A2:B13")
- .Header = xlGuess
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|