Scan MULTIPLE folders / locations.

BillTony

Board Regular
Joined
May 3, 2017
Messages
52
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!

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

Well-known Member
Joined
Apr 8, 2012
Messages
519
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):
Code:
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.
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top