Scan MULTIPLE folders / locations.

[BillTony]

Hi there!

I'm having some issue on coding for an "item" count from multiple folder locations.

The code below works fine for looking at a single location on a network drive but, of course, now I need to look in alternate locations...

In a nutshell: if a value in column D = "A" look in location 1, if the value = "B" look in location 2.

Thanks "F," for the previous advice on file exclusions...

Happy Holidays!

Sub Two_Folder_Scanning()
 
'Variables.
    Dim Folder_Location_1 As String
    Dim Folder_Location_2 As String
    Dim F_S_O As Object
    Dim Object_Files As Object
    Dim Ob_Ject As Object
    Dim File_Count As Long
    Dim Last_Row_ColA As Long
    Dim Loop_Thru As Long
   
'The SCAN.
 
'Set the DIRECTORY / FOLDER LOCATION.
    Folder_Location_1 = "M:\Path 1"
    Set F_S_O = CreateObject("Scripting.FileSystemObject")
'The PARAMETERS of the LOOP.
    For Loop_Thru = 3 To Last_Row_ColA
'VALUE of "AcctNo_Searched."
    AcctNo_Searched = Folder_Location & Range("B" & Loop_Thru).Value
    Set Object_Files = F_S_O.getfolder(AcctNo_Searched).Files
'VALUE of "File_Count."
    File_Count = Object_Files.Count
'LOOP.
    For Each Ob_Ject In Object_Files
'EXCLUDE specific file TYPES.
        If Ob_Ject.Name Like "*.db" Or Ob_Ject.Name Like "*~*" Then
            File_Count = File_Count - 1
        End If
'CONTINUE LOOP.
    Next Ob_Ject
'PLACE the result of the item COUNT.
    Range("AE" & Loop_Thru).Value = File_Count
    Next
 
End Sub

 

[AFPathfinder]

I'd say you have two options if you don't need to search all locations at once. The first is to hardcode the logic into a branch statement (If or Select Case):

If Range("D" & Loop_Thru).Value = "A" Then
    Folder_Location = "C:\Users\" & Environ("USERNAME") & "\Desktop"

ElseIf Range("D" & Loop_Thru).Value = "B" Then
    Folder_Location = "C:\Users\" & Environ("USERNAME") & "\Documents"

End If

Or, you could use an extra column in your table to provide the file path for "A" or "B" for each row; VLOOKUP to a second reference table or just plain text in the column.

 

[BillTony]

Awesome!

Made my day - greatly appreciated!

 

[AFPathfinder]

You're welcome!