'代码放入"代码查询"文件中,文件放在"分发库"目录上一级。供参考,,,
Option Explicit
Sub test()
Dim arr, pth, filename(), dic, i, t, p, f
pth = ThisWorkbook.Path & "\"
If Len(pth & "分发库") = 0 Then MsgBox "!": Exit Sub
If Not getfilename(filename, pth & "分发库", ".pdf") Then MsgBox "!!": Exit Sub
Set dic = CreateObject("scripting.dictionary")
arr = Range("a2:d" & [b2].End(xlDown).Row)
For i = 1 To UBound(arr, 1)
dic(LCase(arr(i, 1))) = pth & arr(i, 4) & "\" & arr(i, 2) & "\"
If Len(Dir(pth & arr(i, 4), vbDirectory)) = 0 Then MkDir (pth & arr(i, 4))
Next
For i = 1 To UBound(filename)
t = Split(filename(i), "\")
f = t(UBound(t))
t = LCase(Split(f, "-")(0))
If dic.exists(t) Then
p = dic(t)
If Len(Dir(p, vbDirectory)) = 0 Then MkDir p
FileCopy filename(i), p & f
Else
MsgBox t: Exit Sub '无法定位
End If
Next
End Sub
Function getfilename(filename, pth, mark) As Boolean
Dim f, n
If Right(pth, 1) <> "\" Then pth = pth & "\"
f = Dir(pth & "*.*")
Do While Len(f) > 0
If LCase(Right(f, Len(mark))) = LCase(mark) Then
n = n + 1: ReDim Preserve filename(1 To n)
filename(n) = pth & f
End If
f = Dir
Loop
If n > 0 Then getfilename = True
End Function |