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
»

Folder Name Output

Submitted by Anonymous on 27 May, 2012 - 14:18.

Thank you for the post as I found that extremely useful. The question that I have is that would there be a way to output the name of the folder only in one of the columns? Perhaps a minor modification of the code as in the below?

Cells(iRow, iCol).Value = myFile.FolderPathName
iCol = iCol + 1

I know the above example is wrong as it's been quite some time since I've played with VBA. Any help would be greatly appreciated. :)

»

Re: Folder Name Output

Submitted by Anonymous on 22 June, 2012 - 09:35.

Yeah, just use this:

Cells(iRow, iCol).Value = mysource.Path
iCol = iCol + 1

»

List folders contain a specific file extension

Submitted by Anonymous on 18 April, 2012 - 19:45.

Is it possible to list all the folders/subfolders for a specific file extension that user entered for example ".xlsx" and then list out all the folders and subfolders that contain this type of file?

»

Sub folders

Submitted by Anonymous on 20 March, 2012 - 10:08.

Hi

The code works fine. Is it possible to extent to all sub-folders?

Thanks
Fabrice

»

Is there any way to set the

Submitted by Anonymous on 2 April, 2012 - 09:12.

Is there any way to set the path in the code like that :

Sub ListFiles()
Call ListMyFiles("c:\tbmaestro\perso", "VRAI")
Ouvertureuf.Show
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
Ouvertureuf.ListBox1.AddItem myFile.Name
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub

I'm getting an error when I launch it!

Thanks :)

»

try: Call

Submitted by Anonymous on 2 April, 2012 - 10:22.

try:
Call ListMyFiles("c:\tbmaestro\perso\", "VRAI")

or:
Call ListMyFiles("c:\tbmaestro\perso\", VRAI)

or:
Call ListMyFiles("c:\tbmaestro\perso\", true)

»

Thank you for you reply! I

Submitted by Anonymous on 2 April, 2012 - 10:45.

Thank you for you reply!

I still get the same error : "erreur de compilation, type défini par l'utilisateur non défini"

»

do you have option explicit

Submitted by Anonymous on 2 April, 2012 - 12:43.

do you have option explicit on ?
... you're getting a compile error

»

Thanks again for your

Submitted by Anonymous on 2 April, 2012 - 17:02.

Thanks again for your answer!

I had option explicit on. I disabled it but the error remains =(

»

solved...I added the right

Submitted by Anonymous on 3 April, 2012 - 16:19.

solved...I added the right library :)

»