In this article, we will show 5 simple ways to use Excel VBA to list the files in a folder with a specific extension. In addition, we will show the method for listing files with any extension.
How to Launch VBA Editor in Excel
To insert any VBA code, we need to open the VBA window first. To do that, simply click on the Developer tab and select Visual Basic from the ribbon. Alternatively, the VBA window can be opened by pressing the keyboard shortcut Alt + F11.
If you don’t see the Developer tab on your ribbon, enable the Developer tab in Excel first.
After opening the VBA window, Insert a new Module to enter code.
To run the code, just click the Run button from the VBA window as in the screenshot below, or press the keyboard shortcut F5.
Excel VBA to List Files in a Folder With a Specific Extension: 5 Suitable Examples
The original location for our files is “C:\ExcelDemy\ “, like in the image below.
Example 1 – Creating a List of Files in a Folder with PDF File Extension
Let’s create a list of pdf files in the folder. The code below is for PDF files, but can easily be adapted for any other extension by changing the extension type in the code.
To create the list of pdf files, insert the code below in a new VBA module and run it.
Code:
Sub List_PDF_Files()
Application.ScreenUpdating = False
Call GetFiles("C:\ExcelDemy\") 'End string with path separator ( \ )
End Sub
Private Sub GetFiles(ByVal path As String)
Dim fso As Object, Fldr As Object, subF As Object, file As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fldr = fso.GetFolder(path)
Dim i As Variant
i = 4
'Getting files from folder
For Each subF In Fldr.SubFolders
GetFiles (subF.path)
Next subF
'Checking file extension and putting them on worksheet
For Each file In Fldr.Files
If LCase(Right(file.path, 4)) = ".pdf" Then
ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(i, 1).Resize(, 2) _
= Array(file.Name, Replace(file.path, file.Name, ""))
i = i + 1
End If
Next file
Set fso = Nothing
Set Fldr = Nothing
Set subF = Nothing
Set file = Nothing
End SubCode Breakdown:
- Firstly, we create a sub-procedure called List_PDF_Files.
- We set the ScreenUpdating to False so that the code runs faster.
- We call another private sub named GetFiles which takes the folder location as input and returns a list of files with .pdf extension at that location.
- In the sub-procedure, we create a VBA object Scripting.FileSystemObject of type fso.
- We access the folder from our given folder path by setting the GetFolder object.
- Then we access the files in that folder using the GetFiles object.
- Finally, we check if the last part of the file names contain a .pdf extension. If true, we print those names in the active worksheet.
After running the code, a list of pdf files at the selected location on the computer is inserted into the active worksheet.
Read More: Excel VBA to Count Files in Folder and Subfolders
Example 2 – Using VBA FileSystemObject (FSO) to List File Names
Now we will create the same pdf list in our worksheet, but using a VBA object known as FileSystemObject. This method works just like the previous one but the code is much simpler.
Enter the following code in a new VBA module and run it.
Code:
Sub Get_File_Name_FileSystemObject()
Dim mFSO As Object
Dim mFolder As Object
Dim mFile As Object
Dim j As Integer
'Using FileSystemObject
Set mFSO = CreateObject("Scripting.FileSystemObject")
'Accessing file location
Set mFolder = mFSO.GetFolder("C:\ExcelDemy\")
'Geting the file names and putting them on worksheet
For Each mFile In mFolder.Files
If LCase(Right(mFile.Name, 4)) = ".pdf" Then
Cells(j + 5, 2) = mFile.Name
j = j + 1
End If
Next mFile
End SubCode Breakdown:
- We set mFSO to create the object Scripting.FileSystemObject.
- We access the folder from our given file location “C:\ExcelDemy\” using the GetFolder object.
- Then, we access the files in the folder with the mFolder.Files object and check the file type.
- If any of the files are of file type pdf, we print the names of those files in the worksheet.
After running the code, the list of pdf files from the selected location are inserted in the active worksheet.
Examples 3 – Using a Custom VBA Function to List Files in a Folder with .xlsx Extension
This time, let’s create a custom function with VBA code to fetch a list of .xlsx files. Simply input a different extension in the custom function to return the files with that extension instead.
Enter the following code in a new module and run it.
Code:
Function List_Function(ByVal FolderPath As String, FileExt As String) As Variant
Dim Res As Variant
Dim j As Integer
Dim mMyFile As Object
Dim mMyFSO As Object
Dim mmyFolder As Object
Dim mMyFiles As Object
'Accessing the files
Set mMyFSO = CreateObject("Scripting.FileSystemObject")
Set mmyFolder = mMyFSO.GetFolder(FolderPath)
Set mMyFiles = mmyFolder.Files
ReDim Res(1 To mMyFiles.Count)
j = 1
'Checking the file extension & putting them on worksheet
For Each mMyFile In mMyFiles
If InStr(1, mMyFile.Name, FileExt) <> 0 Then
Res(i) = mMyFile.Name
j = j + 1
End If
Next mMyFile
ReDim Preserve Res(1 To j - 1)
List_Function = Res
End FunctionCode Breakdown:
- We create a custom function List_Function which takes a file location path and file extension type as arguments.
- Like in our previous code, we use the objects FileSystemObject and GetFolder to access the folder at our given location, which is given as the argument of the custom function.
- We access the files as before with the .Files object and check the extension with VBA’s InStr function. If the condition is true, we save those names in an array called Result.
- The Result array is then inserted in the worksheet.
In your worksheet, use the custom function List_Function to create a list of file names in the folder and with the extension specified in the arguments of the function. Enter the file location and the file extension in the function like in the image below.
After applying the function in a formula, a result list like the one above will be returned.
Read More: Excel VBA to List Files in Folder and Subfolders
Example 4 – Using a Do While Loop To List Files with .xlsm Extension
This time, we will use the Do While loop of VBA to fetch .xlsm files.
Enter the following code in a new module and run it.
Code:
Public Sub Do_While_xlsm_Extension()
'Defining file location and file extension
Const strFolder As String = "C:\ExcelDemy\"
Const strPattern As String = "*.xlsm"
Dim strFile As String
Dim i As Integer
i = 5
'Getting file names from folder
strFile = Dir(strFolder & strPattern, vbNormal)
'Putting the file names in worksheet
Do While Len(strFile) > 0
Worksheets("Do_While_Specific_Extension").Range("B" & i) = strFile
strFile = Dir
i = i + 1
Loop
End SubIn the code, we use the VBA Dir function to find the file names with the strPattern extension from the specified location. Then we apply a Do While loop to insert the names in the worksheet one-by-one until complete.
After running the code, the list of .xlsm files will be inserted in the worksheet.
Read More: How to Use Excel VBA to List Files in Folder
Example 5 – Listing Files (Even Hidden Files) in Both Folder and Subfolder
Lastly, let’s insert the file names of PDF files in a folder and its sub-folders. We will also show additional information like the file location and hidden/visible status.
Enter the following code in a new module and run it.
Code:
Option Explicit
Sub List_File_Folder_Sub_folder()
Dim pPathSpec As String
pPathSpec = "C:\ExcelDemy\" 'Specify a folder
If (pPathSpec = "") Then pPathSpec = SelectSingleFolder 'Browse for Folder to select a folder
Dim mfso As Object
Set mfso = CreateObject("Scripting.FileSystemObject") 'Late Binding
If (mfso.FolderExists(pPathSpec) = False) Then Exit Sub 'folder exist or not?
Application.ScreenUpdating = False 'Disable Screen Updating to speed up macro
Dim MySheetName As String
MySheetName = "List_Files_Folder_Sub_Folder" 'Add a Sheet with name "Files"
AddSheet (MySheetName)
Dim FileType As String
FileType = "pdf" '*:all, or pdf, PDF, XLSX...
FileType = UCase(FileType)
Dim queue As Collection, mFolder As Object, mSubfolder As Object, mFile As Object
Dim mLastBlankCell As Long, mFileExtension As String
Set queue = New Collection
queue.Add mfso.GetFolder(pPathSpec) 'enqueue
Do While queue.Count > 0
Set mFolder = queue(1)
queue.Remove 1 'dequeue
For Each mSubfolder In mFolder.SubFolders 'loop all sub-folders
queue.Add mSubfolder 'enqueue
'...insert any folder processing code here...
Next mSubfolder
mLastBlankCell = ThisWorkbook.Sheets(MySheetName).Cells(Rows.Count, 1).End(xlUp).Row + 1 'get the last blank cell of column A
For Each mFile In mFolder.Files 'loop all files
mFileExtension = UCase(Split(mFile.Name, ".")(UBound(Split(mFile.Name, ".")))) 'get file extension, eg: TXT
If (mFileType = "*" Or mFileExtension = FileType) Then
With ThisWorkbook.Sheets(MySheetName)
.Cells(LastBlankCell, 1) = mFile 'Path
.Cells(LastBlankCell, 2) = mFolder 'Folder
.Cells(LastBlankCell, 3) = mFile.Name 'File Name
If (mFile.Attributes And 2) = 2 Then
.Cells(LastBlankCell, 4) = "TRUE" 'Is Hidden
Else
.Cells(LastBlankCell, 4) = "FALSE" 'Is Hidden
End If
End With
mLastBlankCell = mLastBlankCell + 1
End If
Next mFile
Loop
'Cells.EntireColumn.AutoFit 'Autofit columns width
Application.ScreenUpdating = True
End Sub
Function SelectSingleFolder()
'Select a Folder Path
Dim mFolderPicker As FileDialog
Dim mmyFolder As String
'Select Folder with Dialog Box
Set mFolderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With mFolderPicker
.Title = "Select A Single Folder"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
SelectSingleFolder = .SelectedItems(1)
End With
End Function
Function AddSheet(MySheetName As String)
'Add a worksheet with custom name
Dim My_sheet As Worksheet, F As Boolean
For Each My_sheet In ThisWorkbook.Worksheets
If My_sheet.Name = My_SheetName Then
Sheets(My_SheetName).Cells.Delete
F = True
Exit For
Else
F = False
End If
Next
If Not F Then Sheets.Add.Name = My_SheetName
'Add table header
With Sheets(My_SheetName)
.Cells(4, 1) = "Path"
.Cells(4, 2) = "Folder"
.Cells(4, 3) = "File Name"
.Cells(4, 4) = "Is Hidden"
End With
End FunctionCode Breakdown:
- We use two custom functions AddSheet and SelectSingleFolder, which are used in the main sub procedure List_File_Folder_Sub_folder.
- The main sub named List_File_Folder_Sub_folder creates a new worksheet named List_Files_Folder_Sub_Folder and uses the GetFolder and GetFolder.Files object to navigate the file names, as in our previous code.
- In the SelectSingleFolder function, we use the VBA application FileDialog to open the file folder. This is an alternative measure if the user doesn’t give a file location in the main sub.
- The AddSheet function helps to print the file names in the worksheet.
After running the code, a list of the PDF files in both the folder and its sub-folders is inserted in the worksheet.
Read More: Excel VBA to Loop Through Files in Folder and Rename
How to Create a List of Files with Any Extension Using Excel VBA
So far, we have discussed the ways to find file names in folders with a specific extension. Now we will write some code to find file names with any extension.
Apply the following code in a new module and run it.
Code:
Sub ListFilesAnyExtension()
Application.ScreenUpdating = False
Dim path As String
path = "C:\ExcelDemy\" 'must end with path separator ( \ )
'Creating the header on the worksheet
Cells(4, 2).Resize(, 3).Value = Array("File", "Type", "File Path")
Call GetFiles(path)
With Cells(4, 2)
.Activate
.AutoFilter
End With
End Sub
Private Sub GetFiles(ByVal path As String)
Application.ScreenUpdating = False
Dim fso As Object, Fldr As Object, subF As Object, file As Object, extn As String
'Accessing folder and sub-folder
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fldr = fso.GetFolder(path)
For Each subF In Fldr.SubFolders
GetFiles (subF.path)
Next subF
For Each file In Fldr.Files
On Error Resume Next
'Identifying the extension
extn = Right(file.Name, Len(file.Name) - InStrRev(file.Name, "."))
If Err.Number = 0 Then Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Resize(, 3) = _
Array(file.Name, extn, Replace(file.path, file.Name, ""))
On Error GoTo 0
Next file
Set fso = Nothing
Set Fldr = Nothing
Set subF = Nothing
Set file = Nothing
End SubCode Breakdown:
- In the main sub ListFilesAnyExtension we create some headers in the worksheet and call the private sub GetFiles, which provides the file names in the worksheet.
- In the GetFiles private sub, we use the GetFolder and GetFiles VBA objects to get the file names.
- This time we don’t check the file extension, so the code returns file names with any extension.
After running the code, the list of the file names with all extensions in the selected location is inserted in the worksheet.
Things to Remember
- In all the code above, we used file location “C:\ExcelDemy\”, which is where our files were stored on our computer. Change it in your code to the location of your files on yours.
- Similarly, simply replace the example extensions we used above in the code to find a list of files with a different extension, like .doc or .xml.
- Don’t forget to save the .xlsm before running any code.
Download Practice Workbook
