Home » Blogs » Nick's blog

21. VBA Tips - List Files In A Folder

Nick's picture
Submitted by Nick on 13 May, 2009 - 11:29


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
»

noughts and crosses

Submitted by Anonymous on 27 April, 2011 - 16:24.

Thanks for your advice. I am sure you must have created a noughts and crosses spreadsheet! Can you share it? I am sure there must be lots to learn from it. Thanks.

»
Nick's picture

Excel Games

Submitted by Nick on 28 April, 2011 - 08:15.

Take a look here:

»

fantastic

Submitted by mkapashi on 22 April, 2011 - 20:27.

You are sheer genius!!

»

Super!

Submitted by Anonymous on 15 April, 2011 - 18:35.

We had someone here at work that had 50 or so lines written that would only do one folder. This is so simple and clean and does all the subfolders. Thanks.

»

Thank you!

Submitted by Anonymous on 1 April, 2011 - 11:46.

Works like a charm. Thank you very much for sharing this!

»

Amazing! Thank you.

Submitted by Anonymous on 7 October, 2010 - 19:54.

Thank you for this code. I worked perfectly!

»

Add another column

Submitted by Anonymous on 22 September, 2010 - 19:50.

I want to add a few more columns to pull "genre", "artist" and such. Is that possible?

»

Thank you

Submitted by Anonymous on 28 June, 2010 - 13:50.

This was very useful and was one of the cleanest and most concise examples on how to do a recursive directory listing that I have found

Well done and Thanks a lot for the accompanying video/sheet

Pri

»

Thanks

Submitted by Anonymous on 19 May, 2010 - 04:40.

It works fine for me.

I got this excel at right time. I Appreciate your work.

My suggestion is Please add one more button to clear the data After use.

Good Luck.

Regards,
A.Kalusalingam

»

How to modify to only show one or two type(s) of file(s)

Submitted by Anonymous on 28 April, 2010 - 17:37.

How would I modify your code to only show 1 or 2 types of files.

I found one bit of code that will allow me to set the type of file that will be shown with the following code: 

Dim filetype As String
filetype = "dwg"
fName = Dir(fPath & "*." & filetype)
While fName <> ""
 i = i + 1
 ReDim Preserve fileList(1 To i)
 fileList(i) = fName
 fName = Dir()
Wend 
 
If i = 0 Then
 MsgBox "No files found"
 Exit Sub
End If
 
For i = 1 To UBound(fileList)
 ws.Range("A" & i + startrow).Value = fileList(i)
Next

However I'm having a hard time adding that to your code. Any help that you could offer would be great. Thanks, Michael

»