List files in a folder using VBA in Microsoft Excel

In this article, we will create a macro to list all the files within the folder.

ArrowMain

On running the macro, file name along with the file path will display starting from cell A17.

ArrowOutput

Logic explanation

In this article we have created two macros, “subfolder_files” and “getting_filelist_in_folder”.
“subfolder_files” macro takes the folder path and the boolean value as inputs and returns the file name within the folder.

“getting_filelist_in_folder” is used to call the “subfolder_files” macro. It provides the folder path value to the macro, with boolean value set 'true'. Also, when file names within the sub folders are required, then we assign boolean value 'true'.

Code explanation

folder_path = Sheet1.TextBox1.Value
The above code is used to extract string value from the textbox.

Call subfolder_files(folder_path, True)
The above code is used to call the “subfolder_files” macro. It assigns the folder path and sets “include_subfolder” property true.

Set fso = CreateObject("scripting.filesystemobject")
The above code is used to create object of file system.

Set subfolder1 = fso.getfolder(folder_path)
The above code is used to create the object of the defined folder.

For Each folder1 In subfolder1.subfolders
Call subfolder_files(folder1, True)
Next
The above code is used to look through all the sub-folders, within the main folder.

Dir(folderpath1 & "*.xlsx")
The above code is used to get the excel file name.

While filename <> ""
count1 = count1 + 1
ReDim Preserve filearray(1 To count1)
filearray(count1) = filename
filename = Dir()
Wend

The above code is used to create an array, which consists of all the file names present inside the folder.

For i = 1 To UBound(filearray)
Cells(lastrow, 1).Value = folderpath1 & filearray(i)
lastrow = lastrow + 1
Next

The above code is used to assign file name within the array to the workbook.

Please follow below for the code

Option Explicit

Sub subfolder_files(folderpath1 As Variant, Optional include_subfolder As Boolean)

'Checking whether to include subfolder or not
If include_subfolder Then
    
    'Declaring variables
    Dim filename, filearray() As String
    Dim lastrow, count1, i As Integer
    
    'Checking whether folder path contain backslash as last character
    If Right(folderpath1, 1) <> "\" Then
        folderpath1 = folderpath1 & "\"
    End If
    
    'Getting the filename of the first file in the defined folder path
    filename = Dir(folderpath1 & "*.xlsx")
    
    'Getting the row number of last cell
    lastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row + 1
    
    count1 = 0
    
    'Looping through all the files in the folder
    While filename <> ""
        count1 = count1 + 1
        ReDim Preserve filearray(1 To count1)
        filearray(count1) = filename
        filename = Dir()
    Wend
    
    On Error GoTo last
    
    'Adding file name to workbook
    For i = 1 To UBound(filearray)
        Cells(lastrow, 1).Value = folderpath1 & filearray(i)
        lastrow = lastrow + 1
    Next
    
End If

last:

End Sub


Sub getting_filelist_in_folder()

'Declaring variables
Dim folder_path As String
Dim fso As Object, folder1, subfolder1 As Object

'Getting path of the folder
folder_path = Sheet1.TextBox1.Value

'Checking whether folder path contain backslash as last character
If Right(folder_path, 1) <> "\" Then
    folder_path = folder_path & "\"
End If

'Calling subfolder_files macro
Call subfolder_files(folder_path, True)

'Creating object of File system object
Set fso = CreateObject("scripting.filesystemobject")
Set subfolder1 = fso.getfolder(folder_path)

'Looping through each subfolder
For Each folder1 In subfolder1.subfolders
    Call subfolder_files(folder1, True)
Next

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Rob
October 1, 2019 at 12:17 pm

Hello,

I'm getting an error, "Compile error: Method or data method not found."

How can this code extract every available metadata field for each file in a folder as well as all metadata for each folder?

Reply ↓