VBA Code to list Files in Folder

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
    Sheet1.Range("B9:B1000").ClearContents
    '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
    Loop
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:

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

Similar Posts

One Comment

  1. great help and . nicely presented

Leave a Reply

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