Include SubFolders When Looking For The Existence of A File

[Ark68]

I'm using this code check to see if a particular file (a PDF file) exists within a folder.

dpath = "D:\WSOP 2020\Permits\"
nfn = Target.Value & ".pdf"
strFile = dpath & nfn
'Stop
If FileExists(strFile) Then
     MsgBox "Exists."
     Exit Sub
End If

Function FileExists(filePath As String) As Boolean
    Dim TestStr As String
    TestStr = ""
    On Error Resume Next
    Debug.Print filePath
    TestStr = Dir(filePath)
    On Error GoTo 0
    If TestStr = "" Then
        FileExists = False
    Else
        FileExists = True
    End If
End Function

The only problem with this, is it's not searching the folders that are within the folder. It only checks the main folder but none of the subfolders. What must I do to also check the subfolders?
Once the file is located, since it's a PDF, how can I use VBA to open it?

 

[xenou]

I would go with the FileSystemObject on this one.

This example looks okay:
Loop Through Subfolders using FileSystemObject

Edit: I suppose I should add that I always go with the FileSystemObject on pretty much everything that involves files or folders in the file system

 

[Ark68]

Thank you Xenou for sharing this resource. I've integrated into my code, but it's not finding the file within the nested folders of the directory. I made the code suggested in the tutorial into a function, perhaps that's where it's failing?

Here's the code calling the function. Its based on a worksheet doubleclick event of a cell in column C.

    If Not Intersect(Target, ws_master.Columns(3)) Is Nothing Then 
        If Target.Value = "" Then Exit Sub
        MsgBox Target.Value
        ui1 = MsgBox("View permit?", vbYesNo, "Permit: " & Target.Value)
        If ui1 = vbNo Then Exit Sub
        Myfile = "Permit#" & Target.Value & ".pdf"
        Stop
        If LoopSubfoldersAndFiles(Myfile) Then
            MsgBox "Permit # " & target.value & " exists."
            Exit Sub
        Else
            MsgBox "Permit Not on file."
        End If
    End If

And the LoopSubfoldersAndFiles function ...

Function LoopSubfoldersAndFiles(Myfile As String) As Boolean
    Dim fso As Object
    'Dim Myfile As String
    Dim folder As Object
    Dim subfolders As Object
    Dim wb As Workbook
    Dim CurrFile As Object
    With Application
        '.ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("D:\WSOP 2020\Permits\")
    Set subfolders = folder.subfolders
    'MyFile = "Accounts.xlsx"
   
    For Each subfolders In subfolders
        Set CurrFile = subfolders.Files
        For Each CurrFile In CurrFile
            If CurrFile.Name = Myfile Then
                LoopSubfoldersAndFiles = True
                'Set wb = Workbooks.Open(subfolders.Path & "\" & MyFile)
                '    Range("A2:F10").Copy ThisWorkbook.Sheets(1).Range("A2")
                'wb.Close SaveChanges:=True
            End If
        Next
    Next
    Set fso = Nothing
    Set folder = Nothing
    Set subfolders = Nothing
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        '.ScreenUpdating = True
    End With
End Function

Thoughts on where the code is failing to provide the expected results? I assure you the file being sought does exist within the nested folders of the path provided.

 

[GWteB]

I made the code suggested in the tutorial into a function, perhaps that's where it's failing?

No it's not. The Windows file system is not case sensitive, your comparison is. As an additional note it is recommended to stop searching if your file is found.
Try changing this part

    If CurrFile.Name = Myfile Then
        LoopSubfoldersAndFiles = True
        'Set wb = Workbooks.Open(subfolders.Path & "\" & MyFile)
        '    Range("A2:F10").Copy ThisWorkbook.Sheets(1).Range("A2")
        'wb.Close SaveChanges:=True
    End If

to this

    If StrComp(CurrFile.Name, Myfile, vbTextCompare) = 0 Then
        LoopSubfoldersAndFiles = True
        'Set wb = Workbooks.Open(subfolders.Path & "\" & MyFile)
        '    Range("A2:F10").Copy ThisWorkbook.Sheets(1).Range("A2")
        'wb.Close SaveChanges:=True
        Exit For
    End If

Finally, note that the code from your post only performs a search in subfolders on that particular level (folders in subfolders are ignored) and it does not work if the folder is a rootfolder (eg: F:\ ).
You might be interested in this post.

Extract file names with extensions and absolute path with VBA code

Hi all, I am trying to create a macro in excel for extract file names (with extension) and their absolute path, from one main path (always a local path), but I am afraid I am not able to. My idea is to insert in cell C2 the main path, where there are files, folders and subfolders (with files...

www.mrexcel.com

 

[Ark68]

Thank you GWteB. So, if I'm understanding correctly, the code i have tried isn't created to do what I need it to do ... search all folders within folders on a quest to find the file?
I'll see what kind of damage I can do with your suggestion.

Question ... for forum etiquette. Should I use this post, or the post your linked to, to work through problems with it's implementation? I imagine I will be questioning how to use that information to open the file once it's found rather than displaying the paths. But I'll try.

 

[xenou]

Your code is not a fully nested search. It looks like you are searching the immediate subfolders of the first folder but not subfolders of those folders and so on (which is basically a recursive search, I think). Is it your intention to search in all subfolders including subfolders of subfolders, or just the immediate (first level) subfolders?

 

[Ark68]

yes, I need to search all folders, within folders, within folders of the root.

D:\
      WSOP\
               Permits\
                         Folder A\
                                    FolderA1\
                                              ... files
                                    FolderA2\
                                              ... files
                         Folder B\
                                    Folder B1\
                                              ... files
                                    Folder B2\
                                              ... files
                                     Folder B3\
                                              ... files
                         Folder C\
                                     Folder C1\
                                              ... files
                                    Folder B2\
                                              ... files

 

[xenou]

Ah nevermind I see Ark68 has you on the right track ... I think its up to you if you feel more comfortable continuing in either thread (especially since that other one is quite recent...).

 

[xenou]

My version of the same is all here:

Recursive Folder Search

 

[Ark68]

My version of the same is all here:

Thank you for sharing that! I'll have to take a look at it. Every contribution is a learning opportunity.