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
- 362831 reads
Not Responding
I am attempting to run your beautiful creation. I need to map a network drive that has tons of image files. When I run your macro, it completely freezes excel. Any advice?
select the workbook that's
select the workbook that's running the code, and press CTRL + Break
that will allow you to stop the code if you want.
- if that doesn't work, kill the Excel task in task manager
- if it does work (i.e you see a screen with a button called "Debug") then it is doing something so press "Continue" and let it run
Freezing & Hyperlinks
2 Issues...
Freezing:
Thank you. I killed the application in taskmanager.
But I am still having issues with it freezing after I have it open for a few minutes.I am using Excel 2007 in XP. For the first fold that I am trying to list it has approx 1200 files.
How can I keep it from freezing?
Hyperlinks:
When the macro creates the path file, it is not hyperlinked, so I have had to create a separate column (right of "last modified") that creates a hyperlink to the file path using a formula; Forumla = hyperlink(B11).
Is there a way that the code automate the hyperlinking withing the filepath column?
the only reason it wld freeze
the only reason it wld freeze is if u have network connection problems.. if you try the system on your own computer and it still freezes, then I'd be surprised.
RE hyperlink - your solution is a good one
No Dims ?
Hi Nick. Great bit of code !
I have been testing it sucessfully on my Office 2007 but the guys I am helping out are running 2010 and the code doesnt seem to run at all. It stops at the first Set line and was wondering if VBA2010 is a bit more strict on dimming ? I have declared MyObject as type FileSystemObject, and it seems to move to the next line OK, but I cant work out the type for mySource. Am I barking up the wrong tree completely ? I am only a novice coder, any help would be appreciated !
Cheers.
Chris
all sorted
All sorted, I devclared them all as Objects and working fine :)
Duration
Hi, Great code.
But is there a way to add duration (for music or video files)?
On google I found the following command: GetDetailsOf(Filename,21).
But I can't get it to work.
Thanks,
Elias
Paste the code and I can
Paste the code and I can check it out
Add columns for Date Created and Last Accessed
I am needing to modify this code to output Date Created to column/Cell F11 and Last Accessed to column/cell G11. Right now the code outputs Last Modified to Column E
Here is what I have but it outputs the date created and last accessed to the same column as last modified.....
Sub ListFiles()
iRow = 11
Call ListMyFiles(Range("C7"), Range("C8"))
End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
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
Cells(iRow, iCol).Value = myFile.DateCreated
iRow = iRow + 1
Cells(iRow, iCol).Value = myFile.DateLastAccessed
iRow = iRow + 1
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub
Cells(iRow, iCol).Value =
Cells(iRow, iCol).Value = myFile.DateLastModified
iRow = iRow + 1
Cells(iRow, iCol).Value = myFile.DateCreated
iRow = iRow + 1
=>
Cells(iRow, iCol).Value = myFile.DateLastModified
iCol= iCol+ 1
Cells(iRow, iCol).Value = myFile.DateCreated
iCol= iCol+ 1