VBA Code to List Files in Folder
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. In this article we will learn three methods to list files in a folder and sub-folders.
Method 1 : List files in one 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.
'This function lists files in a folder
'Note: It will not read any files inside a sub-folder
Public Sub ListFilesInOneFolder()
'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
You can refer this section to know how you can attach this code to a Shape in your Excel Sheet.
Method 2: List Files in a folder and sub-folders
In the below code, there are 2 functions written which are going through each sub-folder in the main folder and listing the file details like file name and folder path.
Public Sub ListAllFiles()
Dim fso As Object
Dim folder1 As Object
Dim vFile As Variant
Dim lCurRow As Long
'Clear old data
Sheet3.Range("B9:C1000").ClearContents
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder1 = fso.getFolder(Sheet3.Range("B4").Value)
'List file in the folder
lCurRow = 9
'Set Directory to folder path
ChDir folder1.Path
vFile = Dir(folder1.Path & "\*.*")
Do While vFile <> "" 'LOOP until all files in folder strPath have been looped through
Sheet3.Cells(lCurRow, 2).Value = vFile
Sheet3.Cells(lCurRow, 3).Value = folder1.Path
vFile = Dir
lCurRow = lCurRow + 1
Loop
'List files in subfolders
ListFilesInFolder folder1
MsgBox "Done", vbInformation
End Sub
Public Sub ListFilesInFolder(ByRef objFolder As Object)
Dim vFile As Variant
Dim lCurRow As Long
Dim objSubFolder As Object
Dim objsubfld As Object
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
lCurRow = Sheet3.Range("B" & Rows.Count).End(xlUp).Row + 1
For Each objSubFolder In objFolder.SubFolders
'Set Directory to folder path
ChDir objSubFolder.Path
vFile = Dir(objSubFolder.Path & "\*.*")
Do While vFile <> "" 'LOOP until all files in folder strPath have been looped through
Sheet3.Cells(lCurRow, 2).Value = vFile
Sheet3.Cells(lCurRow, 3).Value = objSubFolder.Path
vFile = Dir
lCurRow = lCurRow + 1
Loop
Next
For Each objsubfld In objFolder.SubFolders
ListFilesInFolder objsubfld
Next
End Sub
You can refer this section to know how you can attach this code to a Shape in your Excel Sheet.
Method 3: Using Excel Function
The last method is to use Excel formula to list file in the folder. Before you use this formula, you need to enable few settings in Excel as mentioned below.
Adjusting Macro Settings
- Go to Excel Options available under File>Options
- Look for Trust Center (which is normally at the bottom of left side pane)
- In the Trust Center, select Macro Settings and enable all VBA macros. This step unlocks the FILES function and other legacy macros in Excel.
Creating Formula using Name Manager
- Let’s open Name Manager from Formulas menu and create a new named range.
- Keep the Name as Files and Refers to as ‘=Files(Sheet1!$A$1)’
Using Files in the formula
- In cell A1, let’s mention the folder path from which we want to retrieve file names. Note there is a * mentioned at the end of the path to retrieve all files.
- The final step is to use the formula working. For that, just type =Files in any of the cell and hit enter
- You will see the list of files available in the given folder directly in your Excel sheet.
- If you want the list of files vertically listed then simply write the formula inside Transpose.
How to attach VBA code with a shape in Excel Sheet
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