VBA Code to List Files in Folder

To work on multiples files through VBA programming, you need to have VBA code which can list files in a folder. Here is a simple code for you, this function list files in a folder on 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.

'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

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 menu bar
4. Paste the code in the module
5. Now add a shape in Excel sheet
6. Give a name to the shape like ‘List Files in Folder’
7. Right click on the shape and select ‘Assign Macro…’

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

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