VBA code to generate a list of files and sizes in subfolders

[classmaz]

Hello, I have found code generated in this forum to generate a list of files in a specific folder and all its subfolders that have been modified in the past 100 days. However, when running it on the network drive it generates an error and stops. I think the error is caused by permission issues and I would like to exclude the subfolders I cant open. Would I have to specify them, or can the code just skip folders I don't have access to see? any help appreciated. Below is the code i am trying to use

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:\")
   

    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

 

[DanteAmor]

If the macro works for you and the issue is permissions, then after this line:

colFolders.Add oFolder          'start with this folder

Put this line:

On Error Resume Next

 

[classmaz]

If the macro works for you and the issue is permissions, then after this line:

colFolders.Add oFolder          'start with this folder

Put this line:

On Error Resume Next

I would like to log the files/sub folders that generate an error on another tab. Can you assist?

I have just ran the code with the date modified filter of

oFile.DateLastModified < Now + 1

so It would list all the files in the folder and sub folder and this could be compared against right clicking the folder and doing properties. Unfortunately, the validation highlighted a difference of 13 000 (20gb) files short. Any assistance would be appreciated.

 

[Automatrix]

Power Query alternative: From File --> From Folder. Add a custom "cutoff" column a hundred days before today with

Date.AddDays(Date.From(DateTime.LocalNow()),-100)

Then a conditional column that compares the cutoff to the Date modified column, and then a filter.

 

[classmaz]

Power Query alternative: From File --> From Folder. Add a custom "cutoff" column a hundred days before today with

Date.AddDays(Date.From(DateTime.LocalNow()),-100)

Then a conditional column that compares the cutoff to the Date modified column, and then a filter.

I tried power query and if failed to retrieve all the data as I don't have permissions for some of the subfolders. Any tips for this?

 

[Automatrix]

I tried power query and if failed to retrieve all the data as I don't have permissions for some of the subfolders. Any tips for this?

Afraid not, I've never run into that sort of permission problems myself.

 

[classmaz]

If the macro works for you and the issue is permissions, then after this line:

colFolders.Add oFolder          'start with this folder

Put this line:

On Error Resume Next

So I have been left confused the original code works for the first 3 subfolders and all their subfolders. It then stops doing the sub-subfolders, I am not sure if it is the on error resume next code? any help?