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
»

Display file size in mbs instead of bites

Submitted by JoaoCorreia on 10 September, 2014 - 19:00.

Hello!
First of all, I really thank you for this script which is helping me a lot building a database in excel.
However, there is something that would make my tasks easier if i could convert the file size from bites to megabites and display only 2 decimals. Would someone please explain to me how should i change the code to make that work?

THank you in advance!!

JC

»

Information from a file

Submitted by tintirina66 on 6 July, 2014 - 08:50.

Where I can get a listing of all the information that can be obtained from a file? The type of "DateLastModified"
Of the jpg files can get the name of the chamber, the diaphragm, the speed with which the picture is taken, etc..
Mp3 files you can get the tags, etc..
Thanks

»

Multiple Folders

Submitted by sm786 on 8 January, 2014 - 16:50.

Hi Nick, this is fantastic peice of code but i wanted to extend this to automatically run over a number of independent folders.

I could run the macro each time but that defeats the purpose. do you have a way in which i can build it in to this solution?

edit: the issue i am getting is the "mysource" variable doesnt seem to reset and always references the origional folder

the code below manages to almost do it...

folder_s = Range("folder").Row
folder_e = Range("folder").End(xlDown).Row

For folder = folder_s To folder_e

On Error Resume Next
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(Range("Step2!d" & folder).Value)

For Each myFile In mySource.files
Counter = 1
For i = 1 To 2
Call GetAttribute(i)
Next
Application.StatusBar = "File Number: " & iRow
iRow = iRow + 1
Next

Next folder
' set mysource = nothing < this doesnt work either

Thanks!

»

List files in a folder

Submitted by duncan on 13 November, 2013 - 21:43.

This code is very helpful to me but ideally I am looking for code which helps me do the following.

In column A I have a list of file paths and I want the files in this file path to be returned either all in one cell for each row (e.g. files in filepath in A1 are returned in B1) or listed across the row (so that I can then concatenate the cells which are listed across)

I am doing this to help a charity improve their efficiency

Thanks in advance

»

Modified By

Submitted by ihinojosajr on 1 May, 2013 - 15:02.

Anyway to included modified by and/or owner?

Thanks in advance.

»

How to add last modified date of file

Submitted by kchm_2000 on 6 October, 2012 - 23:48.

Hi, I ran your macro and it works fine. However i want to add a column of last modified date of the file instead of last modified date of the folder. How do i go about doing that?

»

Folder Iterations

Submitted by ah414 on 2 October, 2012 - 12:40.

Hi

This is a great script but I am hoping you can help me adapt it a little.

I wish to list the folders in a directory rather than the files.

I have attempted to change the original code to suit my needs however I recieve no output, I'm hoping you can have a look and help.

Dim iRow

Sub ListFolders()
iRow = 11
Call ListMyFolders(Range("C7"), Range("C8"))
End Sub

Sub ListMyFolders(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFolder In mySource.Folder
iCol = 2
Cells(iRow, iCol).Value = myFolder.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFolder.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFolder.DateLastModified
iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFolders(mySubFolder.Path, True)
Next
End If
End Sub

Many thanks in advance

»

RE: Folder Iterations

Submitted by manny on 8 October, 2012 - 14:44.

Hi,

Replace

For Each myFolder In mySource.Folder

with

For Each myFolder In mySource.SubFolders

 

Best regards.

»

New Scripting.FileSystemObject - Variable not defined

Submitted by questionguy on 19 September, 2012 - 13:45.

Hi,

when I download your file everything works fine; however, when I copy your code into a new spreadsheet I get an error message on line 10 for Set MyObject = New Scripting.FileSystemObject user-defined type not found. Did I miss anything? What am I doing wrong?

»

Look for the Microsoft Scripting Runtime

Submitted by manny on 19 September, 2012 - 19:19.

Hi,

Look at the new file in the VBE menu Tools -> References... whether Microsoft Scripting Runtime is checked.

 

Best regards.

»