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
- 362834 reads
And how to SET date and time stamp...
Nick
that's a great VBA script... I was just digging to manage my pictures date/time when I found it...
My question is how to change the date/time attribute... I used to cameras during my vacation and they had about 6 hours of difference between their internal clocks... Now I am looking for so way to have those pictures "matching" time.
Thanks
Reginaldo
Problem opening files within code
I'm using a modified version of the code to choose a folder then open all .csv files contained therein.
The code works fine on my computer, by my client (who's in Japan) can't use it. We've narrowed the problem down to the code which finds the files.
I've changed my code to do something very similar to the above, and produce a list of the information for files within the folder. Again, this works fine on my computer. However, my client finds that - regardless of which folder he picks - the code outputs the contents of his Desktop folder.
Anyone see where it's going wrong?!
Sub ListMyFiles()Dim MyFolderName As String
Dim MyFileName As String
Dim iCol, iRow As Integer
Application.FileDialog(msoFileDialogFolderPicker).Show
MyFolderName = CurDir
If Right(MyFolderName, 1) <> "\" Then
MyFolderName = MyFolderName & "\"
End If
Set MyObject = CreateObject("Scripting.FileSystemObject")
Set mySource = MyObject.GetFolder(MyFolderName)
On Error Resume Next
Range("C2").Value = MyFolderName
iRow = 5
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
Next
End Sub
I'm getting an error when I want to use a single Item
Hello Nick,
I'm using this code with good results.
When I use the "For Each" loop it works beautiful.
But when I want to adress the individual items I'm getting an error.
This is my code:
Set MyObject = CreateObject("Scripting.FileSystemObject")
Set mysource = MyObject.GetFolder(Pathname)
i = 2 'example
Filename = mysource.Files.Item(i).Name
When I run this code I get the error:
Invalid procedure call or argument
I know there are more than 2 files in the folder because mysource.Files.Count = 197
What am I missing/doing wrong
looping
what are you actually trying to achieve ?
tks
re: looping
I want the loop to stop when the file I want is in the picture.
I don't need to go through them all.
items
Exit Forwhen you find the one you want ?
tks
Nick
Use this code. You will get link attached to adress.
Dim iRow
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 = 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
Problems with the scripting steps
Hi Nick, I keep getting "invalid procedure call or argument" at the Set mySource line. I've added the scripting reference as you say and also tried the suggestion you made about using fs=CreateObject ...., but no luck. When i download your example file it runs fine, but when i copy the code to my own sheet - no luck. Its a great piece of code and i'd love to get it working - any ideas what i'm doing wrong? Thanks Dave
Can you pl give your opinion
Hi Nick
You are a genius in Excel, as is obvious from your work. I am a Financial Cost Analyst who uses Excel VBA extensively for my reports and analysis. This definitely puts me a cut above my colleagues.
I am always hungry and thirsty for more knowledge on Excel and vba, but no intention of becoming a programmer. Can you pl give me your opinion on:
1. Should I learn class modules and classes? I tried in the past but gave up since it seemed too complicated, and I didn't think there was anything that could not be achieved with normal macros.
2. Should I learn about dictionaries and regexp?
3. Can you recommend some good books? I have John Walkenbach's Power Programming, and someone recommended Prof Excel Development by Stephen Bullen etc.
I am sorry to trouble you, and truly grateful for your help.
Besy wishes
MK
recommend
tks
; - >
1. and 2.
- no need for your purposes.
3. Forget books, I recommend attacking a problem and finding the solution.
- Try to create a game of noughts and crosses with VBA. this will test all essential skills.