Retrieving name of subfolder

[pvr928]

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

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

 

[Rijnsent]

Hi Pvr,
the answer is in your code...

Sub Find_File()


Dim colFiles As New Collection


GetFiles "E:\TEST\", "FileToFind" & ".pdf", True, colFiles
If colFiles.Count > 0 Then
    For Each s In colFiles
        Debug.Print CStr(s)
    Next s
    Debug.Print "Found it!"
Else
    Debug.Print "Didn't find it!"
End If


End Sub

This code will show you all found files. You could get the directory: vba - Find the directory part (minus the filename) of a full path in access 97 - Stack Overflow
Cheers,
Koen

 

[pvr928]

Hi Koen

Thanks so much ! So simple!

For some reason I was concentrating on the main routine - I didn't think the answer lay in the smaller routine.

Thanks once again.

Cheers

pvr928