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

Multiple Folders

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

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

Anyway to included modified by and/or owner?

Thanks in advance.

How to add last modified date of file

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

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

Hi,

Replace

For Each myFolder In mySource.Folder

with

For Each myFolder In mySource.SubFolders

 

Best regards.

New Scripting.FileSystemObject - Variable not defined

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

Hi,

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

 

Best regards.

Search using more than one file path?

I really appreciate you sharing this code! I learn new programming languages by finding things like this.

At work we have a shared drive with a ton of folders. I only need to search through a few of those folders, but all folders have subfolders, so this code is perfect. Is there a good way to give as an input more than one file path? Thank you for your time,

Mike

Nick's picture

currently no... you need to

currently no... you need to run each folder... but it's something we could add cheaply for you.. Request a Quote

 

Adding value from cells

Hey,

I used this code also and it is working great.
I use it to list a lot of excel documents. I am wondering if it is possible, to get also next to the lastmodified Col the value of a specified cell from the listed file (this is always the value of the same cel).

thanks a lot in advance i hope you can help me out.

Best regards,

Stefan

Nick's picture

yes, you can extract data

yes, you can extract data from a cell in a closed workbook via a formula..

something like this in code:

ActiveCell.FormulaR1C1 = _
"='C:\[a.xls]Contacts'!R1C1"

c:\ = the path
a.xls = the file name
R1C1 = A1

Thanks, for your Answer.

Thanks, for your Answer.

With this formula he only looks in the file as writen in the script. Is it also possible to look in all the documents, that are found in the script wit the myfile.path?

Thanks in advance.

Nick's picture

yes, you will need to code

yes, you will need to code that into the bit when you get the file path back

can you show how it done?

HI Nick,

I am a newbie on VBA, and I find your code really great, I want to get a data on each files list using this formula but i can't make it work, it turn an out PUT to " FALSE".

Cells(iRow, iCol).Value = ActiveCell.FormulaR1C1 = "='[myFile.Name]Sheet1'!R9C11"

Dim iRow

Sub ListFiles()
iRow = 11
Call ListMyFiles(Range("C7"), Range("C8"))
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
If InStr(myFile.Path, ".xls") <> 0 Then
iCol = 1
Cells(iRow, iCol).Value = iRow - 10
iCol = 2
Cells(iRow, iCol).Value = myFile.DateCreated
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, iCol), Address:=myFile.Path, TextToDisplay:=myFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastModified
iCol = iCol + 1
Cells(iRow, iCol).Value = ActiveCell.FormulaR1C1 = "='[myFile.Name]Sheet1'!R9C11" ' THIS output turns to " FALSE" '
iRow = iRow + 1

End If
Next

If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If

ActiveWorkbook.Saved = True

End Sub

Help if You Can - I'm Trying to List AND Import Files

Ultimately, what I'm trying to create is a 2-sheet workbook to accomplish the following:

Sheet 1: create a list of all .txt files within a directory and it's subdirectories.

Sheet 2: Import the entirety of the first file listed on Sheet 1 into Sheet 2, and then import and append each of the remaining files on Sheet 1 to Sheet 2. All files after the first should start on row 2 of each file so not to repeat the header row, however I would like to change the formatting of the first line that starts a new file (or some other means of flagging the start of a new file)

All text files have the same field settings and structure. I've been trying to amend this post's code to only list .txt files, but I'm a noob and I think I'm nesting conditionals incorrectly.

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    If InStr(MyFile.Path, ".txt") <> 0 Then
        For Each MyFile In mySource.Files
       
            iCol = 2
            Cells(iRow, iCol).Value = MyFile.Path
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.Name
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.Size
            iCol = iCol + 1
            Cells(iRow, iCol).Value = MyFile.DateLastModified
            iRow = iRow + 1
         Next
    End If
    Columns("C:E").AutoFit
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

And then to import all of the files, I've been trying to modify this Sub.

Sub Import_All_Text_Files_2010()
    
    Sheets(2).Activate
    Cells.Delete

Dim nxt_row As Long
     
    'Change Path
    Const strPath As String = "C:\"
    Dim strExtension As String
     
    'Stop Screen Flickering
    Application.ScreenUpdating = False
     
    ChDir strPath
     
    'Change extension
    strExtension = Dir(strPath & "*.txt")

 'Sets Row Number for Data to Begin
        nxt_row = Range("A65536").End(xlUp).Offset(1, 0).Row
         
        'Below is from a recorded macro importing a text file
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row))
            .Name = strExtension
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 2
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
             'Delimiter Settings:
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(2, 2, 2, 2, 1, 1, 2, 1, 1, 1, 1, 1, 1)
             
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
         
        strExtension = Dir
    Loop
     
    Application.ScreenUpdating = True
     
End Sub

Author / Owner

Hi! This code is working pretty good! Thanks for this! Is there any chance to list the Author and Owner? I tried to amend your code by myFile.Author (etc.) but without any luck. Thanks!

Works great

Is there a way to get this to work on multiple directories?

Folder Name Output

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

Yeah, just use this:

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

List folders contain a specific file extension

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

Hi

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

Thanks
Fabrice

Is there any way to set the

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

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

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

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

Thanks again for your

Thanks again for your answer!

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

solved...I added the right

solved...I added the right library :)

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!

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.

i.e.

myfile.Comments
myfile.BeatsPerMinute
myFile.InitialKey
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!

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

 

 

 

Not Responding

I am attempting to run your beautiful creation. I need to map a network drive that has tons of image files. When I run your macro, it completely freezes excel. Any advice?

Nick's picture

select the workbook that's

select the workbook that's running the code, and press CTRL + Break

that will allow you to stop the code if you want.
- if that doesn't work, kill the Excel task in task manager
- if it does work (i.e you see a screen with a button called "Debug") then it is doing something so press "Continue" and let it run