VBA Code to list Files in Folder

Complete Excel VBA Course

To work on multiple files through VBA programming, you need to have VBA code that can list files in a folder.

 Here is a simple code for you, this function list files in a folder on an Excel sheet. Note that if there are sub-folders available in the folder then this function will not read the files inside the sub-folders.

VBA to List File name from Excel Folder

VBA code: List all file names from a folder into a worksheet

'This function lists files in a folder
'Note: It will not read any files inside a sub-folder
Public Sub ListFilesInFolder()
    'Variable Declaration
    Dim strPath As String
    Dim vFile As Variant
    Dim iCurRow As Integer
    'Clear old data
    'Set the path of the folder
    strPath = Sheet1.Range("B4").Value
    'Add slash at the end of the path
    If Right(strPath, 1) <> "/" And Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    'Set Directory to folder path
    ChDir strPath
    vFile = Dir(strPath & "*.*") 'Change or add formats to get specific file types
    iCurRow = 9
    Do While vFile <> "" 'LOOP until all files in folder strPath have been looped through
        Sheet1.Cells(iCurRow, 2).Value = vFile
        vFile = Dir
        iCurRow = iCurRow + 1
End Sub

List All File Names from A Folder into Worksheet by Using VBA Code

To use this code in your Excel file, follow below steps:

Complete Excel VBA Course

1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from the menu bar
4. Paste the code in the module
5. Now add a shape to the Excel sheet
6. Give a name to the shape like ‘List Files in Folder’
7. Right-click on the shape and select ‘Assign Macro…’

Macro to List File name from Excel Folder

8. Select ‘ListFilesInFolder’ from the list and click on ‘Ok’ button

Macro to List File name from Excel Folder

9. Done, enter a folder path in cell B4 and click on the ‘List Files in Folder’ button

Macro to List File name from Excel Folder
File Manager Tool

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

Excel VBA Course : Beginners to Advanced

We are currently offering our Excel VBA Course at discounted prices. This courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion

This course is going to help you to excel your skills in Excel VBA with our real time case studies.

Lets get connected and start learning now. Click here to Enroll.

Secrets of Excel Data Visualization: Beginners to Advanced Course

Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.

This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.

So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.

Similar Posts

One Comment

  1. great help and . nicely presented

Leave a Reply

Your email address will not be published. Required fields are marked *