|
给你一个类似的代码参考
Sub App_FileSearch()
'Stop
Const keyword As String = "*.xl*"
Call App_SearchSubFolder(keyword, True)
If UBound(strArr) > 0 Then
ActiveSheet.Range("a2:a65536").Clear
x = 0
For i = 0 To UBound(strArr)
If strArr(i) <> "" And strArr(i) Like "[SF]*" Then
x = x + 1
ActiveSheet.Hyperlinks.Add Anchor:=Cells(x + 2, "A"), _
Address:=strArr(i), TextToDisplay:=strArr(i)
End If
Next i
Else
MsgBox "没有发现文件"
End If
End Sub
Function App_SearchSubFolder(keyword As String, rSearchSubFolders As Boolean)
Dim fd As Object
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
' If fd.Show = -1 Then
rLookIn = ThisWorkbook.Path 'fd.SelectedItems(1)
' Else
' MsgBox "未选取文件夹": Exit Function
' End If
rFilename = Dir$(rLookIn & "\" & keyword)
' If rFilename = ThisWorkbook.Name Then Exit Function
rCount = 0
ReDim Preserve strArr(rCount)
Do While rFilename <> vbNullString
If rFilename <> ThisWorkbook.Name Then
strArr(rCount) = rFilename
rCount = rCount + 1
ReDim Preserve strArr(rCount)
End If
rFilename = Dir$()
Loop
If rSearchSubFolders Then
Call App_NextSubFolder(fso.GetFolder(rLookIn), keyword)
End If
' Set fd = Nothing
' Set fso = Nothing
End Function
Private Sub App_NextSubFolder(ByRef Folder As Object, ByRef keyword As String)
Dim SubFolder As Object
For Each SubFolder In Folder.SubFolders
rFilename = Dir$(SubFolder.Path & "\" & keyword)
Do While rFilename <> vbNullString
strArr(rCount) = SubFolder.Path & "\" & rFilename
rCount = rCount + 1
ReDim Preserve strArr(rCount)
rFilename = Dir$()
Loop
Call App_NextSubFolder(SubFolder, keyword)
Next
End Sub |
|