Excel VBA List Files in Folders and Subfolders

This post details a VBA macro to list all files that exist in a given folder, and will loop through and get the files in each subfolder until all have been examined.  A comment was made on the original post Excel VBA – List Files in Folder,  but I decided to create a new post to cover this topic.  The original simply listed all files in a given folder, using the DIR function.

In this sample, I decided to use the Scripting.FileSystemObject model, which is more robust and capable of doing a more complex task such as this.   The File System Object (FSO) model provides an object-based tool for working with folders and files. It allows you to use the familiar object.method syntax with a rich set of properties, methods, and events to process folders and files. You can also employ the traditional Visual Basic statements and commands.

The end result of this macro is the creation of a table in the Excel file, which captures the following information about each file in the chosen root folder:

Name / Path / Size (KB) / DateLastModified / Attributes / DateCreated / DateLastAccessed / Drive / ParentFolder / ShortName / ShortPath / Type

Excel VBA List Files in Folders and Subfolders – Setup

Here is the code:

Results look like this:

Excel VBA List Files in Folders and Subfolders

This comes in handy when you are trying to identify specific files in a given path, listing each file embedded in the root path as well as any subfolders.  I use this when I need to review disk space in a given path, looking for large files and those which have not been accessed in a specified amount of time (i.e. haven’t been accessed for 12 months or longer).

You can modify this macro to meet your needs.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

15 thoughts on “Excel VBA List Files in Folders and Subfolders”

  1. Thank you for your script. When I use this on my local hard drive it works fine, however when I attempt to use this on a network drive it run extremely slow. In fact it locks up my Excel. I’ve found some other posts that talk about the performance issue between FSO and Dir, with Dir being much faster. Have you encountered this, and is there a way to still get all the file details but improve the performance by using Dir?

    • Hi Brendan, I have not taken a deep dive into Dir function, but I don’t think it has the same level of detail. This is something I plan on checking into, when I have a little more time.

      In the meantime, if anyone else has any insight, please add a comment!

    • Also, I recall when running on a larger network drive, I had to leave it for a while but it ultimately returned the full results (couple hours if I’m not mistaken). Not ideal, but it would end up working!

  2. Hello
    Your code works perfectly for files found in a specified folder on my computer. However it does not work for files not there. Ie I have a worksheet, where in Column A, I have a list of file paths. The list is compiled from more than one user and the files are not on my computer. I want to be able to do some of what you did in extracting file information from the given info on the worksheet – file parent folder, file drive, but more importantly file type. I don’t mean file extension, but file type as in your code above. I have other ways of extracting the parent folder and drive, but not the type as recognized by the system. After much experimentation on my part, I think that fso will not work. Is that so and if not, then can you provide the code for that? Thank you.

    • Just so I understand correctly, are you looking for code to loop through a list of files (full path provided in Column A as you noted), and then pull the same information about each file, like I’ve done in the post?

      • Yes, exactly. I have full paths in Column A, but the files are not on my computer. I would like to extract file info from the path, just as you did, especially the file type. The files in column A vary in type and I cannot predict what will be there.

        PS responded via your email a day or so ago, but since I don’t see my reply here, posting it here too.

        • Give the code below a try. It will take the list of files with full path in Column A (i.e. “C:\BuffaloBI – Post Content\Website.txt”) and loop through each, gathering the same file information as I’ve demonstrated in the main post. However, when running this code, the file must exist on the computer. It will leave the file info data blank if the file does not exist on the computer you are running the code (you must have security access to the file).

          
          Public strPath As String
          
          Sub GetFolder()
          
          Range("B:L").ClearContents
          Range("B1").Value = "Name"
          Range("C1").Value = "Size (KB)"
          Range("D1").Value = "DateLastModified"
          Range("E1").Value = "Attributes"
          Range("F1").Value = "DateCreated"
          Range("G1").Value = "DateLastAccessed"
          Range("H1").Value = "Drive"
          Range("I1").Value = "ParentFolder"
          Range("J1").Value = "ShortName"
          Range("K1").Value = "ShortPath"
          Range("L1").Value = "Type"
          Range("A2").Select
          
          Dim rng As Range
          Set rng = ActiveSheet.Range(ActiveCell, ActiveCell.End(xlDown))
          
              For Each cell In rng
          
                  strPath = cell
                  
                  Dim OBJ As Object
                  Dim Folder As Object
                  Dim File As Object
                  Set OBJ = CreateObject("Scripting.FileSystemObject")
          
                  Dim StripFolder As String
                  StripFolder = OBJ.GetParentFolderName(strPath) & "\"
                  
                  Set Folder = OBJ.GetFolder(StripFolder)
                  
                  Call ListFiles(Folder)
                  
                  ActiveCell.Offset(1, 0).Select
                  
              Next cell
          
          Range("A1").Select
          
          End Sub
          
          
          Sub ListFiles(ByRef Folder As Object)
          
          For Each File In Folder.Files
          
              If File = strPath Then
                  ActiveCell.Offset(0, 1) = File.Name
                  ActiveCell.Offset(0, 2) = (File.Size / 1024) 'IN KB
                  ActiveCell.Offset(0, 3) = File.DateLastModified
                  ActiveCell.Offset(0, 4) = File.Attributes
                  ActiveCell.Offset(0, 5) = File.DateCreated
                  ActiveCell.Offset(0, 6) = File.DateLastAccessed
                  ActiveCell.Offset(0, 7) = File.Drive
                  ActiveCell.Offset(0, 8) = File.ParentFolder
                  ActiveCell.Offset(0, 9) = File.ShortName
                  ActiveCell.Offset(0, 10) = File.ShortPath
                  ActiveCell.Offset(0, 11) = File.Type
              Else
              End If
              
          Next File
          
          End Sub
          
          < \pre>
          • Thank you. I had the same code for cycling through the file names in col A, and that is how I found that your code using fso would not work for them. What I hoped was that you had some other method, or a variation of this one, that would extract the file type.

    • You can add these Sub procedures after the List Files is complete. These are written assuming the “Path” detail is captured in Column B:

      
      Sub AddLinks()
      
          Dim rng As Range
          
          If ActiveSheet.Range("B3").Value = "" Then
              Set rng = ActiveSheet.Range("B2")
          Else
              Set rng = ActiveSheet.Range("B2", ActiveSheet.Range("B2").End(xlDown))
          End If
          
          Dim DisplayText As String
      
          For Each cell In rng
              cell.Activate
              ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=cell.Value, TextToDisplay:=cell.Value
              cell.Offset(1, 0).Activate
          Next cell
          
          Range("A2").Activate
              
      End Sub
      
      
      Sub RemoveLinks()
      
          Columns("B:B").Select
          Selection.Hyperlinks.Delete
          Range("A1").Select
              
      End Sub
      
      
    • Try the following. You will need to set the value for “strDestinationPath” first. The code will then detect a “.txt” file and after it records the information in the Excel file, it will take a copy of the file and place it in the destination path you specify.

      Public strDestinationPath As String
      
      
      Sub GetFolder()
      
      Range("A:L").ClearContents
      Range("A1").Value = "Name"
      Range("B1").Value = "Path"
      Range("C1").Value = "Size (KB)"
      Range("D1").Value = "DateLastModified"
      Range("E1").Value = "Attributes"
      Range("F1").Value = "DateCreated"
      Range("G1").Value = "DateLastAccessed"
      Range("H1").Value = "Drive"
      Range("I1").Value = "ParentFolder"
      Range("J1").Value = "ShortName"
      Range("K1").Value = "ShortPath"
      Range("L1").Value = "Type"
      Range("A1").Select
      
      Dim strPath As String
      strPath = "C:\BuffaloBI - Post Content\"
      
      strDestinationPath = "J:\CopyFiles\"
      
      On Error Resume Next
          Kill (strDestinationPath & "*.*")
          On Error GoTo 0
      
      Dim OBJ As Object
      Dim Folder As Object
      Dim File As Object
      Set OBJ = CreateObject("Scripting.FileSystemObject")
      Set Folder = OBJ.GetFolder(strPath)
      
      Call ListFiles(Folder)
      
      Dim SubFolder As Object
      
      For Each SubFolder In Folder.SubFolders
          Call ListFiles(SubFolder)
          Call GetSubFolders(SubFolder)
      Next SubFolder
      
      Range("A1").Select
      
      End Sub
      
      
      Sub ListFiles(ByRef Folder As Object)
      
      For Each File In Folder.Files
      
          If Right(File, 4) = ".txt" Then
          
              ActiveCell.Offset(1, 0).Select
              ActiveCell = File.Name
              ActiveCell.Offset(0, 1) = File.Path
              ActiveCell.Offset(0, 2) = (File.Size / 1024) 'IN KB
              ActiveCell.Offset(0, 3) = File.DateLastModified
              ActiveCell.Offset(0, 4) = File.Attributes
              ActiveCell.Offset(0, 5) = File.DateCreated
              ActiveCell.Offset(0, 6) = File.DateLastAccessed
              ActiveCell.Offset(0, 7) = File.Drive
              ActiveCell.Offset(0, 8) = File.ParentFolder
              ActiveCell.Offset(0, 9) = File.ShortName
              ActiveCell.Offset(0, 10) = File.ShortPath
              ActiveCell.Offset(0, 11) = File.Type
              
              FileCopy File, strDestinationPath & File.Name
              
          Else
          End If
          
      Next File
      
      End Sub
      
      
      Sub GetSubFolders(ByRef SubFolder As Object)
      
      Dim FolderItem As Object
      
      For Each FolderItem In SubFolder.SubFolders
          Call ListFiles(FolderItem)
          Call GetSubFolders(FolderItem)
      Next FolderItem
      
      End Sub
      
  3. Hi. im use this
    strPath As String
    strPath = Range(“a1”).Value
    Dim OBJ As Object, Folder As Object, File As Object.
    work perfect for me.
    can u say what i need to change so its filter me .txt file>? (its be always .txt no need to let user choose this parameter)
    i know u need type something like wild card in box or something…

    • Sergey, try replacing the Sub “ListFiles” from my post above with code block below:

      Sub ListFiles(ByRef Folder As Object)
       
      For Each File In Folder.Files
          If Right(File, 4) = ".txt" Then
          
              ActiveCell.Offset(1, 0).Select
              ActiveCell = File.Name
              ActiveCell.Offset(0, 1) = File.Path
              ActiveCell.Offset(0, 2) = (File.Size / 1024) 'IN KB
              ActiveCell.Offset(0, 3) = File.DateLastModified
              ActiveCell.Offset(0, 4) = File.Attributes
              ActiveCell.Offset(0, 5) = File.DateCreated
              ActiveCell.Offset(0, 6) = File.DateLastAccessed
              ActiveCell.Offset(0, 7) = File.Drive
              ActiveCell.Offset(0, 8) = File.ParentFolder
              ActiveCell.Offset(0, 9) = File.ShortName
              ActiveCell.Offset(0, 10) = File.ShortPath
              ActiveCell.Offset(0, 11) = File.Type
          Else
          End If
          
      Next File
       
      End Sub
      
  4. I got a question today about allowing the user to select a folder.

    First, add the following Function in the VBA module I provide in the post above:

    Function UserGetFolder() As String
    	Dim fldr As FileDialog
    	Dim sItem As String
    	Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    	With fldr
    		.Title = "Select a Folder"
    		.AllowMultiSelect = False
    		.InitialFileName = Application.DefaultFilePath
    		If .Show -1 Then GoTo NextCode
    		sItem = .SelectedItems(1)
    	End With
    NextCode:
    	UserGetFolder = sItem
    	Set fldr = Nothing
    End Function
    

    Next, change the following line of code from the post above, within the “Sub GetFolder()” macro, right after the first section where I am hard coding the column headers. All you need to do is comment out the hard-coded folder path, and replace by setting “strPath” equal to the newly added Function. Here is the update you need to make:

    Dim strPath As String
    'strPath = "C:\BuffaloBI - Post Content\"
    strPath = UserGetFolder
    

Leave a Comment