Combine recursive listing with excluded code

[Paul_Hossler]

I revised my macro a few minutes after I posted it last night at 10:26 since I wanted to simplify some things. Are you looking at the revised version?


1. Changed Init().
Using rowOut (a Long) is faster than tracing several objects and methods

2. No, instead of having basically the same 8 lines to put data onto the worksheet, a separate module (ListInfo) with calling parameters will put it in one place. Also makes the overall macro smaller and maintenance is usually easier

3. Arrays would make part of it faster, but IMHO the bulk of the time is spent getting information about files/folders the complexity wasn't worth it

4. A 'subfolder' is still a 'folder'. Something like oFolder.Subfolders is a collection of Folder objects in oFolder, but For Each returns a Folder object
That's why ListFolder was never used and I replaced ListFile and ListSubfolder with just ListInfo, since they both did 90% the same thing, the old difference being what you call it in cell 4

5. You could format (date, size) each line as you go, but I thinks it's faster to just do the whole column at once


The main recursive code is below, so if you want it to say "Subfolder", change the marked line

    Call ListInfo(oPath, "Folder")  ' <<<<<<<<<<<<<<<
    
    For Each oFile In oPath.Files
        Call ListInfo(oFile, "File")
    Next

Option Explicit


Const sPathTop As String = "D:\Test"


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


Sub Start()
    aryExclude = Array( _
        "D:\Test\111\111CCC\111CCC111", _
        "D:\Test\333\333AAA", _
        "D:\Test\333\333BBB" _
        )
    
    Init


    Call GetFiles(oFSO.GetFolder(sPathTop))
    
    wsOut.Columns(5).NumberFormat = "m/dd/yyyy"
    wsOut.Columns(6).NumberFormat = "m/dd/yyyy"
    wsOut.Columns(7).NumberFormat = "#,##0,.0 ""KB"""


    MsgBox "Done"
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, "Folder")
    
    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()
    Set wsOut = Worksheets("Files")
    
    With wsOut
    
        rowOut = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        If rowOut = 1 Then          '   blank sheet
            .Cells(rowOut, 1).Value = "FILE/FOLDER PATH"
            .Cells(rowOut, 2).Value = "PARENT FOLDER"
            .Cells(rowOut, 3).Value = "FILE/FOLDER NAME"
            .Cells(rowOut, 4).Value = "FILE or FOLDER"
            .Cells(rowOut, 5).Value = "DATE CREATED"
            .Cells(rowOut, 6).Value = "DATE MODIFIED"
            .Cells(rowOut, 7).Value = "SIZE"
            .Cells(rowOut, 8).Value = "TYPE"
        
            rowOut = rowOut + 1
        End If
    End With


    Set oFSO = CreateObject("Scripting.FileSystemObject")
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(oFile As Object, sType As String)
    With oFile
        wsOut.Cells(rowOut, 1).Value = .path
        wsOut.Cells(rowOut, 2).Value = .ParentFolder.path
        wsOut.Cells(rowOut, 3).Value = .Name
        wsOut.Cells(rowOut, 4).Value = sType
        wsOut.Cells(rowOut, 5).Value = .DateCreated
        wsOut.Cells(rowOut, 6).Value = .DateLastModified
        wsOut.Cells(rowOut, 7).Value = .Size
        wsOut.Cells(rowOut, 8).Value = .Type
    End With
    
    rowOut = rowOut + 1
End Sub




Private Function IsExcluded(p As Object) As Boolean
    Dim i As Long
    
    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