Excel VBA to List Files in Folder with Specific Extension 

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel vba list files in folder with specific 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.

opening vba window

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.

inserting 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.

Step to run vba code.


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.

location of original files


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 for Creating a List of Files in a Folder with PDF File Extension

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 Sub

Code 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.

Result of Creating a List of Files in Folder with PDF File Extension

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 for Using VBA FileSystemObject (FSO) to List File Names

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 Sub

Code 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.

Result of Using VBA FileSystemObject (FSO) to List File Names

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 for Getting List of Files with .xlsx Extension in Folder Using VBA Custom Function

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 Function

Code 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.

Result of Getting List of Files with .xlsx Extension in Folder Using VBA Custom 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 for Extracting List of Files with .xlsm Extension Using Do While Loop

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 Sub

In 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.

Result of Extracting List of Files with .xlsm Extension Using Do While Loop

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 for Getting List of Files (Even Hidden Files) in a Folder and Subfolder At Once

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 Function

Code 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.

Result of Getting List of Files (Even Hidden Files) in Folder and Subfolder At Once

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 to Create a List of Files with Any Extension Using Excel VBA

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 Sub

Code 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.

Result of Creating a List of Files with Any Extension Using Excel VBA

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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo