21. VBA Tips - List Files In A Folder

Nick's picture

This VBA tip will demonstrate how to list all the files in a folder.

  • We'll learn about Scripting Objects
  • We'll also see how to recursively call the sub routine so that we can also list files in the subfolders
  • The example I will use is the case of trying to manage your photos
    • You have thousands of photos all over the place and you think you have replicas that you might be able to delete to save disk space

Here's an example folder, it's contents, and it's subfolder's contents.



Here's what we want to achieve in Excel:


Here's the code we will use:



  1. Open the practise sheet, and recreate the folders if you want to follow along with the tip
  2. We start by dimming iRow
    • The reason for this is so that we can share it between the subroutines, and when we loop through the subfolders, it continues to increment
    • iRow is the row that we will put the file name / path etc..
  3. The ListFiles Sub routine sets the start row... in our case we want row 11
  4. It also calls the ListMyFiles procedure with inputs equal to the values you input on the sheet. (B7 and B8)
  5. You can change that path (B7) to whatever you want
  6. You can also change whether to include or exclude subfolders (B8) by changing from TRUE to FALSE
  7. The ListMyFiles procedure then creates a FileSystemObject (myObject), and Source folder object (mySource)
  8. We then have the line: On Error Resume Next
    • This was included so that the code doesn't break if you're looking at a folder you don't have access to
  9. We then loop through each of the files in the source folder, get the attributes we're interested in, and output them to our sheet.
  10. The last bit of the code asks: if we want to include SubFolders, then call ListMyFiles with the subfolder as input. This ensures that cannot exit the code until all the subfolders are done.

The output we get on the sheet tells us that there are several files that have the same name, last modified date, and size... so we should have a look and see whether we can delete one of them and free up disk space.

Download sheet to practise how to List Files In A Folder in Excel

Training Video on how to List Files In A Folder in Excel:

list-files-in-a-folder.xls850.5 KB

Folder is located on a SharePoint site

Hi Nick,

Similar to the request above, my folder path points at a SharePoint site, which ostensibly is being updated while the code is running. Your macro runs very quickly, populating the worksheet, but then it pauses for several minutes, seemingly doing nothing at all. Is it possible that since new files may be posting during the execution of the code that the macro loops several times until an opportunity to end or close is reached?

If so, is there a way to modify the code to simply take a snapshot of the folder's contents at the instant the macro is run and to ignore new incoming files?

Thanks in advance for your time.

Nick's picture

it runs as fast as your

it runs as fast as your network allows

Is there a way to access/display more file properties

I am creating a list of music files and would like to add other file properties not in your example.


myfile.Length (i.e. length of song)

Can this be done with the same code? I have tried adding columns with these examples, and the columns are just left blank.

Any help would be greatly appreciated!

Thanks, John

this is me above

I created an account to receive emails once this comment is replied. Thanks!

Nick's picture

not that I know of easily,

not that I know of easily, but it must be doable...

add left function

hi Nick,

Is it possible to put left function in the code so that the file extension will no longer appear in the excel file? example instead of mypic.jpg, the name that will appear is mypic only? will really appreciate your help..

and thanks for the code, its what I have been really needing. :D

Nick's picture

hi.. just do a replace of

hi.. just do a replace of ".jpg" with nothing.

.. you can record this macro

 Record a Macro


Thanks! :)

Invalid procedure?

Hi Nick, When i download your example file it runs fine, but when i copy the code to my own sheet - no luck. Any ideas what i'm doing wrong? Thanks Gilbert

Nick's picture

happy to help, but this

happy to help, but this sounds a bit more than a forum question

; - >

Request a Quote