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



## classmaz (Dec 7, 2022)

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 (Dec 7, 2022)

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 (Dec 12, 2022)

DanteAmor said:


> If the macro works for you and the issue is permissions, then after this line:
> 
> ```
> colFolders.Add oFolder          'start with this folder
> ...


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 (Dec 12, 2022)

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 (Dec 12, 2022)

Automatrix said:


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


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 (Dec 12, 2022)

classmaz said:


> 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 (Dec 15, 2022)

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


DanteAmor said:


> ```
> colFolders.Add oFolder          'start with this folder
> ```
> 
> ...


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?


----------

