Consulting

Page 5 of 7 FirstFirst ... 3 4 5 6 7 LastLast
Results 81 to 100 of 125

Thread: Combine recursive listing with excluded code

  1. #81
    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

  2. #82
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #83
    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....

  4. #84
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    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
    Last edited by Paul_Hossler; 05-10-2021 at 11:54 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #85
    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"
    Last edited by anmac1789; 05-10-2021 at 07:38 PM.

  6. #86
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #87
    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..??

  8. #88
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    Capture.JPG
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #89
    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
    Attached Files Attached Files
    Last edited by anmac1789; 05-11-2021 at 01:36 PM.

  10. #90
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    _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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #91
    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.
    Attached Files Attached Files
    Last edited by anmac1789; 05-11-2021 at 05:58 PM.

  12. #92
    So did the longer folder path work with your edition of the workbook?

  13. #93
    it seems like everyone's on summer vacation lol

  14. #94
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    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?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #95
    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.

  16. #96
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,187
    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 ?

  17. #97
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    Quote Originally Posted by anmac1789 View Post
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #98
    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

  19. #99
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,827
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  20. #100
    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •