Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 67

Thread: Combine recursive listing with excluded code

  1. #21
    If you don't mind me asking, I've had a problem in the past that lets say u want multiple properties and methods for a given range for example and I didn't know how to use that effectively so basically I was stuck writing:

    range("A1").end(xlup).offset(row,column).other methods.other properties and so on and so on using these periods and sometimes I would create new variables and add to the range and then before I knew it, i was so confused beyond my understanding...

    So are you saying that with/end wich, we can add multiple properties and methods to an object?

    Instead of using Range("A65000") [probably Excel 2003] which will return wrong answer if more than 65000 rows, I prefer (again, personal choice/style) to use the built in Excel capabilities (#1 below). I've seen Range("A1000") used which will probably fail sooner or later. The #1 works in 2003 and with 1M+ rows, and in 64 bit Excel and if they ever have Excel with 100M+ rows it will still work. More transportable
    So you're saying that in #1 here
    rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row               ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #1
    this will accept more than 1 million rows if the data has more than 1 million rows intead of using Range("A65000")

    Just using Cells() refers to the ActiveSheet, which might not be the worksheet that you intended (I've seen many hard to trace bugs because the macro was checking or writing to the wrong worksheet). By using wsOut.Cells(...) (#2 below) it's clear that you're using the cell that you think your are
    in #2 here:
                .Cells(rowOut, 1).Value = "FILE/FOLDER PATH"            ' <<<<<<<<<<<<<<<<<<<<<<<<<<< #2
    wsOut.Cells(...) was not used here..so how does excel determine that we are using wsOut worksheet?

    The bracketing With / End With (#3) is just a way to keep the code more readable (again, personal opinion) since all of the <dot>Cells(...) within are clearly part of the wsOut object
    Readibility and simplicity is what I strive for so if the code can be written more simply then I'm all for it..if you know any tips and tricks how you organize your code in day to day tasks or in this forum I would like to learn about it..


  2. #22
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    Didn't mean to sound confusing

    1. I was saying that Excel and VBA have a rich set of properties in the object model, so I try to use them as much as I can

    For example, the Worksheet object has a .Rows property that returns the rows on the worksheet as a Range object, and the .Rows.Count property on a Range object tells you how many rows are in the range

    So instead of using a 'Magic Number' like 1000 (a magic number is a number that just shows up) in

    N = Range("A1000").End(xlUp).Row + 1
    to get the next blank row, I (again it's just personal style) like to use something like

    N = Worksheets("Data").Cells(Worksheets("Data").Rows.Count,1) .End(xlUp).Offset(1,0).Row
    So are you saying that with/end with, we can add multiple properties and methods to an object?
    Not exactly. It saves having to repeatedly specify the object. The With + End With is sort of a shortcut to save typing (for me that means less chance of error) and to improve readability

    The 'dot' prefix is used to 'tie' something to it's parent so just using <dot>Cells(...) the 'With object is the parent

    With Worksheets("Data")
         N = .Cells(.Rows.Count,1) .End(xlUp).Offset(1,0).Row 
         .Cells(N,1).value = "Yes"
         .Cells(N,2).Resize(1,10).Interior.Color = vbRed
    
        .Cells(1,1).CurrentRegion.EntireColumn.Autofit
    End With
    No Magic Numbers and will work regardless of the number of WS rows in the version of Excel being used

    2. I think it's very important to explicitly refer to objects

    Say there are two sheets, "Data" which has 100 rows used and "SomethingElse which has 500 rows used

    Using the bit of code just above, doesn't matter if Data is the ActiveSheet or if "SomethingElse" is the Activesheet, I'll get "Yes' and 10 red cells in row 101


    If I were less explicit (below) and just ASSUMED that Data was always going to be the Activesheet when the macro was run, it would go to which ever the Activesheet really was (you know you can never trust users to leave things alone)

    N = Cells(.Rows.Count,1) .End(xlUp).Offset(1,0).Row 
    Cells(N,1).value = "Yes"
    Cells(N,2).Resize(1,10).Interior.Color = vbRed
    Using the bit of code just above, I could get "Yes' and 10 red cells in row 101 of "Data" or I could get "Yes' and 10 red cells in row 501 of "SomethingElse"
    ---------------------------------------------------------------------------------------------------------------------

    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. #23
    I am confused about this line of code:
    Private Function RemovePrefix(s As String) As String
        RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
    End Function
    What does this mean in relation to
    sParentFolder = RemovePrefix(sPathTop)
    and
    sParentFolder = RemovePrefix(oPath.path)

  4. #24
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,126
    Function F_RemovePrefix(s As String) As String
      F_RemovePrefix = mid(s,5)
    End Function
    But as a function it's unnecessarily complicated

    this would do as well

    if left(s,4)="\\?\" then s = mid(s,5)

  5. #25
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    Quote Originally Posted by anmac1789 View Post
    I am confused about this line of code:

    Private Function RemovePrefix(s As String) As String
        RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
    End Function
    The long path names is something I couldn't / didn't test, but the function takes a string and if it begins with "\\?" remove those characters and returns what's left, otherwise it just returns the input string

    Instead of using the IIF() function, If / Then / Else / EndIf would have worked

    https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue

    IIf(expr, truepart, falsepart)

    The IIf function syntax has these named arguments:
    Part Description
    expr Required. Expression that you want to evaluate.
    truepart Required. Value or expression returned if expr is True.
    falsepart Required. Value or expression returned if expr is False.
    ---------------------------------------------------------------------------------------------------------------------

    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

  6. #26
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    Quote Originally Posted by snb View Post
    Function F_RemovePrefix(s As String) As String
      F_RemovePrefix = mid(s,5)
    End Function
    But as a function it's unnecessarily complicated

    this would do as well

    if left(s,4)="\\?\" then s = mid(s,5)
    Well, the first version will ALWAYS remove the first 4 characters, even if they're not "\\?"

    The use of Mid() might seem to be a little faster (at least less typing) since it replaces a Len() and a Right(), but according to

    https://www.aivosto.com/articles/stringopt2.html

    Left$, Right$ and Mid$. Performance keeps at the degraded level with this group of functions. These functions create new strings by copying some characters in the input string. These are the only functions that can access the individual characters in a string. As you can see, Mid$ is slower than Left$ or Right$. This means you should use Left$ and Right$ when possible and only resort to Mid$ when you really need to access characters in the middle.
    Capture.JPG

    Even called a few 1000 times, either won't make a perceptable wall clock difference. Probably get more performance by not using the Variant form of the functions and using the String versions (Mid$(...) instead of Mid(...)
    ---------------------------------------------------------------------------------------------------------------------

    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. #27
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,126
    You shouldn't call a function if it shouldn't be applied.
    You shouldn't test that after calling the function, but before

    Sub M_show()
      if left("text"="\\?\" then y = F_RemovePrefix('text")
    End Sub
    
    Function F_RemovePrefix(s As String) As String
      F_RemovePrefix = mid(s,5)
    End Function
    I'd prefer, instead of these lines of code:

    if left(s,4)="\\?\" then s = mid(s,5)

  8. #28
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    Quote Originally Posted by snb View Post
    You shouldn't call a function if it shouldn't be applied.
    You shouldn't test that after calling the function, but before
    Agree, but since the functionality was required in multiple places, I opted to simplify the lines where it was used by putting the IIF() in the function

    The largest savings I've seen for 'testing first' is when using Replace().


    https://www.aivosto.com/articles/stringopt.html#whyslow


    Replace or not?

    The following tip might be obvious, but it wasn't to us. It makes no sense to call Replace if you're not likely to replace anything. Replace runs slowly. Replace always creates a copy of the input string, even if no replacement occurs, and making the copy is slow. If a replacement is unlikely, verify first (with InStr or InStrB, for example) that there is something you need to replace.

    If InStr(Text$, ToBeReplaced$) <> 0 Then
    Text$ = Replace(Text$, ToBeReplaced$, "xyz")
    End If

    If a replacement is likely or certain to occur, there is no need to call InStr. It just adds an extra burden.
    ---------------------------------------------------------------------------------------------------------------------

    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. #29
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,126
    You are quoting users with outdated Intel 88 chipsets.
    The statement 'Replace runs slowly' is 'so '80-ies' (40 years ago).


  10. #30
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    Quote Originally Posted by snb View Post
    You are quoting users with outdated Intel 88 chipsets.
    The statement 'Replace runs slowly' is 'so '80-ies' (40 years ago).


    Ah, well ... so am I
    ---------------------------------------------------------------------------------------------------------------------

    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. #31
    So basically in “Excludes_7” workbook, there are several problems:

    1. The parent folder displays correctly for files, but does not display correctly for subfolders. For example,

    path (column 1): C:\test\subfolder 1\file 1.txt
    parent folder (column 2): C:\test\subfolder 1

    which is correct

    For subfolders,

    path (column 1): C:\test\subfolder 1
    parent folder (column 2): C:\test\subfolder 1

    which is not correct because subfolder 1 resides inside the C:\test folder. it should say, parent folder (column 2): C:\test

    The parent folder and the path cannot be the same it just doesn't make sense

    2. using the "\\?\ " notation does not display correctly for both column 1 (FILE/FOLDER PATH) and column 2 (PARENT FOLDER)

    (post 28)
    Agree, but since the functionality was required in multiple places, I opted to simplify the lines where it was used by putting the IIF() in the function
    You are correct in the sense that removing the brackets from 2 columns was necessary but, in doing so, column 2 had the wrong parent folder path (see #1 above). Therefore, this code needs to be adjusted:

    Private Function RemovePrefix(s As String) As String
        RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s)
    End Function
    to firstly, remove longer folder path prefix from both columns and secondly, shorten the parent folder path as #1 above.

    I have made a code that shortens what was written in my code

    wsOut.Cells(rowOut, colParent).Value = Right(Left(.path, Len(.path) - Len(.Name) - 1), Len(Left(.path, Len(.path) - Len(.Name) - 1)) - 4)
    to several other ways as shown in the attachment. If it's not shown correctly, then I am uploading my workbook to show those formulas using excel's built-in functions
    Attached Images Attached Images
    Attached Files Attached Files

  12. #32
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    I left .Path in instead of changing it to .ParentFolder when I made some of your other changes and didn't notice the problem

    Instead of the worksheet formulas all you need I think is the two changes below

    For safety's sake, I added a check to RemovePrefix


    Wasn't clear if those were the only issues in your post


    Sub Start()
        Dim rowStart As Long
    '    aryExclude = Array( _
    '        "\\?\D:\Test\111\111CCC\111CCC111", _
    '        "\\?\D:\Test\333\333AAA", _
    '        "\\?\D:\Test\333\333BBB" _
    '        )
        
    '    aryExclude = Array("\\?\D:\Test\111")
        aryExclude = Array("\\?\D:\Test\222")
    
    
        Init
    
    
        rowStart = rowOut
        sParentFolder = RemovePrefix(sPathTop)
    
    
        Call GetFiles(oFSO.GetFolder(sParentFolder))         '   <<<<<<<<<<<<<<<<<<<<<<
        
        wsOut.Cells(rowStart, colFileFolder).Value = "Parent Folder"
        
        If numRuns > 0 Then 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
            
        sParentFolder = RemovePrefix(oPath.ParentFolder) '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
            
        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
    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

  13. #33
    it seems like there is still a problem with the parent folder column (column 2). For example,

    folder path : C:\test with spaces
    parent folder: C:\
    which is correct. But,

    folder path: C:\test with spaces\file1.txt
    parent folder: C:\ ????
    which is not correct because, file1.txt resides in the C:\test with spaces\ folder

    I just can't seem to understand what & where "s" variable is and where its defined in the code, because the only s i see is in "sParentFolder" is this why you have chosen s?
    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; 02-22-2021 at 01:11 AM.

  14. #34
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    1. Went back to basics with ver 9

    Capture.JPG



    2. The 's' is just sort of a placeholder for the function call. When the function REmovePrefix is called a 'real' parameter is passed and the function processes the 'real' one instead of the placeholder 's'

    I called it 's' because I was tired of typing (not a good reason) and it was a string. I should called it something more meaningful like "PathToHaveTheBackslashParameterRemoved" which would make the function definition something like

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


    3. Single step through the test() sub to see calling parameters in action


    Option Explicit
    
    Sub test()
        MsgBox RemovePrefix("\\?\SOMETHING")
        MsgBox RemovePrefix("\\?\ANOTHER SOMETHING")
        MsgBox RemovePrefix("NOTHING")
    End Sub
    
    
    
    
    
    
    
    
    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
    ---------------------------------------------------------------------------------------------------------------------

    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. #35
    2. The 's' is just sort of a placeholder for the function call. When the function REmovePrefix is called a 'real' parameter is passed and the function processes the 'real' one instead of the placeholder 's'
    okay so I think I kind of understand the placeholder "s" inside the function. When it's used for a actual path then the s gets replaced with something like o.path or whatever and then the path gets the backslack prefix removed. Am I correct in thinking like this?

    I have a couple questions

    1. When taking further look at your code it seems like (with my n00b basic skills), there could be 2 functions - one function to remove the prefix, second function to take the file/folder path (from column 1) and subtract it from file/folder name to give the parent folder. Does this make the code more efficient or can this be all done within one line of code using the IIF() function?

    What I mean by this is for function 1 which removes the prefix to the left of the folder/file path (column 1):
    Private Function RemovePrefix(s As String) As String
        RemovePrefix = IIf(Left(s, 4) = "\\?\", Right(s, Len(s) - 4), s) 'Right(s, Len(s))
    End Function
    Function 2 to return the parent folder (column 2) of each folder/file path (from column 1)
    Private Function parentfolderfilepath(p As String, s As String) As String
        parentfolderfilepath = IIf(Left(p, 4) = "\\?\", Mid(Right(p, Len(p) - 4), 1, Len(Right(p, Len(p) - 4)) - Len(s)), s)
    End Function
    So combining function 2 and function 1:
    wsOut.Cells(rowOut, colPath).Value = RemovePrefix(.path)
    wsOut.Cells(rowOut, colParent).Value = parentfolderfilepath(.path, .Name)
    But, I just realized that ".ParentFolder" does basically similar to the code above so now which one do I use? Because in 9th revision of the code, it seems like " sParentFolder" is now removed and replaced with ".ParentFolder". Also, what is the difference between these two, they were used in the code in the 7th-9th revision of code, I experimented without .path and it seems to work but i'm just wondering is there any difference?

    wsOut.Cells(rowOut, colParent).Value = .ParentFolder.path
    
    AND 
    
    wsOut.Cells(rowOut, colParent).Value = .ParentFolder

    2. Please take a look at the attachment to see pictures of the problem with "Excludes_9". In this code, after including 2 exclude subfolder paths and executing the code a second time, "subfolder 1" goes missing. I am assuming this also happens with other subfolders even with the same names in deeper levels after the 2nd, 3rd, 4th runs.

    The reason why I chose column 1 (file/folder path) to double check duplicates after the 1st run, rather than column 2 (parent folder), is because it uniquely checks each file/subfolder/parent directory during 2nd run, 3rd run, 4th run, etc... for duplicate entries. If parent folder was being checked then it may remove another different file/folder belonging in the same parent directory (ie. having the same parent folder). By seeing that, I made the following changes to the code:

    'look at newly added lines (not in rPrev) and if PARENT FOLDER is in rPrev delete from newly added
    Private Sub RemoveDups()
        Dim rowNew As Long
        
        For rowNew = wsOut.Cells(1, 1).CurrentRegion.Rows.Count To rPrev.Rows.Count + 1 Step -1
            If Application.WorksheetFunction.CountIf(rPrev.Columns(colPath), wsOut.Cells(rowNew, colPath).Value) > 0 Then
                'mark special
                wsOut.Cells(rowNew, colPath).Value = True
            End If
        Next rowNew
        
        On Error Resume Next
        wsOut.Columns(colPath).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
        On Error GoTo 0
    
    
    End Sub
    Basically, I replaced all instances of
    colParent
    with
    colPath
    . Please let me know if I'm doing it right or wrong (still a vba n00b).

    Attachment 27999
    Attached Files Attached Files

  16. #36
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    okay so I think I kind of understand the placeholder "s" inside the function. When it's used for a actual path then the s gets replaced with something like o.path or whatever and then the path gets the backslack prefix removed. Am I correct in thinking like this?
    That's the way that parameters are passed to functions and subs



    But, I just realized that ".ParentFolder" does basically similar to the code above so now which one do I use? Because in 9th revision of the code, it seems like " sParentFolder" is now removed and replaced with ".ParentFolder". Also, what is the difference between these two, they were used in the code in the 7th-9th revision of code, I experimented without .path and it seems to work but i'm just wondering is there any difference?
    .Path is the default for the Folder object. I just like to specify it

    Capture.JPG



    You're using the FileSystemObject which has a rich set of methods and properties

    https://ss64.com/vb/filesystemobject.html

    The Folder and the File objects have the properties that you were looking to list so it's just easier to use them

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

    Please take a look at the attachment to see pictures of the problem with "Excludes_9". In this code, after including 2 exclude subfolder paths and executing the code a second time, "subfolder 1" goes missing. I am assuming this also happens with other subfolders even with the same names in deeper levels after the 2nd, 3rd, 4th runs.

    What is the definition of 'duplicate' over different runs?



    Didn't understand your pictures

    With spaces / without spaces don't matter

    Test 1 - Do first run and it's Excludes, save copy of Files worksheet as Files-1, and clear Files WS. Is it correct?

    Test 2 - Do second run and it's Excludes, save Files worksheet as Files-2, , and clear Files WS.Is it correct?

    Test 3 - Do run 1 and it's excludes and then do run 2 it's Excludes. Is it correct?
    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

  17. #37
    What is the definition of 'duplicate' over different runs?
    What I mean is, running the code recursively lists all folders/files and repeats the list after each subsequent run producing duplicate entries for the same parent folder. Basically it is this piece of code here:

    Private Sub RemoveDups()
        Dim rowNew As Long
        
        For rowNew = wsOut.Cells(1, 1).CurrentRegion.Rows.Count To rPrev.Rows.Count + 1 Step -1
            If Application.WorksheetFunction.CountIf(rPrev.Columns(colParent), wsOut.Cells(rowNew, colParent).Value) > 0 Then
                'mark special
                wsOut.Cells(rowNew, colParent).Value = True
            End If
        Next rowNew
        
        On Error Resume Next
        wsOut.Columns(colParent).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
        On Error GoTo 0
    
    
    End Sub
    However, I just thought of something. What if I produce a list of files and folders for a parent directory in run 1 but want to exclude a folder path AFTER run 1 for run 2 and shorten the results? Would I just need to add another code or can I extend the above code?


    Didn't understand your pictures

    With spaces / without spaces don't matter

    Test 1 - Do first run and it's Excludes, save copy of Files worksheet as Files-1, and clear Files WS. Is it correct?

    Test 2 - Do second run and it's Excludes, save Files worksheet as Files-2, , and clear Files WS.Is it correct?

    Test 3 - Do run 1 and it's excludes and then do run 2 it's Excludes. Is it correct?
    The reason for the spaces inside the folder names is just something random I did, i understand it doesn't matter and that excel vba accepts spaces in file/folder names. The pictures demonstrate that after run 2, a folder from run 1 was deleted. I am attaching a workbook which explains what those pictures mean
    Attached Files Attached Files

  18. #38
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    However, I just thought of something. What if I produce a list of files and folders for a parent directory in run 1 but want to exclude a folder path AFTER run 1 for run 2 and shorten the results? Would I just need to add another code or can I extend the above code?
    Using my folder structure I made 4 runs

    1. D:\Test with no Excludes - gave row 2 - 104 on 'Files'

    2. D:\Test2 with no Excludes - gave row 105 - 206 on 'Files'

    3. D:\Test with no Excludes (same as run #1) - added the lines 207 - 309 when recursion, but then deleted them as dups from run #1

    4. D:\Test with Excludes - added the lines 207 - 271 when recursion, but then deleted them as dups from run #1


    Now ....

    Are you saying that you want to always (a) remove duplicate PATHs from the entire list after each run, or (b) duplicate PARENT FOLDERs from the entire list after each run, or (c) something else?

    (a) will delete duplicate FILES and (b) will delete duplicate folders and all the files in the folder from the list


    Right now, only the new appended lines are check for dups

    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

  19. #39
    I tested out revision 10 of the workbook and still having the same problem

    I want to do:
    (a) remove duplicate PATHs from the entire list after each run
    by checking from column A and
    (b) will delete duplicate folders and all the files in the folder from the list
    by checking from column A for each run. I am attaching a workbook to illustrate the problem from 2 runs

    Can you send me your directory files so I can work with it as well ?
    Attached Files Attached Files

  20. #40
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,679
    Location
    If all you want to do is keep a 'running list' of PATHs that have passed, a simple .RemoveDuplicates() would work


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

    In attached sheet 'BeforeRemovingDuplicates'

    Green = Test2 with no excludes

    Yellow= Test with Test\222 excluded

    Blue = Test2 again with no excluded BEFORE removing duplicates

    Sheet 'Files' is final result

    I attached a Tree listing of my two test folder structures
    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

Posting Permissions

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