|
在“遍历文件夹并根据规则重新命名”文件夹中新建个Excel文件,将下面代码粘贴即可。
Sub 遍历子文件夹,根据规则拷贝指定文件到指定目录并重命名()
Dim i&, n&, MyPath$
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'**********************************************************************************
Rem 提取路径名及文件名
Set dic = CreateObject("Scripting.Dictionary") '创建一个字典对象
MyPath = ThisWorkbook.Path
dic.Add (MyPath & "\"), ""
i = 0
Do While i < dic.Count
ke = dic.keys '开始遍历字典
Filename = Dir(ke(i), vbDirectory) '查找目录
Do While Filename <> ""
If Filename <> "." And Filename <> ".." Then
If (GetAttr(ke(i) & Filename) And vbDirectory) = vbDirectory Then '如果是次级目录
dic.Add (ke(i) & Filename & "\"), "" '就往字典中添加这个次级目录名作为一个条目
End If
End If
Filename = Dir '继续遍历寻找
Loop
i = i + 1
Loop
i = 0
'**********************************************************************************
struser = MyPath & "\"
For Each ke In dic.keys '以查找总表所在文件夹下所有excel文件为例
' m = m + 1
If InStr(ke, "素材") Then GoTo 1
myfliename = Dir(ke & "*.*") '过滤器:EXCEL2003为:*.xls,excel2007为:*.xlsx
Do While myfliename <> ""
If myfliename <> Liwai Then '排除例外文件
If myfliename <> ThisWorkbook.Name And myfliename <> "求助说明.txt" Then
If InStr(myfliename, ".xls") Or InStr(myfliename, ".xlsx") Then
Set wb = Workbooks.Open(ke & myfliename)
With wb
nm = .Sheets(1).Range("a2").Value
.Close
End With
End If
If Dir(ke & myfliename, ".cdr") <> "" Or Dir(ke & myfliename, ".ai") <> "" Then
If InStr(myfliename, ".cdr") Then
FileCopy ke & myfliename, struser & "素材\" & nm & ".cdr"
End If
If InStr(myfliename, ".ai") Then
FileCopy ke & myfliename, struser & "素材\" & nm & ".ai"
End If
End If
End If
End If
myfliename = Dir
Loop
1:
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub |
评分
-
2
查看全部评分
-
|