exclude folder paths list

[anmac1789]

no absolutely not, I am talking about using VBA code. What i am saying is that search button on my worksheet to retreive the list of files/folders

[SamT]

See Post # 6

[anmac1789]

So basically you are saying that using this code:

Sub main()
   For each file in parentfolder.subfolders
      If Not Excluded(subfolder 1.Name) then
          execute all the range code with recursive getfiles
      End If 
   Next
end sub

[anmac1789]

post #6 only checks for path names but what I am looking for is if a path name matches during subfolder search

for example, during :

for each osubfolder in directory.folder (parent directory)
if subfolder = exclud then
leave out this list of subfolders
end if
list other subfolders as normal as in the 1st post
next osubfolder

i dont know how to combine this with excluding all files within the excluded subfolder in one code

[SamT]

excluding all files within the excluded subfolder

How does your code "See" into an excluded folder? The question is not logical. (Forgive the Spockism.)

[anmac1789]

well what I want vba to see is as the code is running, check each subfolder path and file path and if it matches the folder path I want to be excluded then it excludes listing it in the main worksheet. I want to see if it can do it without listing all the folder and file paths first and then exclude those folder/file paths because it seems like that just takes more memory

[SamT]

Well, I want to be 30 years old and 6' tall.

[anmac1789]

what?

[snb]

@SamT

No problem. Your wish is my command.

[anmac1789]

okay so getting back to topic...

[Paul_Hossler]

I'm very confused by the posts in this thread

I created a simple folder tree and recursed it

I have an array with excluded names and if a branch is in the array, I skip the subfolders and files all the way out to the leaves

I didn't include any file parameters since I also found the requirements confusing also

Capture.JPG

Option Explicit


Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim o As Long
Dim FSO As Object


Sub Start()
    aryExclude = Array("111CCC111", "333AAA", "333BBB")
    
    o = 1


    ActiveSheet.Columns(5).ClearContents    '   testing purposes


    Set FSO = CreateObject("Scripting.FileSystemObject")


    Call GetFiles(FSO.GetFolder(sPathTop))


    MsgBox "Done"
End Sub






Sub GetFiles(oPath As Object)
    Dim oFolder As Object, oSubFolder As Object, oFile As Object


    If Not IsExcluded(oPath) Then
        ActiveSheet.Cells(o, 5).Value = oPath.path
        o = o + 1
        
        For Each oFile In oPath.Files
            ActiveSheet.Cells(o, 5).Value = oFile.path
            o = o + 1
        Next
        
        For Each oSubFolder In oPath.SubFolders
            Call GetFiles(oSubFolder)
        Next
    
    End If


End Sub


Private Function IsExcluded(p As Object) As Boolean
    Dim i As Long
    IsExcluded = True
    
    For i = LBound(aryExclude) To UBound(aryExclude)
        If UCase(p.Name) = UCase(aryExclude(i)) Then Exit Function
    Next i
    
    IsExcluded = False
End Function

[anmac1789]

I don't think its that complicated to understand, seeing post #16 is more helpful. it's very close to what you are doing with the exception that instead of having the directory list already listed in the cells, it is excluding multiple subfolder paths (while also listing all files and folders recursively) during when my main code is run.. (from post #1). And also this only works for folder names, not folder paths

[Paul_Hossler]

I don't think its that complicated to understand, seeing post #16 is more helpful. it's very close to what you are doing with the exception that instead of having the directory list already listed in the cells, it is excluding multiple subfolder paths (while also listing all files and folders recursively) during when my main code is run.. (from post #1). And also this only works for folder names, not folder paths

All easily changed

[anmac1789]

All easily changed

pardon me ?

[Paul_Hossler]

pardon me ?

the exception that instead of having the directory list already listed in the cells, it is excluding multiple subfolder paths (while also listing all files and folders recursively) during when my main code is run.. (from post #1). And also this only works for folder names, not folder paths

The noted exceptions (directory path in cells, folder names instead of paths, listing the files' parameters) can easily be added to the macro in #31

it is excluding multiple subfolder paths (while also listing all files and folders recursively)

If a subfolder is exclused, I don't see my macro listing anything within that subfolder. Can you provide an example?

I want this functionality to loop for all files and all subfolders within the specified exclude folder. Thank you & I hope it makes some sense

As others have pointed out, the requirements are less than clear. If a folder is excluded, then why loop?

[anmac1789]

The noted exceptions (directory path in cells, folder names instead of paths, listing the files' parameters) can easily be added to the macro in #31

I have tried to change the subfolder names into subfolder paths and still the paths are not being excluded. Your code works well for excluding subfolder names (but what if I have the same subfolder name in a different path, so all occurances of that folder path would be excluded, which is not the goal). therefore, specific subfolder paths are what is needed

[Paul_Hossler]

I changed one line in IsExcluded

To test I used full paths in my Exclude array, and added a 4th level subfolder 111CCC111 to other 2nd and 3rd folders in my test tree

Capture.JPG

Option Explicit


Const sPathTop As String = "D:\Test"


Dim aryExclude As Variant
Dim o As Long
Dim FSO As Object


Sub Start()
    aryExclude = Array( _
        "D:\Test\111\111CCC\111CCC111", _
        "D:\Test\333\333AAA", _
        "D:\Test\333\333BBB" _
        )
    
    o = 1


    ActiveSheet.Columns(5).Clear    '   testing purposes


    Set FSO = CreateObject("Scripting.FileSystemObject")


    Call GetFiles(FSO.GetFolder(sPathTop))


    MsgBox "Done"
End Sub






Sub GetFiles(oPath As Object)
    Dim oFolder As Object, oSubFolder As Object, oFile As Object


    If Not IsExcluded(oPath) Then
        ActiveSheet.Cells(o, 5).Value = oPath.path
        o = o + 1
        
        For Each oFile In oPath.Files
            ActiveSheet.Cells(o, 5).Value = oFile.path
            o = o + 1
        Next
        
        For Each oSubFolder In oPath.SubFolders
            Call GetFiles(oSubFolder)
        Next
    
    End If


End Sub


Private Function IsExcluded(p As Object) As Boolean
    Dim i As Long
    
    IsExcluded = True
    
    For i = LBound(aryExclude) To UBound(aryExclude)
        If UCase(p.path) = UCase(aryExclude(i)) Then Exit Function  '   <<<<<<<
    Next i
    
    IsExcluded = False
End Function

[anmac1789]

Hello, so it looks like this code does exactly what I want it to with me modifying something. I changed the parent folder code to list all files/folders within the parent directory (minus one less delimiter) you will see in the code (column B). The line of code that does that is:

Replace(Left(oPath.path, (Len(oPath.path) - Len(oPath.Name) - 1)), "\\?\", "") 'parent folder for subfolders

Replace(Left(oFile.path, (Len(oFile.path) - Len(oFile.Name) - 1)), "\\?\", "") 'parent folder for files

it seems like that even the excluding folder paths work for deeper nested subfolders as well which is precisely what I was after for SOO LONG. my final code is:

Option Explicit

Const sPathTop As String = ""


Dim aryExclude As Variant
Dim o As Long
Dim FSO As Object


Sub Start()
    aryExclude = Array("C:\test with spaces\subfolder 2", "C:\test with spaces\subfolder 1") 'place excluded folder paths here!!
    
    o = 2


    ActiveSheet.Columns("A:H").Clear    '   testing purposes


    Set FSO = CreateObject("Scripting.FileSystemObject")


    Call GetFiles(FSO.GetFolder("C:\test with spaces")) 'attach "\\?\" at the beginning for long folder path names! ex..'GetFiles("\\?\INSERT..." 'can also list multiple "Call GetFiles("\\?\[insert new folder path here]")" to list multiple folder paths all at once


End Sub


Sub GetFiles(oPath As Object)
    Dim oFolder As Object, oSubFolder As Object, oFile As Object
    Cells(1, 1).Value = "FILE/FOLDER PATH"
    Cells(1, 1).Offset(0, 1).Value = "PARENT FOLDER"
    Cells(1, 1).Offset(0, 2).Value = "FILE/FOLDER NAME"
    Cells(1, 1).Offset(0, 3).Value = "FILE or FOLDER"
    Cells(1, 1).Offset(0, 4).Value = "DATE CREATED"
    Cells(1, 1).Offset(0, 5).Value = "DATE MODIFIED"
    Cells(1, 1).Offset(0, 6).Value = "SIZE"
    Cells(1, 1).Offset(0, 7).Value = "TYPE"


    If Not IsExcluded(oPath) Then
        ActiveSheet.Cells(o, 1).Value = Replace(oPath.path, "\\?\", "")
        ActiveSheet.Cells(o, 2).Value = Replace(Left(oPath.path, (Len(oPath.path) - Len(oPath.Name) - 1)), "\\?\", "") 'parent folder for subfolders
        ActiveSheet.Cells(o, 3).Value = oPath.Name
        ActiveSheet.Cells(o, 4).Value = "folder"
        ActiveSheet.Cells(o, 5).Value = oPath.datecreated
        ActiveSheet.Cells(o, 6).Value = oPath.datelastmodified
        o = o + 1
        
        For Each oFile In oPath.Files
            ActiveSheet.Cells(o, 1).Value = Replace(oFile.path, "\\?\", "")
            ActiveSheet.Cells(o, 2).Value = Replace(Left(oFile.path, (Len(oFile.path) - Len(oFile.Name) - 1)), "\\?\", "") 'parent folder for files
            ActiveSheet.Cells(o, 3).Value = oFile.Name
            ActiveSheet.Cells(o, 4).Value = "file"
            ActiveSheet.Cells(o, 5).Value = oFile.datecreated
            ActiveSheet.Cells(o, 6).Value = oFile.datelastmodified
            ActiveSheet.Cells(o, 7).Value = oFile.Size
            ActiveSheet.Cells(o, 8).Value = oFile.Type
            o = o + 1
        Next
        
        For Each oSubFolder In oPath.SubFolders
            Call GetFiles(oSubFolder)
        Next
        
        With Range("E:F")
        .NumberFormat = "dddd mmmm dd, yyyy H:mm:ss AM/PM" 'long file date and time
        End With
    
    End If
    
        ActiveSheet.UsedRange.EntireColumn.AutoFit


End Sub


Private Function IsExcluded(p As Object) As Boolean
    Dim i As Long
    
    IsExcluded = True
    
    For i = LBound(aryExclude) To UBound(aryExclude)
        If UCase(p.path) = UCase(aryExclude(i)) Then Exit Function  '   <<<<<<<
    Next i
    
    IsExcluded = False
End Function

I have attached a zip file which includes the files and folders that need to be placed on the C:\ drive for anyone looking to see & use my code for a complete solution.

It looks like there's no other functionality I need to add to this code. This code is basically what I was looking for since November 2020. This is what I wanted to help me see where so many duplicates are on my computer instead of opening up many folders all at once and sorting them manually.

Another program that I used is voidtool's program called 'everything' (voidtools) but the shortcomings it has is that it cannot list folders and files together independently. A second program that I found just yesterday which does list them independently together is Jam software's program called 'ultrasearch' (UltraSearch). The shortcoming of this is that the search syntax is basically really horrible and tedius and doesn't have much flexibility to search and find results that 'everything' has which is what I like much better. If I need additional functionality added then I have this code as a starting basis in this post as reference.

This is why I was looking for an excel solution and this is what I wanted so thank you to those who helped contributed code to this thread & took their time to respond to my annoying posts. I sincerely appreciate all your efforts!!!