Hi
I am would like to retrieve the name of the subfolder containing the file I am searching for. I am using this code from excel - VBA macro that search for file in multiple subfolders - Stack Overflow
I do otherwise have code that provides the name of the subfolder, but this method using Collections, is much faster. The downside is - I am not that familiar with Collections !
Any help greatly appreciated.
Cheers
pvr928
I am would like to retrieve the name of the subfolder containing the file I am searching for. I am using this code from excel - VBA macro that search for file in multiple subfolders - Stack Overflow
I do otherwise have code that provides the name of the subfolder, but this method using Collections, is much faster. The downside is - I am not that familiar with Collections !
Any help greatly appreciated.
Cheers
pvr928
Code:
Option Explicit
Sub GetFiles(StartFolder As String, Pattern As String, _
DoSubfolders As Boolean, ByRef colFiles As Collection)
'http://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders
Dim f As String, sf As String, subF As New Collection, s
If Right(StartFolder, 1) <> "\" Then StartFolder = StartFolder & "\"
f = Dir(StartFolder & Pattern)
Do While Len(f) > 0
colFiles.Add StartFolder & f
f = Dir()
Loop
sf = Dir(StartFolder, vbDirectory)
Do While Len(sf) > 0
If sf <> "." And sf <> ".." Then
If (GetAttr(StartFolder & sf) And vbDirectory) <> 0 Then
subF.Add StartFolder & sf
End If
End If
sf = Dir()
Loop
For Each s In subF
GetFiles CStr(s), Pattern, True, colFiles
Debug.Print CStr(s)
Next s
End Sub
Sub Find_File()
Dim colFiles As New Collection
GetFiles "E:\TEST\", "FileToFind" & ".pdf", True, colFiles
If colFiles.Count > 0 Then
Debug.Print "Found it!"
Else
Debug.Print "Didn't find it!"
End If
End Sub