Find and List all Files and Folders in a Directory

In this article I will explain how you can find and create a list of all the files and folder in a directory:

Jump To:

Get List of All Files Using, FileSystemObject:

In this example I will assume that I have the following files in the directory “D:StuffFreelancesWebsiteBlogArraysPics”:

Files In Directory

The code below retrieves the file in this directory and creates a list of their names and paths:

Sub Example1()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("D:StuffFreelancesWebsiteBlogArraysPics")
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
'print file name
Cells(i + 1, 1) = objFile.Name
'print file path
Cells(i + 1, 2) = objFile.Path
i = i + 1
Next objFile
End Sub

Result:
File Names and Path Excel VBA

Get List of All Folders Using, FileSystemObject:

In this example I will use the directory “D:StuffFreelancesWebsiteBlog”. The following folders can be found in that directory:

Folders in Path

Using the code below, the names of the folders and their associated paths are listed on column A and B:

Sub Example2()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim i As Integer

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("D:StuffFreelancesWebsiteBlog")
i = 1
'loops through each file in the directory and prints their names and path
For Each objSubFolder In objFolder.subfolders
'print folder name
Cells(i + 1, 1) = objSubFolder.Name
'print folder path
Cells(i + 1, 2) = objSubFolder.Path
i = i + 1
Next objSubFolder
End Sub

Result:
List of Folder Names and Paths Excel VBA

Dir():

Using the Dir() function you can get the list of files and folders in a specific path. The Dir() function takes 2 input parameters, the directory path and the type of file we are looking for:

strFileOrFolder = Dir(strPath, FileAttribute)

strPath is the path of the directory which the files and folder are in. The FileAttribute specifies what type of file or folder we are after. For more information about the FileAttribute parameter click here.

Get List of All Files in a Directory Using Dir():

In this example I will assume that I have the following files in the directory “D:StuffFreelancesWebsiteBlogArraysPics”:

Files In Directory

The code below retrieves the name and path of the files in that directory using the DIR() function and lists them in column A and B:

Sub Example3()

Dim varDirectory As Variant
Dim flag As Boolean
Dim i As Integer
Dim strDirectory As String

strDirectory = "D:StuffFreelancesWebsiteBlogArraysPics"
i = 1
flag = True
varDirectory = Dir(strDirectory, vbNormal)

While flag = True
If varDirectory = "" Then
flag = False
Else
Cells(i + 1, 1) = varDirectory
Cells(i + 1, 2) = strDirectory + varDirectory
'returns the next file or directory in the path
varDirectory = Dir
i = i + 1
End If
Wend

Result:
File Names and Path Excel VBA

Get List of All Folders and Files in a Directory Using Dir():

In this example I will assume that I have the following files and folders in the directory “D:StuffFreelancesWebsiteBlog”:

Folders in Path

The code below retrieves the name and path of the folders and files in that directory using the DIR() function and lists them in column A and B:

Sub Example4()

Dim varDirectory As Variant
Dim flag As Boolean
Dim i As Integer
Dim strDirectory As String

strDirectory = "D:StuffFreelancesWebsiteBlog"
i = 1
flag = True
varDirectory = Dir(strDirectory, vbDirectory)

While flag = True
If varDirectory = "" Then
flag = False
Else
Cells(i + 1, 1) = varDirectory
Cells(i + 1, 2) = strDirectory + varDirectory
'returns the next file or directory in the path
varDirectory = Dir
i = i + 1
End If
Wend

Result:
List of Folders and File Names and Paths DIR Excel VBA

See Also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com

38 thoughts on “Find and List all Files and Folders in a Directory”

  1. zapp10anth says:

    Hi,

    I am trying to use your code to begin a selection process from a file directory.

    I need to access this file directory and go into each folder to match if a specific file name exists. The naming conventions are the same for each file that I want to isolate.

    For example: PlanIT_Timesheet_Data_2013_Dec. The name is the same for each file, except for the “2013_Dec” piece. That will change monthly, with the corresponding month and year for the file extract.

    I want to build and excel template that places the latest year_month combination in a single cell: i.e. the value would be “2014_Apr” because I just uploaded the April 2014 extract yesterday.

    I can use these last 8 characters to reference the most recent date, everytime. But it must reference the file name, not modified by date, as changes may occur after the file is placed in this directory.

    I need to do this for multiple file names, maybe 20.

    If I could have a column that displays a cell with the latest file date, with each row being the file name I am monitoring, this will have a great impact in my collection duties.

    Very hopeful you can help!

    1. pedrumj says:

      Hello zapp10anth, thank you for visiting my website.

      I’m sorry but I’m having a hard time understanding what you are trying to do:

      I need to access this file directory and go into each folder to match if a specific file name exists. The naming conventions are the same for each file that I want to isolate.

      For this you could use a double iteration. The sample code provided below iterates through all the folders in the initial directory. Then it iterates through all the files in each of those sub directories:

      Sub main()
      Dim objFSO As Object
      Dim objFSO2 As Object

      Dim objFolder As Object
      Dim objFolder2 As Object
      Dim objSubFolder As Object
      Dim objSubFile As Object
      Dim i As Integer
      ‘This is the path to the directory
      Dim PATH As String
      ‘The directory path
      PATH = “C:SOMETHINGSOMETHINGELSE”
      ‘Create an instance of the FileSystemObject
      Set objFSO = CreateObject(“Scripting.FileSystemObject”)
      ‘Get the folder object
      Set objFolder = objFSO.GetFolder(PATH)
      i = 1
      ‘loops through each file in the directory and prints their names and path
      For Each objSubFolder In objFolder.subfolders
          Set objFSO2 = CreateObject(“Scripting.FileSystemObject”)
          Set objFolder2 = objFSO2.GetFolder(PATH + “” + objSubFolder.Name)
          For Each objSubFile In objFolder2.Files
              ‘objSubFile will iterate through all the files in the sub folder
          Next objSubFolder
      Next objSubFolder
      End Sub
      End Sub

      I want to build and excel template that places the latest year_month combination in a single cell: i.e. the value would be “2014_Apr" because I just uploaded the April 2014 extract yesterday.

      I’m not really sure what you are trying to do here. I’m guessing you want to loop through all the files in each of these sub folders and find the file with the latest date. If this is true using the code above you can get all the filenames. Then you could use the topics covered in this article to compare the dates to find the most recent one:

      Excel VBA Working With Dates

      It would really help if you could email me a sample (A directory with the template files and subfolders) so I could provide a more accurate answer to your question. My email is:
      Pedrum.jalali@gmail.com

      1. steve says:

        ‘use the vba command: SPLIT
        ‘see url: https://msdn.microsoft.com/en-us/library/6x627e5f(v=vs.90).aspx
        ‘——————- start
        Dim TestString As String
        TestString = “PlanIT_Timesheet_Data_2013_Dec”
        Dim TestArray() As String = Split (TestString, “_”)
        ‘ TestArray now holds {“PlanIT”, “Data”, “2013”,Dec””}
        ‘——————- end

  2. NAVEEN C N says:

    Hi, I have the folder keywords in column A and Directory( paths) in column B and i need a macro to search for the folder with the keyword in a directory and it should output folder present or not in column C and if present it should output the last modified date of the folder in column D.

    Column A ; 20140605
    Column B: \nasmsb1apMSBPROClearwaterEXPORTHISTORY3)_Impose_2nd_Late_Fee
    column C: Folder present/ folder not present ( expected output)
    column D: 6/4/2014 11:05 PM ( expected output)

    1. pedrumj says:

      Hi there

      Please send me a message through the contact page so we can further discuss this project. Thanks

  3. Charlie says:

    the sub folders do not work on your code mate, the main folders do, any ideas?

    1. pedrumj says:

      Hi there

      This article only explains how to get the files and folders in the “current directory” and not the subdirectories. In order to get a list of all files and folders in the “current directory and its sub directories” please see the article below:

      http://software-solutions-online.com/2014/05/29/excel-vba-find-and-list-all-files-in-a-directory-and-its-subdirectories/

      Please let me know if you have any further questions 🙂

  4. wilson says:

    hey awesome tutorial
    was trying to implement it here
    got a question about the first two responses
    how can I skip those “.” and “..”?

    1. pedrumj says:

      Hi there

      You could use an “IF” in your code to check for the “.” and “..” file paths:

      If varDirectory "." And varDirectory ".." Then
          Cells(i + 1, 1) = varDirectory
          Cells(i + 1, 2) = strDirectory + varDirectory
      End If

      Please let me know if you are still having trouble.

  5. Dan says:

    Hi Pedrumj,

    Thanks for a great article it was just what I’m looking for. The problem is I’m not too familar with Excel VBA and I’d like to search for files in a directory.

    I need to match products with their images and tell me which images are missing

    I have 1500 rows of data with column B having image filenames like “qq1645.jpg”

    Starting at B2 I would like a macro that would search a directory (d:stock updates) on my PC where all my images are stored. If the image is present I would like to add AA2 = “Yes”, if the image is NOT present I would like the entry in AA2 = “Missing”
    I would like it to go through all the rows of the sheet automatically until the end.

    Can you point me in the right direction?

    Thanks Dan

    1. pedrumj says:

      Hi there

      Please send me an email so that I could help you out on this.

      1. Dan says:

        Thanks for reaching out to help.
        I was desperate for a quick solution so I posted a project on Elance and got it sorted in an hour.
        Thanks again for the offer to help.

        Dan

        1. pedrumj says:

          No problem, glad you found a solution 🙂

        2. Sebastian says:

          Hi Dan,

          can u pls provide the solution you found… its the exact requirement of mine.

          Thanks
          Sebastian.

  6. arun says:

    Hi guys,

    I hav 1000 images in a folder, i want their details like resolution, dimension, color mode, compression type, size of file and file name in excel, is there any possibility..?

    Pls suggest me with codes…..m nw learning abcd in vb..

    Pls help me guys..m very thankfull to u ppl

    Advance thanks and happy new year….

  7. arun says:

    Hi guys,

    I hav 1000 images in a folder, i want their details like resolution, dimension, color mode, compression type, size of file and file name in excel, is there any possibility..?

    Pls suggest me with codes…..m nw learning abcd in vb..

    Pls help me guys..m very thankfull to u ppl

    Advance thanks and happy new year…. . …….

    1. pedrumj says:

      Hi there

      Please send me a mail so that I can assist you with this.

      1. arun says:

        can u pls provide the E-mail ID

        1. pedrumj says:

          Hi

          You can find my contact details on the contact page.

  8. The Mad Macro Man :) says:

    I am trying to create a macro that lists the mp3 files in a folder, but I want the user to be able to choose a folder. I have the basic code for listing the files but how do I pass the path chosen from the folder open dialog to that code? I have Directory = “C:RandomFolder”, but how do I get the result of the folder open dialog in there?

    1. pedrumj says:

      Hello
      Maybe this will help:

      http://software-solutions-online.com/2014/03/13/vba-folder-dialog/

      Please let me know if you are still experiencing any trouble.

  9. julius says:

    I have excel VBA file that can transfer name of file to excel sheet and automatically link on that file to view. Honestly the file is not mine, I only got from my colleague and I also don’t know how to create VBA but i want to learn. The excel file is protected if anyone can unlock the file, please give also to me. My email Kutsitis@yahoo.com.

  10. Ehsan says:

    Hi

    I want to use the last solution “Example 4” but when I run it in will just returns the main folder name and its address. please help me.

  11. Julius says:

    TO all
    if you want to get the name of the file just type this in notepad
    dir /b > list.xls then save it to the location that you want to get a file name. change “txt” format to “bat” format, then got to the file double click again the file. One list of excel file will appear open it and enjoy… =)

  12. Kory says:

    This is a great tool! What drives the output order in excel? Is there a way to print the output sorted by name rather than what is default? Any help is much appreciated.

    Thanks!

  13. Prabu says:

    While am trying the same, for me it’s not listing in alphabetical order. I have used using dir();

  14. Rachel says:

    Hi there,

    I have tried your example4, however, it only listed the files but no folders….

    Thanks,
    Rachel