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

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?