Combine recursive listing with excluded code

[anmac1789]

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

[anmac1789]

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...

[Paul_Hossler]

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

[anmac1789]

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 ??

[Paul_Hossler]

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

[anmac1789]

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 ?

[Paul_Hossler]

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

[anmac1789]

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..

[Paul_Hossler]

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

[anmac1789]

But, how can I use the filesystemobject properties together with arrays ?

[Paul_Hossler]

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

[anmac1789]

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

[snb]

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

[Paul_Hossler]

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

[anmac1789]

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..

[Paul_Hossler]

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

[anmac1789]

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)

[Paul_Hossler]

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

[anmac1789]

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)

[Paul_Hossler]

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