|
楼主 |
发表于 2010-7-20 13:19
|
显示全部楼层
仅以此纪念学习excel一周年.....
实例: 文件清单
功能: 按磁盘查询指定类型文件.
字典代码是家里下载的,仅对表头表尾进行修改.
我觉得这个蛮实用的,一直在用,忘记文件存放的地方或文件名字不要紧,只要不是隐藏的,都能找到.
Sub Test() '使用双字典,旨在提高速度
On Error GoTo errh
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim MyName, Dic, Did, I, T, F, TT, MyFileName
Dim cp, wj As String
Dim sh As Worksheet
cp = Sheet1.[b2]
wj = Sheet1.[b1]
T = Time
For Each sh In Worksheets
If sh.Name <> "序" Then sh.Delete
Next
Set Dic = CreateObject("Scripting.Dictionary") '创建一个字典对象
Set Did = CreateObject("Scripting.Dictionary")
Dic.Add (cp & ":\"), "" '需要查找的磁盘
I = 0
Do While I < Dic.Count
Ke = Dic.keys '开始遍历字典
MyName = Dir(Ke(I), vbDirectory) '查找目录
Do While MyName <> ""
If MyName <> "." And MyName <> ".." Then
If (GetAttr(Ke(I) & MyName) And vbDirectory) = vbDirectory Then '如果是次级目录
Dic.Add (Ke(I) & MyName & "\"), "" '就往字典中添加这个次级目录名作为一个条目
End If
End If
MyName = Dir '继续遍历寻找
Loop
I = I + 1
Loop
Did.Add (cp & "盘" & wj & "文件清单"), "" '以查找文件为例
For Each Ke In Dic.keys
MyFileName = Dir(Ke & "*." & wj) '查找文件的类型
Do While MyFileName <> ""
Did.Add (Ke & MyFileName), ""
MyFileName = Dir
Loop
Next
For Each sh In ThisWorkbook.Worksheets
If sh.Name = cp & "盘" & wj & "文件清单" Then
Sheets(cp & "盘" & wj & "文件清单").Cells.Delete
F = True
Exit For
Else
F = False
End If
Next
If Not F Then
Sheets.Add.Name = cp & "盘" & wj & "文件清单"
End If
Sheets(cp & "盘" & wj & "文件清单").[A1].Resize(Did.Count, 1) = WorksheetFunction.Transpose(Did.keys)
TT = Time - T
MsgBox Minute(TT) & "分" & Second(TT) & "秒"
With Sheets(cp & "盘" & wj & "文件清单")
.Select
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Range("B2").Select
I = .[a65536].End(xlUp).Row
ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-1])"
Selection.AutoFill Destination:=Range("B2:B" & I)
End With
errexit:
Set Dic = Nothing
Set Did = Nothing
Exit Sub
errh:
MsgBox Err.Description
Resume errexit
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
[ 本帖最后由 XZ19860527 于 2010-7-20 13:30 编辑 ] |
|