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
- 362833 reads
folder permissions are fairly
folder permissions are fairly complex.
- people can be permissioned via being added to a group, or personally.
- if it's via a group, you also need to know who is in the group
I suggest you don't attempt to do this in Excel / VBA, and get an Access Control specialist in..
The last thing you want is to come in one day and find all your folder permissions messed up.
Nick
Show only files created after a specific date
This code works create. This code allowed me obtain hyperlinks to more than 1200 customer files in seconds. I then added the hyperlinks to my access database. Rather than pulling all the files in the future, I would like to list only the files that were created after my last run.
I am pulling my files by date created. How would I add to the code to include only the files after a specified date?
Exactly what i am looking for
In addition to the specific date ranges. Can the results show only the last created file in a directory?
Deleting Date
Hi Nick,
I am using this macro to create a reference file for many other excel workbooks that I use daily. This is very helpful but I need help modifying a bit of the code.
I want to use date created not modified to provide a reference for a VLookup. To do so I used
Myfile.DateCreated
Unfortunately this not only returns the date but the time as well. Is there a way to subtract that before outputting into the sheet?
Thanks,
Rob
to round down a date, try
to round down a date, try using the INT() function..
=INT(NOW())=TODAY()
returns TRUE
Date Only from Time
FWIW ... by using only INT(NOW()), one relies on implicit conversion of the integer value to a date value. Often times it is better to convert explicitly: CDATE(INT(NOW()).
Of course both work, so it's up to the programmer.
I dont think this is what you
I dont think this is what you meant but it worked.
=INT(Myfile.DateCreated)
Then I formated the range
Selection.NumberFormat = "M/DD/YYYY"
Thanks for the help
Skip row if no files in folder
Where would I add the command iRow = iRow + 1 to keep track of empty folders?
Try this modified code... Sub
Try this modified code...
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Set MyObject = New Scripting.FileSystemObject
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
If mySource.Files.Count = 0 Then
Cells(iRow, 1).Value = iRow - 10
Cells(iRow, 2).Value = mySourcePath
iRow = iRow + 1
End If
For Each myFile In mySource.Files
iCol = 1
Cells(iRow, iCol).Value = iRow - 10
iCol = 2
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, iCol), Address:=myFile.Path, TextToDisplay:=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
Next
If IncludeSubfolders Then
For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.Path, True)
Next
End If
End Sub
Skipping lines to denote no files in a folder
Where would i add the iRow = iRow + 1 command to keep track that a particular folder it is checking has no actual files in it (but in my case has subfolders that do have files it them) as a sort of place holder?
Thanks
Matt