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 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 SubList 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…’
8. Select ‘ListFilesInFolder’ from the list and click on ‘Ok’ button
9. Done, enter a folder path in cell B4 and click on the ‘List Files in Folder’ button
great help and . nicely presented