Include SubFolders When Looking For The Existence of A File

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,876
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm using this code check to see if a particular file (a PDF file) exists within a folder.

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

Code:
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?
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,876
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
Code:
    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 ...
Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
    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
Rich (BB code):
    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.

 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,876
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 23, 2004
Messages
3,876
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Rich (BB code):
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,832
Office Version
  1. 2019
Platform
  1. Windows
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...).
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,876
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top