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.

Excel Tricks

'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


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…’

Excel Tricks

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

Excel Tricks

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

Excel Tricks

File Manager Tool

Similar Posts

One Comment

  1. great help and . nicely presented

Leave a Reply

Your email address will not be published.