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
- 362823 reads
DateLastModified is in the
DateLastModified is in the original code and it had iRow = iRow + 1 - that works fine
I need:
Cells(iRow, iCol).Value = myFile.DateCreated
iRow = iRow + 1
Cells(iRow, iCol).Value = myFile.DateLastAccessed
iRow = iRow + 1
but when I change it to what I need to get the data skips a cell and puts it down one and it doesn't match up to the files I need and the Last Accessed column stays empty, no data is piped to it:
Cells(iRow, iCol).Value = myFile.DateCreated
iCol = iCol + 1
Cells(iRow, iCol).Value = myFile.DateLastAccessed
iCol = iCol + 1
List Files In A Network Shared Folder?
Hi Nick,
Do you know how to do this on a network shared folder that needs a user name and password?
Kind regards
--
Sergio
unless you have permissions
unless you have permissions to the folders it won't work, and cannot work..
Yes, this is true, without credentials it cannot work
But how do I pass the credentials with the code? It is working when I map the drive, but if not mapped or visible at all? Is it possible to pass GetFolder somehow the credentials? Could you point me into a direction?
Thank you for your time!
--
Sergio
the only way I know is to
the only way I know is to have the drive mapped... however, you might be able to find some VBA to map drives.
How to get last user
How to get last user name for this list?
do you mean the last person
do you mean the last person who modified the file ?
.. don't think that can be done.
Folder Permissions
Hi.
I am using something similar for listing folders (see below). The company that I work has started a merger annd I am now being asked to provide a list of folders and their respective permissions. Do you or any of your readers know how I can modify the code below to be able to provide this.
Thanks
Matt
Sub ListFolders(SourceFolderName As String, IncludeSubfolders As Boolean, rootfolder As String)
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder, root_folder As Scripting.Folder
Dim r As Long, foldercount As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
Set root_folder = FSO.GetFolder(rootfolder)
'line added by dr for repeated "Permission Denied" errors
On Error Resume Next
' display folder properties
r = Range("A1048576").End(xlUp).Row + 1
Cells(r, 1).Formula = SourceFolder.Path
Cells(r, 2).Formula = SourceFolder.Name
Cells(r, 3).Formula = SourceFolder.Size / 1024
Cells(r, 4).Formula = SourceFolder.SubFolders.Count
Cells(r, 5).Formula = SourceFolder.Files.Count
Cells(r, 6).Formula = SourceFolder.ParentFolder.Name
Cells(r, 7).Formula = SourceFolder.ShortName
Cells(r, 8).Formula = SourceFolder.ShortPath
Cells(r, 9).Formula = SourceFolder.DateLastModified
Cells(r, 10).Formula = SourceFolder.ParentFolder.Path
Cells(r, 11).Formula = SourceFolder.ParentFolder.ShortPath
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFolders SubFolder.Path, True, rootfolder
Next SubFolder
Set SubFolder = Nothing
End If
Columns("A:G").AutoFit
Columns("c:c").NumberFormat = "0"
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub
commiserations on the
commiserations on the merger... can be a tense time.
What exactly does this mean:
"provide a list of folders and their respective permissions"
- you can only test what folders you personally have permissions to.
Update
We are looking to provide the sister company with a list of all permissions that exist on all folders individually, so when we actually move over no-one will be missing any files due to permissions not being the same.
Do you know of any other way of doing this, either VB or 3rd party software??
Thanks
Matt