List Files In Subfolders with Modified Date

[blimbert]

Hello - I am trying to obtain a file listing of all files in a network folder, and multiple layers of subfolders.

Requirements:

1. Return filename, file path, file type, and file last modified date to an excel sheet
2. Evaluate all files within all subfolders. In other words if there are nested subfolders, evaluate the files within every subfolder and its subfolders
3. Obtain the file information if the last modified date is after a certain date (eg >=1/1/2022)
4. Obtain the file information if the file type is a certain file type (eg .xlsx)

I have tried various code snippets from various sources and have not yet found one that works as desired.

Could you please help?

 

[Legacy 143009]

Sorry. This is an old code. I don't remember exactly what each line does. You may have to debug

 

[JEC]

Here a recursive approach

Dim xp, ar, x As Long

Sub jec()
  ReDim ar(3, 0)
  xp = "": x = 0
  M_getFile "C:\Users\xxx\Downloads\"   ' -->  change path to suit
  Range("A1").Resize(x, 4) = Application.Transpose(ar)
End Sub

Sub M_getFile(xp)
 Dim it, itl
 With CreateObject("scripting.filesystemobject").GetFolder(xp)
   For Each it In .Files
     If it.DateLastModified >= DateSerial(2022, 1, 1) And it.Name Like "*.xlsx" Then
       ReDim Preserve ar(3, x)
       ar(0, x) = it.Name
       ar(1, x) = it.Path
       ar(2, x) = it.Type
       ar(3, x) = it.DateLastModified
       x = x + 1
     End If
   Next
   For Each it1 In .SubFolders
     M_getFile it1.Path
   Next
 End With
End Sub

 

[pjmorris]

My solution is a complete change in tack, but knowing what i now know about Powerquery there is no way I would use VBA to collect directory information. Follow the steps below and you will return a list of all files with the information you're after. You can remain in the Powerquery editor to filter on whatever parameters you need and potentially combine the files into a single data source:

1. Open the Data ribbon.
2. Click on little down arrow by the 'Get Data' and select 'From File' then 'From Folder'.
3. Navigate to the top of the folder structure of interest and click 'OK'.
4. In the dialogue box that appears select Transform Data.
5. You will now be in the Powerquery Editor.
6. On the Home ribbon select 'Close & Load'.

You will then have a table in excel that contains all files in the folder structure together with their Path, File Extension, Date/Time Accessed, Date/Time Modified, and Date/Time Created.

There is so much you can do with Powerquery and PowerPivot that getting to grips with them should be a key part of an excel users repertoire (in my opinion ).
HTH.

 

[Sufiyan97]

Here a recursive approach

Dim xp, ar, x As Long

Sub jec()
  ReDim ar(3, 0)
  xp = "": x = 0
  M_getFile "C:\Users\xxx\Downloads\"   ' -->  change path to suit
  Range("A1").Resize(x, 4) = Application.Transpose(ar)
End Sub

Sub M_getFile(xp)
 Dim it, itl
 With CreateObject("scripting.filesystemobject").GetFolder(xp)
   For Each it In .Files
     If it.DateLastModified >= DateSerial(2022, 1, 1) And it.Name Like "*.xlsx" Then
       ReDim Preserve ar(3, x)
       ar(0, x) = it.Name
       ar(1, x) = it.Path
       ar(2, x) = it.Type
       ar(3, x) = it.DateLastModified
       x = x + 1
     End If
   Next
   For Each it1 In .SubFolders
     M_getFile it1.Path
   Next
 End With
End Sub

Hi JEC
This is perfect.
Thank you very much.

 

[Sufiyan97]

My solution is a complete change in tack, but knowing what i now know about Powerquery there is no way I would use VBA to collect directory information. Follow the steps below and you will return a list of all files with the information you're after. You can remain in the Powerquery editor to filter on whatever parameters you need and potentially combine the files into a single data source:

1. Open the Data ribbon.
2. Click on little down arrow by the 'Get Data' and select 'From File' then 'From Folder'.
3. Navigate to the top of the folder structure of interest and click 'OK'.
4. In the dialogue box that appears select Transform Data.
5. You will now be in the Powerquery Editor.
6. On the Home ribbon select 'Close & Load'.

You will then have a table in excel that contains all files in the folder structure together with their Path, File Extension, Date/Time Accessed, Date/Time Modified, and Date/Time Created.

There is so much you can do with Powerquery and PowerPivot that getting to grips with them should be a key part of an excel users repertoire (in my opinion ).
HTH.

Hi pjmorris,

This is doing the same job as VBA just need to follow few steps.

a BIG Thank you.

 

[JEC]

You’re welcome!

 

[pjmorris]

Hi pjmorris,

This is doing the same job as VBA just need to follow few steps.

a BIG Thank you.

My pleasure.

 

[Sufiyan97]

Here a recursive approach

Dim xp, ar, x As Long

Sub jec()
  ReDim ar(3, 0)
  xp = "": x = 0
  M_getFile "C:\Users\xxx\Downloads\"   ' -->  change path to suit
  Range("A1").Resize(x, 4) = Application.Transpose(ar)
End Sub

Sub M_getFile(xp)
 Dim it, itl
 With CreateObject("scripting.filesystemobject").GetFolder(xp)
   For Each it In .Files
     If it.DateLastModified >= DateSerial(2022, 1, 1) And it.Name Like "*.xlsx" Then
       ReDim Preserve ar(3, x)
       ar(0, x) = it.Name
       ar(1, x) = it.Path
       ar(2, x) = it.Type
       ar(3, x) = it.DateLastModified
       x = x + 1
     End If
   Next
   For Each it1 In .SubFolders
     M_getFile it1.Path
   Next
 End With
End Sub

Hi JEC
While working with thousands of files in a folder of subfolders, it is taking too much time.
is there any way to make it faster?

 

[JEC]

Looping through thousands of files with these conditions, is never going to be super fast. If you need to do this once in a while the durations would not be an issue right? How long does it take?

Note: having thousands of items, applications transpose will probably fail. I suggest you ReDim the array big enough. Then you do not need transpose

 

[Sufiyan97]

If you need to do this once in a while the durations would not be an issue right?

Right.

How long does it take?

My friend didn't wait much, he just went to task manager and ended the task.
I think it will take a few minutes, but no problem we can wait for a few minutes.

Note: having thousands of items, applications transpose will probably fail. I suggest you ReDim the array big enough. Then you do not need transpose

As I know nothing about VBA, I don't understand what you are trying to say.