Sub test1() '
Dim strPath As String, strFile As String, strText As String
Dim a, b, ar, br() As String, i As Long, j As Long
a = Array("审批表", "制度", "协议", "规定")
b = Array("工资表", "工资凭单", "凭证", "规则")
ar = Range("A1").CurrentRegion.Columns(4).Offset(1)
ReDim br(1 To UBound(ar) - 1, 1 To 4)
For i = 1 To UBound(ar) - 1
strPath = ThisWorkbook.Path & "\名单\" & ar(i, 1)
If Len(Dir(strPath, vbDirectory)) Then
strText = vbNullString
strFile = Dir(strPath & "\*.jpg")
While Len(strFile)
strText = strText & strFile
strFile = Dir
Wend
If InStr(strText, a(0)) Then br(i, 1) = "有" Else br(i, 1) = "无"
For j = 0 To 3
If j > 0 Then If InStr(strText, a(j)) Then br(i, 2) = br(i, 2) & "、" & a(j)
If InStr(strText, b(j)) Then br(i, 3) = br(i, 3) & "、" & b(j)
Next
For j = 2 To 3
br(i, j) = Mid(br(i, j), 2)
Next
End If
Next
Range("H2").Resize(UBound(br), UBound(br, 2)) = br
Beep
End Sub |