21. VBA Tips - List Files In A Folder
This VBA tip will demonstrate how to list all the files in a folder.
- We'll learn about Scripting Objects
- We'll also see how to recursively call the sub routine so that we can also list files in the subfolders
- The example I will use is the case of trying to manage your photos
- You have thousands of photos all over the place and you think you have replicas that you might be able to delete to save disk space
Here's an example folder, it's contents, and it's subfolder's contents.
Here's what we want to achieve in Excel:
Here's the code we will use:
Explanation
- Open the practise sheet, and recreate the folders if you want to follow along with the tip
- We start by dimming iRow
- The reason for this is so that we can share it between the subroutines, and when we loop through the subfolders, it continues to increment
- iRow is the row that we will put the file name / path etc..
- The ListFiles Sub routine sets the start row... in our case we want row 11
- It also calls the ListMyFiles procedure with inputs equal to the values you input on the sheet. (B7 and B8)
- You can change that path (B7) to whatever you want
- You can also change whether to include or exclude subfolders (B8) by changing from TRUE to FALSE
- The ListMyFiles procedure then creates a FileSystemObject (myObject), and Source folder object (mySource)
- We then have the line: On Error Resume Next
- This was included so that the code doesn't break if you're looking at a folder you don't have access to
- We then loop through each of the files in the source folder, get the attributes we're interested in, and output them to our sheet.
- The last bit of the code asks: if we want to include SubFolders, then call ListMyFiles with the subfolder as input. This ensures that cannot exit the code until all the subfolders are done.
The output we get on the sheet tells us that there are several files that have the same name, last modified date, and size... so we should have a look and see whether we can delete one of them and free up disk space.
Download sheet to practise how to List Files In A Folder in Excel
Training Video on how to List Files In A Folder in Excel:
| Attachment | Size |
|---|---|
| list-files-in-a-folder.xls | 850.5 KB |
»
- Nick's blog
- Login or register to post comments
- 362821 reads
filter
run it as is and use autofilter to show what you want
alternatively, use instr on the file name so see if it contains ".txt" for text files... etc.
p.s. adding your email address will lead to a tonne of spam..
If you're not a member, Register here
Use the InStr function to list specific filetypes
I'm trying to integrate the InStr function into your code to modify it to only list text files, but I can't seem to get it to work...can you please help me with the specifics on using this function? I'm a noob trying to nest conditionals:
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each MyFile In mySource.Files
If InStr(MyFile.Path, ".txt") <> 0 Then
iCol = 2
Cells(iRow, iCol).Value = MyFile.Path
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.Name
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.Size
iCol = iCol + 1
Cells(iRow, iCol).Value = MyFile.DateLastModified
iRow = iRow + 1
End If
Next
need to know how to apply the filter to this code
could you please show me how it is done exactly.
i find this code remarkable.
i also am modifying it to fit my needs with extra specialties.
great job you all
thanx
Thanks
I don't know why I did not think of that.
Thank you
That works nicely.
won't work
I get user-defined errors around line 10, column 50.
try this