Sometimes we need to list files with specific extensions in the Excel worksheets. Obviously, we can do it manually, but it’s quite laborious and time-consuming. What can be done? With help from the Visual Basic for Applications (VBA) of Excel, we can easily do the task effectively, as we know most of the tasks in Excel can be done with VBA code. In this article, we will show 5 simple ways to use Excel VBA to List Files in Folder with Specific Extension. In addition, we will show the method for listing files by any extension.
How to Launch VBA Editor in Excel
For inserting any VBA code, open the VBA window first. For that, just click on the Developer tab and select Visual Basic from the appeared options. Also, you can open the VBA window by pressing the keyboard shortcut Alt + F11.
If your Excel doesn’t have the Developer tab automatically, then you can enable the Developer tab in Excel first.
After opening the VBA window, you need to Insert a new Module.
For running any code, just press the Run button from the VBA window, as in the screenshot. Also, you can press the keyboard shortcut F5.
Excel VBA to List Files in Folder With Specific Extension: 5 Suitable Examples
In this segment, we will discuss ways to list files in a folder with a specific extension using Excel VBA. Without further delay, let’s jump to the procedures.
The original location for our files is “C:\ExcelDemy\ “ just like in the image below.
1. Creating a List of Files in Folder with PDF File Extension
In the primary method, let’s see how to create a list of pdf files from the folder on our computer. We did the coding for PDF files. You can change it for any other extension, you have to change the extension type inside the code.
For creating the list of pdf files, just insert the attached code 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 created a sub-procedure called List_PDF_Files.
- We set the ScreenUpdating to False to run the code faster.
- Then, we called another private sub named GetFiles which takes the file location in a folder as input and give the file name list of pdf type from the location.
- In the sub-procedure, we created a VBA object Scripting.FileSystemObject as fso.
- And access the folder from our given folder path by setting the GetFolder object.
- Later on, we accessed the files in that folder using the GetFiles object.
- Afterward, we checked if the last part of the file names contains a .pdf extension. If that is true we printed those names in the active worksheet.
After running the code, you will see the list of pdf files from your selected location on the computer included in the active worksheet.
Read More: Excel VBA to Count Files in Folder and Subfolders
2. Using VBA FileSystemObject (FSO) to List File Names
Now, we will create the same pdf list in the Excel worksheet but will use a VBA object known as FileSystemObject. This method works just like the previous one but the code is much simpler. Let’s hover over the procedures.
Apply the following attached 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.
- Then, we accessed the folder from our given file location “C:\ExcelDemy\” using the GetFolder object.
- Then, we accessed the files in the folder with mFolder.Files object and checked the file type.
- If the file type is pdf then, we printed the names of files on worksheet.
After running the code, you will see the list of pdf files from your selected location in the computer included in the active worksheet.
3. Getting List of Files with .xlsx Extension in Folder Using VBA Custom Function
This time, let’s create a custom function with VBA code to get list of.xlsx files. You can input different extensions in the custom function to enlist files with different extensions.
Just use 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:
- In the code, we created a custom function List_Function which takes file location path and file extension type as arguments.
- Like in previous codes, we used the objects FileSystemObject and GetFolder to access the folder from our given location. The location is given as the argument of the custom function.
- We accessed the files as before with the .Files object and checked the extension of the file name with the VBA InStr If that is true, we saved those names in an array called Result.
- Lastly, the function returns the Res array in the worksheet.
Afterward, use the custom function List_Function to create the list in the worksheet. You have to give the file location and the file extension as arguments for the function.
After applying the function formula, you will see a result list in the worksheet, as shown in the above image.
Read More: Excel VBA to List Files in Folder and Subfolders
4. Extracting List of Files with .xlsm Extension Using Do While Loop
Let’s do the same task with a different approach. This time, we will use the Do While loop of VBA for the enlistment of .xlsm files.
Just insert 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 used the VBA Dir function to get the file names with the strPattern extension from location strPattern. Then, we applied a Do While loop to enlist the names in the worksheet until there was no file with a specific extension left.
Finally, after running the code, you will see the list of .xlsm files in your worksheet.
Read More: How to Use Excel VBA to List Files in Folder
5. Getting List of Files (Even Hidden Files) in Folder and Subfolder At Once
Lastly, let’s see a way to enlist PDF file names from a folder, also from its sub-folders. In this method, we will also show additional statuses like file location or hidden status.
Use 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:
- In the code, we used 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 the previous codes.
- In the SelectSingleFolder function, we used 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.
- AddSheet function helps to print the file names in the worksheet.
After running the code, you will see a list of PDF files in your 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 enlist file names in folders with a specific extension. Let’s see a procedure to enroll file names with any extension.
Apply the following attached 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 created some header on the worksheet and called the private sub GetFiles which gives the file names in the worksheet.
- In the GetFiles private sub, we used GetFolder and GetFiles VBA objects to get the file names.
- This time we didn’t check the file extension. So the code returns file names of any extension.
After running the code, you see the file names of all extensions from your selected location on the computer listed in the worksheet.
Things to Remember
- In all the codes, we used file location “C:\ExcelDemy\”. Change it in the code according to your one.
- Also, we enlisted one file type in each method like pdf or xlsx. You can change it to your desired type. just replace the extension type in the code.
- Don’t forget to save the .xlsm before running any code.
Wrapping Up
- In the first two methods, we included pdf file names from a folder on the computer. In the second method, we used the FileSystemObject which is a VBAobject.
- Then, we included .xlsx and .xlsm files from the folder of our computer location.
- In the last method, we included additional information with the file name list.
- Lastly, we discussed a way to enlist files of all extensions into an Excel worksheet.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
List files in a folder with a specific extension with Excel VBA can be a tremendous task that saves both time and labor. In the article, we covered various ways to enlist file names from folders with a specific extension. Also, we have added a method to enlist file names of any extension. Hopefully, it will come in handy for you.
