Consulting

Page 4 of 7 FirstFirst ... 2 3 4 5 6 ... LastLast
Results 61 to 80 of 125

Thread: Combine recursive listing with excluded code

  1. #61
    I was looking at worksheetfunction.unique() from this website: https://www.reddit.com/r/excel/comme...s_and_methods/ to extract unique values but it seem it doesn't print the unique values to a range, it only stores them within each element of the array

  2. #62
    I am trying to use cells to execute my code for each folder path and run my code but the furthest that I have gone is just getting an example of a for each...next loop and don't know how to proceed. Here is my code:

    Sub example()
    
    
    Dim cell As Range
    
    
    For Each cell In Range("A2", Range("A2").End(xlDown))
    cell.Font.Color = 255
    Next cell
    
    
    End Sub
    The problem with this code is that if only cell A2 is filled in and then I run Sub example(), I get a "Code execution has been interrupted" error. However, when I have 2 cells filled in, cell A2 and A3, then there is no error. Why doesn't one cell (cell A2) work ??

    This is an example, but what I want to do is instead of manually inserting each file/folder path inside sPathTop and executing my main code, I want to manually write the folder path(s) in suppose cell A1 and then run my main code on this folder path into another worksheet. This way, I wouldn't have to go back and change sPathTop for each additional folder path that I add. it solves one problem but it gives another. Finding duplicates is already inserted on a different sub which we solved using hashes, but another problem is to also include a exclude folder path list suppose in cell B1.. I have went back to the main code and i see that spathtop is a constant but How do I store different folder paths inside a range, its making me think of arrays..so I'm not sure. How do I proceed? Thanks...

  3. #63
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Changes and comments/suggestions

    Option Explicit
    
    
    Sub example()
        'better not to use VBA reserved words, less confusing
        Dim rCell As Range
    
    
        'as it was, whatever the activesheet was would be used
        .need the dot on the Range(A2)'s
        With Worksheets("Sheet1")
    
    
            'the Range around (A2) was missing
            For Each rCell In Range(.Range("A2"), .Range("A2").End(xlDown))
                rCell.Font.Color = 255
            Next
        End With
    End Sub


    Suggestion: by starting in A2 and going down, the selection will end at a cell above a blank cell, and if there are data filled cells below, they won't be included

    I've found it's safer to start at the bottom of the worksheet and go up to make the end cell the one with data

    Sub example2()
        Dim rCell As Range, rColor As Range
    
    
        With Worksheets("Sheet1")
            Set rColor = .Range("A2")
            Set rColor = Range(rColor, .Cells(.Rows.Count, 1).End(xlUp))
            
            'the Range around (A2) was missing
            For Each rCell In rColor.Cells
                rCell.Font.Color = 255
            Next
        End With
    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

  4. #64
    This is an example, but what I want to do is instead of manually inserting each file/folder path inside sPathTop and executing my main code, I want to manually write the folder path(s) in suppose cell A1 and then run my main code on this folder path into another worksheet. This way, I wouldn't have to go back and change sPathTop for each additional folder path that I add. it solves one problem but it gives another. Finding duplicates is already inserted on a different sub which we solved using hashes, but another problem is to also include a exclude folder path list suppose in cell B1.. I have went back to the main code and i see that spathtop is a constant but How do I store different folder paths inside a range, its making me think of arrays..so I'm not sure. How do I proceed? Thanks...
    What if I wanted to extend the red cell method for file/folder paths in my main code ??

  5. #65
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    This loops a list of 3 folders

    Within the loop, you could use Dir() to get the files, etc.



    Option Explicit
    
    
    Sub example3()
        Dim vPath As Variant
    
    
        For Each vPath In Array("C:\Users", "D:\Music", "L:\Quicken")
            
            MsgBox vPath & " -- " & FileDateTime(vPath)
        
        Next
    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

  6. #66
    Within the loop, you could use Dir() to get the files, etc.
    is it possible to store cell values of a range and reference them in an array using a variable which dynamically shortens or lengthens and then use a Call statement to execute a main code for each element inside that array ?

  7. #67
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Yes, not sure about the "which dynamically shortens or lengthens" part

    Option Explicit
    
    Sub LoadArray()
        Dim a As Variant
        Dim B As Variant
        Dim i As Long
        
        'needed to make a 1 dim array
        a = Application.WorksheetFunction.Transpose(ActiveSheet.Cells(1, 1).CurrentRegion)
    
    
        'we can use this as a 2 dim array
        B = ActiveSheet.Cells(1, 3).CurrentRegion
        
        For i = LBound(a) + 1 To UBound(a)
            Call SubA(a(i))
        Next i
        
        For i = LBound(B, 1) + 1 To UBound(B, 1)
            Call SubB(B(i, 1), B(i, 2))
        Next i
        
    
    
    End Sub
    
    
    Sub SubA(N As Variant)
        MsgBox "SubA   " & 10 * N
    End Sub
    
    
    Sub SubB(N1 As Variant, N2 As Variant)
        MsgBox "SubB    " & N1 * N2
    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

  8. #68
    Yes, not sure about the "which dynamically shortens or lengthens" part
    What I mean by this is that the number of entries dynamically changes with each folder path addition or deletion.

    Lets say in Sheet1 I have:

    cell A2 = folderpath1
    cell A3 = folderpath2
    cell A3 = folderpath3

    How can I execute the below code on each folder path above and get the result of the search in Sheet2 ??

    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
    Please take a look at the example workbook attached..
    Attached Files Attached Files

  9. #69
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Code fragment that may help


    Option Explicit
    
    
    Dim rowNext As Long
    Dim ws1 As Worksheet, ws2 As Worksheet
        
    
    
    Sub LoadArray()
        Dim aryFolders As Variant
        Dim aryParameters As Variant
        Dim i As Long
        
        Set ws1 = Worksheets("Sheet1")
        Set ws2 = Worksheets("Sheet2")
        
        'needed to make a 1 dim array
        aryFolders = Application.WorksheetFunction.Transpose(ws1.Cells(1, 1).CurrentRegion)
    
    
        'next blank row
        rowNext = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
        For i = LBound(aryFolders) + 1 To UBound(aryFolders)
            Call ListInfo(aryFolders(i), i)
        Next i
        
    End Sub
    
    
    
    
    Sub ListInfo(S As Variant, N As Long)
    '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
    
    
        With ws2.Rows(rowNext)
            .Cells(1).Value = S
            .Cells(2).Value = N
            .Cells(3).Value = 2 * N
            .Cells(4).Value = 4 * N
            .Cells(5).Value = N ^ 2
            .Cells(6).Value = N / 2
        End With
        
        rowNext = rowNext + 1
    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

  10. #70
    But, how can I use the filesystemobject properties together with arrays ?

  11. #71
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I don't have your latest version, but this is one way to store the FSO properties in an array

    The For/Next and Do/Loop will need to be integrated into the overall macro

    Option Explicit
    
    
    Dim aryCount As Long
    Dim aryData(1 To 1000, 1 To 8) As Variant
    
    
    Dim ws1 As Worksheet, ws2 As Worksheet
        
    
    
    Sub LoadArray()
        Dim aryFolders As Variant
        Dim i As Long
        
        Set ws1 = Worksheets("Sheet1")
        Set ws2 = Worksheets("Sheet2")
        
        'needed to make a 1 dim array
        aryFolders = Application.WorksheetFunction.Transpose(ws1.Cells(1, 1).CurrentRegion)
    
    
        aryCount = 0
            
        For i = LBound(aryFolders) + 1 To UBound(aryFolders)
                        
            'do loop files and folder in aryFolders
                Call ListInfo(FileorFolderObject, FileorFolderType)
            'loop
        Next i
    End Sub
    
    
    
    
    Sub ListInfo(oFolderFile As Object, sType As String)
        aryCount = aryCount + 1
        With oFolderFile
            aryData(aryCount, 1) = RemovePrefix(.Path)
            aryData(aryCount, 2) = RemovePrefix(oFSO.GetParentFolderName(.Path))
            aryData(aryCount, 3) = .Name
            aryData(aryCount, 4) = sType
            aryData(aryCount, 5) = .DateCreated
            aryData(aryCount, 6) = .DateLastModified
            aryData(aryCount, 7) = .Size
            aryData(aryCount, 8) = .Type
        End With
    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

  12. #72
    The latest version was excludes_14 very little has changed in formatting or newer codes...
    It says that variable not defined and "FileorFolderObject" is highlighted
    Last edited by anmac1789; 05-06-2021 at 07:23 PM.

  13. #73
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    The code in #71 rewritten.

    Reduce the amount of variables to a minimum.
    Use Arrays to read data, to store calculated/adapted data, and to write them in 1 movement into the workbook.
    Reduce the interaction with the workbook to: reading once, writing once.

    Sub M_snb()
      sn = Sheet1.Cells(1).CurrentRegion.Resize(, 9)                            '     reading
        
      With CreateObject("scripting.filesystemobject")
        For j = 1 To UBound(sn)
          With .getfolder(sn(j, 1))
            For jj = 2 To UBound(sn, 2)
              sn(j, jj) = Choose(jj, "", .Path, .Drive, .Name, .Type, .datecreated, .datelastmodified, .Size, .Type)
            Next
          End With
        Next
      End With
        
      Sheet1.Cells(1).CurrentRegion.Resize(, 9) = sn                           '      writing
    End Sub

  14. #74
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. I attached an expanded copy of your test folder (this is my folder files to test on.zip)

    2. Excludes_15 has a loop to read the folders to recurse on the worksheet FoldersToDo. Includes a second call to a folder to test dup removal

    3. Remove Dups looks in column A of the output sheet since that's what Excluded_14 does

    4. There is no need avoid writing directly to the output worksheet. It goes quickly enough

    5. Folders to exclude can also be a list on FoldersToDo




    Edit --

    I decided to use a different approach for collecting data in ver 17
    Attached Files Attached Files
    Last edited by Paul_Hossler; 05-08-2021 at 11:33 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

  15. #75
    5. Folders to exclude can also be a list on FoldersToDo
    Does this go in column 2 of 'FoldersToDo' worksheet ? because I see 'Generate Duplicates' is added there..

  16. #76
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    No, that was just a note that the second call to that folder will Generate Duplicates, and I wanted to make sure that they were removed

    Probably something like this for the Excludes

    I'm still not sure about how you want to define what gets excluded when you have an Excluded Folder

    Capture.JPG


    Decided to go a different approach for collecting file/folder data in ver 17
    Attached Files Attached Files
    Last edited by Paul_Hossler; 05-08-2021 at 11:35 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

  17. #77
    I'm still not sure about how you want to define what gets excluded when you have an Excluded Folder
    so basically, if a folder is listed as being excluded, then everything inside that parent folder and the parent folder itself should get excluded I would say...if it were written as a path that would make it easier because it pinpoints exactly what gets excluded weather its a entire folder path or a specific set of file(s)
    Last edited by anmac1789; 05-08-2021 at 09:33 PM.

  18. #78
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by anmac1789 View Post
    so basically, if a folder is listed as being excluded, then everything inside that parent folder and the parent folder itself should get excluded I would say...if it were written as a path that would make it easier because it pinpoints exactly what gets excluded weather its a entire folder path or a specific set of file(s)

    Check that Excludes_17.xlsm does it that way
    ---------------------------------------------------------------------------------------------------------------------

    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. #79
    it only changes with the folder/file name. So I changed this section of the code:

    For i = LBound(aryExcludes) To UBound(aryExcludes)
    If UCase(p.Path) = UCase(aryExcludes(i)) Then Exit Function ' <<<<<<<
    Next i
    Edit: I Just found out it only excludes folders not actual file(s). I will try to find some kind of readjustment to exclude in addition to file path(s)

  20. #80
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not seeing that

    I think it excludes the folders in the Col C list and the files within those folders/subfolders

    Capture.jpg

    'subfolder 2' is on the Exclude list and a 'Find' on the listings in Col A does not see it

    I prefixed the files in 'subfolder 2' with 'Exclude' just to make sure

    So if that's NOT what you want, you'll need to be more specific
    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
  •