Combine recursive listing with excluded code

[anmac1789]

For example, in the exclude list, I can exclude subfolders such as "C:\Users\azeem\Downloads\Test Files\test one\subfolder 2" but lets say I want to exclude a specific file inside that folder "C:\Users\azeem\Downloads\Test Files\test one\subfolder 2\Exclude file2-1.txt" not necessary the entire subfolder

[Paul_Hossler]

I added in Col E a list of files that are always excluded regardless of what folder they're in

If a folder is excluded in Col C that all subfolders and all files are excluded

Capture.JPG

so File1.txt and File2.txt are excluded

In ver 18 the gray shaded are not included

[anmac1789]

ohh i see it now...so I can change

If UCase(p.Name) = UCase(aryFiles(i)) Then Exit Function

for both isFolderExcluded and isFileExcluded into something like

If UCase(p.path) = UCase(aryFiles(i)) Then Exit Function

if I wanted the complete path for each specific folder or specific file to be excluded....

[Paul_Hossler]

All I can say is ...


1. This recursively goes down all the paths in Col A

    For i = LBound(aryPaths) To UBound(aryPaths)
        Call GetFiles(oFSO.GetFolder(RemovePrefix(aryPaths(i))))
    Next

2. If the path is in Col C, then it stops going down that path

    If isFolderExcluded(oPath) Then Exit Sub  '   stops recursion
        
    Call AddFileFolder(oPath)

3. If the file name is in Col E then it does NOT get added to the list

    For Each oFile In oPath.Files
        If Not isFileExcluded(oFile) Then Call AddFileFolder(oFile)
    Next

4. So I'm not sure how to interpret this

if I wanted the complete path for each specific folder or specific file to be excluded...

Do you mean that if somewhere in the folder tree here's a file listed in Col E, then delete the entire folder tree?


Edit:

You can change

Const incFilesFolders As Long = 10

to

Const incFilesFolders As Long = 100

That tells Redim Preserve how many more spaces to allocate. I had it set low for testing, but might slow things down

[anmac1789]

if I wanted the complete path for each specific folder or specific file to be excluded...

What I mean by this is if I wanted a specific file path to be excluded such as "C:\test with spaces\subfolder 1\file1.txt" I can change the code from:

For i = LBound(aryFiles) To UBound(aryFiles)
        If UCase(p.Name) = UCase(aryFiles(i)) Then Exit Function
    Next i

to

For i = LBound(aryFiles) To UBound(aryFiles)
        If UCase(p.Path) = UCase(aryFiles(i)) Then Exit Function
    Next i

instead of file1.txt being excluded everywhere in every subfolder, by changing the p.Name to p.Path, i can pin-point the exact file path to be excluded

That tells Redim Preserve how many more spaces to allocate. I had it set low for testing, but might slow things down

Const incFilesFolders As Long = 10

Do you mean resizing the array by 10 at code execution?

It seems like for long folder/file paths which is 246 characters in length in cell A1, there is a run-time error 9 - subscript out of range occuring and

For i = LBound(aryExcludes) To UBound(aryExcludes)

is highlighted after i click debug under the sub "isFolderExcluded"

[Paul_Hossler]

What I mean by this is if I wanted a specific file path to be excluded such as "C:\test with spaces\subfolder 1\file1.txt" I can change the code from:

To exclude a specific file from a specific path, you'd have to do it by hand

As you found, trying the do that with the macro, generates errors.

So you don't want to remove ALL instances of file1.txt, just the one in a specific folder????

It can be done, but seems like a lot more work that it's worth. I'll think about it, but it'd most likely slow things down

[anmac1789]

how nuch slower will it be ?? as long as it doesnt generate errors i think i am fine with waiting a bit...

Can there be two versions ?? one to remove all instances by using its name and another to remove jist one file path pointing to a specific file..??

[Paul_Hossler]

Capture.JPG

[anmac1789]

It seems like when I search for a path longer than 255 characters, it doesn't list the subfolder contents (deeper nested files or subfolders). The search stops at that point. I looked on this page here: https://support.microsoft.com/en-us/...7-269d656771c3 and it says that the character limit in each cell is 32,767 characters.

However, long folder path searching worked in excludes_14 so I am not sure what the difference is between excludes_14 and Excludes_19. Here is an example path length: C:\Users\username\Downloads\something college 2020\f201x sem whatever\Users\whateverusername\Desktop\desktop folders\ALL STUFF\FALL 201x SAMESTER 3 CORSAIR USB 3.0\oct 22, 201x\1yeaF --Hospital Techniques Principles I (Combined) - HOSP200C019_201x18\Content\week 6 in order backup\Math Answer Keys which is 297 characters long which works in Excludes_14 but not in Excludes_19

Option Explicit


Const sPathTop As String = "" 'MAIN PATH GOES HERE WITH \\?\ PREFIX


Const colPath As Long = 1
Const colParent As Long = 2
Const colName As Long = 3
Const colFileFolder As Long = 4
Const colCreated As Long = 5
Const colModified As Long = 6
Const colSize As Long = 7
Const colType As Long = 8


Dim aryExclude As Variant
Dim rowOut As Long
Dim oFSO As Object
Dim wsOut As Worksheet
Dim rPrev As Range


Sub Start()
    Dim rowStart As Long
    Dim oFile As Object
    
    aryExclude = Array("")


    Init


    rowStart = rowOut


    Call GetFiles(oFSO.GetFolder(sPathTop))
    
    wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"
    
    RemoveDups
    
    Cleanup
End Sub


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


    If IsExcluded(oPath) Then Exit Sub  '   stops recursion
        
    Call ListInfo(oPath, "Subfolder")
    
    For Each oFile In oPath.Files
        Call ListInfo(oFile, "File")
    Next
    
    For Each oSubFolder In oPath.SubFolders
        Call GetFiles(oSubFolder)
    Next
    
End Sub


'============================================================================
Private Sub Init()
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    If IsArray(aryExclude) Then
        For i = LBound(aryExclude) To UBound(aryExclude)
            aryExclude(i) = CStr(aryExclude(i))
        Next i
    End If
    
    Set wsOut = Worksheets("Files")
    
    With wsOut
        'get last used row, or 1 if empty
        rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        If rowOut = 1 Then          '   blank sheet
            .Cells(rowOut, colPath).Value = "FILE/FOLDER PATH"
            .Cells(rowOut, colParent).Value = "PARENT FOLDER"
            .Cells(rowOut, colName).Value = "FILE/FOLDER NAME"
            .Cells(rowOut, colFileFolder).Value = "FILE or FOLDER"
            .Cells(rowOut, colCreated).Value = "DATE CREATED"
            .Cells(rowOut, colModified).Value = "DATE MODIFIED"
            .Cells(rowOut, colSize).Value = "SIZE"
            .Cells(rowOut, colType).Value = "TYPE"
        End If
        
        rowOut = rowOut + 1
        
        'save the previous data
        Set rPrev = wsOut.Cells(1, 1).CurrentRegion
    End With
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
End Sub


Private Sub Cleanup()
    wsOut.Columns(colName).HorizontalAlignment = xlLeft
    wsOut.Columns(colCreated).NumberFormat = "dddd, mmmm d, yyyy h:mm:ss AM/PM"
    wsOut.Columns(colModified).NumberFormat = "dddd, mmmm d, yyyy h:mm:ss AM/PM"
    wsOut.Columns(colSize).NumberFormat = "#,##0,.0 ""KB"""
    
    wsOut.Cells(1, 2).CurrentRegion.entireColumnn.AutoFit
    
    Application.ScreenUpdating = True


    MsgBox "Done"
End Sub


Private Sub RemoveDups()
    wsOut.Cells(1, 1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
End Sub


'   IFolder object
'       Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive,
'       Files, IsRootFolder, Name, ParentFolder (IFolder), Path,
'       ShortName, ShortPath, Size, SubFolders, Type


'   iFile object
'       Attributes, DateCreated, DateLastAccessed, DateLastModified, Drive (IDrive),
'       Name, ParentFolder (IFolder), Path, ShortName, ShortPath, Size, Type
'       Attributes


Private Sub ListInfo(oFolderFile As Object, sType As String)
    With oFolderFile
        wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.Path)
        wsOut.Cells(rowOut, colParent).Value = RemovePrefix(oFSO.GetParentFolderName(.Path))  'oFSO.GetParentFolderName(.Path) or .ParentFolder.Path
        wsOut.Cells(rowOut, colName).Value = .Name
        wsOut.Cells(rowOut, colFileFolder).Value = sType
        wsOut.Cells(rowOut, colCreated).Value = .DateCreated
        wsOut.Cells(rowOut, colModified).Value = .DateLastModified
        wsOut.Cells(rowOut, colSize).Value = .size
        wsOut.Cells(rowOut, colType).Value = .Type
    End With
    
    rowOut = rowOut + 1
End Sub

Private Function IsExcluded(p As Object) As Boolean
    Dim i As Long
    
    If IsEmpty(aryExclude) Then
        IsExcluded = False
        Exit Function
    End If
    
    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

Private Function RemovePrefix(s As String) As String
    If Len(s) < 5 Then
        RemovePrefix = s
    Else
        RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
    End If
End Function

[Paul_Hossler]

_14 was a long time and many mods ago

I have Long NTFS File names enabled, but Explorer won't let me create a really long one to test



In Main() change the marked line to remove the RemovePrefix( ..... ) and see if it works better

Sub Main()
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    Init


    ReDim aryFilesFolders(1 To incFilesFolders)
    For i = LBound(aryPathsToInclude) To UBound(aryPathsToInclude)
        Call getFiles(oFSO.GetFolder(aryPathsToInclude(i)))
    Next
    ReDim Preserve aryFilesFolders(1 To cntFilesFolders)
    
    listData
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
End Sub

[anmac1789]

I have used Vice versa from https://www.tgrmn.com/ to create a long folder path and then make another directory with a longer folder path and then combine one folder path with another to make a folder path longer than 255 characters. This is how I was able to make it longer than 255 or 260. I've created a path that is 452 characters in length.

[anmac1789]

So did the longer folder path work with your edition of the workbook?

[anmac1789]

it seems like everyone's on summer vacation lol

[Paul_Hossler]

Sorry, I didn't realize that you wanted me to create a bunch of long file/folder names

I really don't have any reason to

Did the last version work with your LFNs?

[anmac1789]

Sorry, I didn't realize that you wanted me to create a bunch of long file/folder names

I had attached a .zip file called "very long folder path.zip" in post #91 which has a folder path that is longer than 255 characters..

In Main() change the marked line to remove the RemovePrefix( ..... ) and see if it works better

I removed the long prefix and it doesn't work fully. It only lists folders for some reason but not files. Also, it doesn't list the correct number of subfolders, for some reason a few of them are left out. Testing with "very long folder path.zip" will reveal those files that are not listed.

When I was using excludes_14, there was no problem listing subfolders and/or any files. Even excludes_5 was working. There is something in the code which is very different from excludes_14 and excludes_5 im assuming.

[snb]

Have you made any 'progress' in this thread since http://www.vbaexpress.com/forum/show...l=1#post407423 ?
If only 1 foldername is a problem, you should change that foldername instead of asking in vain for code that is handling this anomaly. I wonder who in this forum is benefitting from the information in this thread ?

[Paul_Hossler]

I had attached a .zip file called "very long folder path.zip" in post #91 which has a folder path that is longer than 255 characters..
There is something in the code which is very different from excludes_14 and excludes_5 im assuming.

I had added some condition flags to avoid unnecessary checks, but apparently didn't cover all the bases

Try 20

[anmac1789]

I'm getting Run-time error 5: invalid procedure call or argument error and this line is highlighted:

i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0)

in the sub procedure called isFolderExcluded

[Paul_Hossler]

Works OK for me 0 the 'Files' worksheet completes and has all data that I think it should.

I'm testing with your deeply nested folder tree

Restart the PC and see if that helps

If/when it happens again, see what the .Name of the folder that it's checking is

Capture.JPG

[anmac1789]

So i restarted my laptop and I re-tested the excel workbook, I am still getting the same error

If/when it happens again, see what the .Name of the folder that it's checking is

What do you mean by this? your example screenshot is the same as in my workbook..