0

Hello I have inherited some excel vba code which produces a list of files which are less than 100 days old and captures the file sizes that sit in a specific folder and sub folders. When running the code, it tries to access some sub folders which I don't have access to and it crashes can anyone assist? I would also like it to ignore subfolders that start 02-Recs.

thanks

Sub getfiles()

    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object, sf
    Dim i As Integer, colFolders As New Collection, ws As Worksheet
    
    Set ws = ActiveSheet
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.getfolder("F:\test\")
    

    colFolders.Add oFolder          'start with this folder
    
    Do While colFolders.Count > 0      'process all folders
        Set oFolder = colFolders(1)    'get a folder to process
        colFolders.Remove 1            'remove item at index 1
    
        For Each oFile In oFolder.Files
            If oFile.DateLastModified > Now - 100 Then
                ws.Cells(i + 1, 1) = oFolder.Path
                ws.Cells(i + 1, 2) = oFile.Name
                ws.Cells(i + 1, 3) = "RO"
                ws.Cells(i + 1, 4) = oFile.DateLastModified
                ws.Cells(i + 1, 5) = oFile.Size / 1024 ^ 2
                i = i + 1
            End If
        Next oFile

        'add any subfolders to the collection for processing
        For Each sf In oFolder.subfolders
            colFolders.Add sf 'add to collection for processing
        Next sf
    Loop

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Classre
  • 1
  • 1

0 Answers0