21. VBA Tips - List Files In A Folder

Submitted by Nick on 13 May, 2009 - 11:29

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.

list-files-in-a-folder

Here's what we want to achieve in Excel:

list-files-in-a-folder

Here's the code we will use:

list-files-in-a-folder

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

won't work

Submitted by Anonymous on 29 January, 2010 - 00:16.

I get user-defined errors around line 10, column 50.

try this

Submitted by Anonymous on 1 February, 2011 - 14:44.

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(mySourcePath)

Thank you

Submitted by Anonymous on 22 April, 2010 - 20:05.

That works nicely.

How to modify to only show one or two type(s) of file(s)

Submitted by Anonymous on 28 April, 2010 - 17:37.

How would I modify your code to only show 1 or 2 types of files.

I found one bit of code that will allow me to set the type of file that will be shown with the following code:

Dim filetype As String
filetype = "dwg"
fName = Dir(fPath & "*." & filetype)
While fName <> ""
 i = i + 1
 ReDim Preserve fileList(1 To i)
 fileList(i) = fName
 fName = Dir()
Wend 
 
If i = 0 Then
 MsgBox "No files found"
 Exit Sub
End If
 
For i = 1 To UBound(fileList)
 ws.Range("A" & i + startrow).Value = fileList(i)
Next

However I'm having a hard time adding that to your code. Any help that you could offer would be great. Thanks, Michael

filter

Submitted by Nick on 28 April, 2010 - 18:31.

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

Thanks

Submitted by Anonymous on 28 April, 2010 - 19:48.

I don't know why I did not think of that.

Thanks

Submitted by Anonymous on 19 May, 2010 - 04:40.

It works fine for me.

I got this excel at right time. I Appreciate your work.

My suggestion is Please add one more button to clear the data After use.

Good Luck.

Regards,
A.Kalusalingam

Thank you

Submitted by Anonymous on 28 June, 2010 - 13:50.

This was very useful and was one of the cleanest and most concise examples on how to do a recursive directory listing that I have found

Well done and Thanks a lot for the accompanying video/sheet

Pri

Add another column

Submitted by Anonymous on 22 September, 2010 - 19:50.

I want to add a few more columns to pull "genre", "artist" and such. Is that possible?

Amazing! Thank you.

Submitted by Anonymous on 7 October, 2010 - 19:54.

Thank you for this code. I worked perfectly!

Thank you!

Submitted by Anonymous on 1 April, 2011 - 11:46.

Works like a charm. Thank you very much for sharing this!

Super!

Submitted by Anonymous on 15 April, 2011 - 18:35.

We had someone here at work that had 50 or so lines written that would only do one folder. This is so simple and clean and does all the subfolders. Thanks.

fantastic

Submitted by mkapashi on 22 April, 2011 - 20:27.

You are sheer genius!!

Can you pl give your opinion

Submitted by Anonymous on 27 April, 2011 - 12:31.

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

recommend

Submitted by Nick on 27 April, 2011 - 12:59.

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.

noughts and crosses

Submitted by Anonymous on 27 April, 2011 - 16:24.

Thanks for your advice. I am sure you must have created a noughts and crosses spreadsheet! Can you share it? I am sure there must be lots to learn from it. Thanks.

Excel Games

Submitted by Nick on 28 April, 2011 - 08:15.

Take a look here:

Games

Problems with the scripting steps

Submitted by Anonymous on 1 July, 2011 - 12:02.

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

Use this code. You will get link attached to adress.

Submitted by Anonymous on 26 July, 2011 - 07:07.

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

I'm getting an error when I want to use a single Item

Submitted by JPH on 19 August, 2011 - 14:33.

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

Submitted by Nick on 24 August, 2011 - 08:01.

what are you actually trying to achieve ?
tks

re: looping

Submitted by JPH on 1 September, 2011 - 15:13.

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

Submitted by Nick on 2 September, 2011 - 15:31.

can't you just use:

Exit For

when you find the one you want ?

tks

Nick

Problem opening files within code

Submitted by Anonymous on 5 October, 2011 - 14:26.

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

And how to SET date and time stamp...

Submitted by Anonymous on 6 October, 2011 - 11:06.

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

Skipping lines to denote no files in a folder

Submitted by Anonymous on 26 October, 2011 - 22:10.

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

Skip row if no files in folder

Submitted by Anonymous on 26 October, 2011 - 22:11.

Where would I add the command iRow = iRow + 1 to keep track of empty folders?

Try this modified code... Sub

Submitted by Vishesh on 29 October, 2011 - 11:15.

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

Deleting Date

Submitted by Anonymous on 31 October, 2011 - 21:35.

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

Submitted by Nick on 31 October, 2011 - 22:21.

to round down a date, try using the INT() function..

=INT(NOW())=TODAY()
returns TRUE

I dont think this is what you

Submitted by Anonymous on 1 November, 2011 - 13:44.

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

Date Only from Time

Submitted by Anonymous on 23 January, 2012 - 18:27.

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.

Show only files created after a specific date

Submitted by Anonymous on 16 December, 2011 - 19:56.

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

Submitted by Anonymous on 19 December, 2011 - 23:08.

In addition to the specific date ranges. Can the results show only the last created file in a directory?

Folder Permissions

Submitted by Anonymous on 6 January, 2012 - 10:17.

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

Submitted by Nick on 6 January, 2012 - 12:57.

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

Submitted by Anonymous on 9 January, 2012 - 10:29.

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

folder permissions are fairly

Submitted by Anonymous on 9 January, 2012 - 10:44.

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

How to get last user

Submitted by Anonymous on 7 January, 2012 - 19:36.

How to get last user name for this list?

do you mean the last person

Submitted by Nick on 8 January, 2012 - 12:49.

do you mean the last person who modified the file ?
.. don't think that can be done.

List Files In A Network Shared Folder?

Submitted by Anonymous on 9 January, 2012 - 10:48.

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

Submitted by Nick on 9 January, 2012 - 12:34.

unless you have permissions to the folders it won't work, and cannot work..

Yes, this is true, without credentials it cannot work

Submitted by Anonymous on 10 January, 2012 - 01:03.

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

Submitted by Nick on 10 January, 2012 - 10:23.

the only way I know is to have the drive mapped... however, you might be able to find some VBA to map drives.

Add columns for Date Created and Last Accessed

Submitted by Anonymous on 11 January, 2012 - 16:56.

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 =

Submitted by Nick on 11 January, 2012 - 17:20.

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

DateLastModified is in the

Submitted by Anonymous on 11 January, 2012 - 19:24.

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

Duration

Submitted by Anonymous on 12 January, 2012 - 18:45.

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

Submitted by Nick on 12 January, 2012 - 21:32.

Paste the code and I can check it out

No Dims ?

Submitted by Anonymous on 15 January, 2012 - 23:49.

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

Submitted by Anonymous on 16 January, 2012 - 12:53.

All sorted, I devclared them all as Objects and working fine :)

Not Responding

Submitted by Anonymous on 19 January, 2012 - 16:25.

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

Submitted by Nick on 19 January, 2012 - 16:33.

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

Submitted by Anonymous on 19 January, 2012 - 17:13.

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

Submitted by Nick on 19 January, 2012 - 17:24.

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

Invalid procedure?

Submitted by Anonymous on 25 January, 2012 - 07:46.

Hi Nick, When i download your example file it runs fine, but when i copy the code to my own sheet - no luck. Any ideas what i'm doing wrong? Thanks Gilbert

happy to help, but this

Submitted by Nick on 25 January, 2012 - 07:50.

happy to help, but this sounds a bit more than a forum question

; - >

Request a Quote

Excel / VBA Consultancy

Free Training Videos

Free Spreadsheets

Current Excel / VBA Jobs

Excel / VBA Jobs

Navigation

Who's online

New Excel Experts

21. VBA Tips - List Files In A Folder

Last viewed:

Recent Blogs

Forums

Recent comments

User login