• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to search specific files in nested/sub folders of a path

Hello Experts,

I have 2 files located in a given folder/sub folders of a path: (“C:\Users\Desktop\Deal\”)

Under the Deal folder, i have sub folders like Deal1, Deal2, Deal3 and so on....

Each Sub folder has nested sub folders like Q1 2021, Q2 2021, Q3 2021 & so on....

File names:
1. Quarterly Reporting Data.xlsx
2. Summary Report.PDF

I’m looking for a VB Script to search for the above files in each nested folders of the path mentioned above.

Currently, i am tracking manually in the excel as below:

73609

If i run a code the above table should get populated with yes/no based on the files availability in the path.

I appreciate if someone can help me on the above scenario.
 

Attachments

  • Deal Documents Missing Info.xlsx
    9.3 KB · Views: 7
Hi, according to your attachment a VBA demonstration as a starter :​
Code:
Sub Demo1()
        W = Array("No", "Yes")
    With [A1].CurrentRegion.Rows("2:" & [A1].CurrentRegion.Rows.Count)
            V = .Columns("A:B").Value2
        For R& = 1 To .Count
            If V(R, 1) > "" Then F$ = V(R, 1)
            P$ = "C:\Users\Desktop\Deal\" & F & "\" & V(R, 2) & "\"
            V(R, 1) = W(-(Dir(P & "Quarterly Reporting Data.xlsx") > ""))
            V(R, 2) = W(-(Dir(P & "Summary Report.pdf") > ""))
        Next
           .Columns("C:D").Value2 = V
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Hi,

Firstly, Thank you for the code. It works perfect. However small change in the ask as below:

1. If i add 1 more nested folder, i mean Deal\Deal 1\2021\Q1
2. Instead of exact match of the file name, is there any possibility of the partial match or trimming the file names, for example,
if file names reads Quarterly Reporting Data 9-20-2020 or Quarterly Reporting Data v1 etc.
Even some times file comes in word document and sometimes in PDF but name would be same as "
Quarterly Reporting Data",
only extension of the file name or addition of few more characters at the end of the file name.

Appreciate if you can tweek the code the above 2 scenarios.

Thank you so much for your support.
 
Hi,

just wanted to check if you got a chance to look into the above sceanrios to tweek the code. Extension one solved.

Point 1 mentioned above is the main thing, i am looking for.
Appreciate your help. Thank you !!
 
1 : If necessary you have to update the starter procedure for​
• the source columns address​
• the allocation of the variable P as the files path​
• the destination columns address …​
 
Back
Top