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.

list-files-in-a-folder

list-files-in-a-folder

Here's what we want to achieve in Excel:

list-files-in-a-folder

Here's the code we will use:

list-files-in-a-folder

Explanation

  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:

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

it already does that..

it already does that..

Search the file n open

Hello dude..... The file works great..... I just need a slight modification..... That's i will give the file name (ex: drawing no. 12345678.dwg or 12345678.pdf) and the drive in which it is to be searched..... The drive has sub folders and again subfolders within it..... i just want it to open the drawing if it exists else give a msg "File Doesnt exist"..... the drive has 10000's of drawings in many subfolders......

and if possible, even with half the file name. for ex ("12345678r1.dwg" should be opened when we just give the input as "12345678") i hope this is possible..... Thanks in advance...... appreciate ua reply..... thanks again

Thanks a lot!

I found many different solutions for this item - yours was the cleanest and easiest to maintain, so... Thanks! :-)

[Ofernicus]

Is there a way to add file title?

Hello Nick,

I was wondering if there is a way I am able to extract the file title as well as file name?

Emma

Nick's picture

not that I know of... but

not that I know of... but there probably is a way

User-defined - type not defined error

Hello,

Would you be able to help?, I'm trying to run your example code and keep getting the "User-defined - type not defined error" at line "Set MyObject = New Scripting.FileSystemObject". After reading other comments regarding this I then tried declaring both as objects but still can't get it to run. Could you show me where I'm going wrong please.

regards
stuart

Nick's picture

in VBA editor, you need to

in VBA editor, you need to add a reference to:
Microsoft Scripting Runtime

from Excel, press ALT + F11
then go: Tools => References
turn on Microsoft Scripting Runtime

Excellent - That did the

Excellent - That did the trick
Thanks for your help.

Thanks

It worked excellent.. you are a genius!! My Hero.. LOL I've wanted to do this for a long time and now I can make a database of my backup music DVDs quickly.. Thank you.

Folder is saved in a network drive

Hi Nick,

I used your code and it works great except that because my file is saved in a network drive and retrieving is so slow about 3-4 mins even if there are only about 300 files to be retrieved.

Appreciate your help.. Thanks and have agreat day!