|
Sub limonet()
Dim Arr As Variant, S$, StrT$, Brr() As Variant, Crr As Variant, i%, j%
Arr = Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
ReDim Brr(1 To UBound(Arr), 1 To 3)
Crr = Array("审批表", "制度|协议|规定", "工资表|工资凭单|凭证")
S = CreateObject("wscript.shell").exec("PowerShell $FormatEnumerationLimit=-1;get-childitem " & ThisWorkbook.Path _
& "\名单 -recurse -file | Select @{Name='Name';Expression={$_.name -Replace '[^一-龥]',''}},@{Name='Directory';Expression={$_.Directory -Replace '.*\\',''}} " _
& "| Get-Unique -AsString | Group directory |select name, @{Name='Group';Expression={$_.Group -Replace '[^一-龥]',''}}").stdout.readall
With CreateObject("vbscript.regexp")
For i = 1 To UBound(Arr)
.Pattern = Arr(i, 1) & ".*?}": .Global = True
StrT = .Execute(S)(0)
For j = 1 To 3
.Pattern = Crr(j - 1)
If j - 1 Then
For Each ms In .Execute(StrT)
Brr(i, j) = Brr(i, j) & "," & ms
Next ms
Brr(i, j) = Mid(Brr(i, j), 2)
Else
If .test(StrT) Then
Brr(i, j) = "有"
Else
Brr(i, j) = "无"
End If
End If
Next j
Next i
End With
Range("h2").Resize(i - 1, j - 1) = Brr
End Sub |
|