15-May-2009 NEW UTILITY: List Files In Folder

Submitted by Nick on 15 May, 2009 - 19:06

This utility will list all the files in a folder and subfolders.

It's like a big browser
You choose:
1. The folder to start at
2. Whether to include subfolders
3. A limit on the number of files you want to return
  - Set this to a small number to test if it's working
  - Then set it to a big number for your run
4. Choose the format you want by formatting the cells on the sheet
5. Choose your own sort order for the results
Then Press "List Files"
Your files will be listed
A pivot table will be created
- You can do lots of things with the pivot table
  - Create a list of folders and size of the files
  - Find how many files are in the folders
  - Find duplicate files
  - Split out files by file type
  - etc...

Here's a screen shot of the main screen in Excel:

list-files-in-folder

Here's the output in Raw form:

list-files-in-folder

Here's one of the things you can do with the data:

- put it in a pivot table and find out the size of the files in each folder

list-files-in-folder

Training Video on how to List Files In Folder in Excel:

Attachment	Size
list-files-in-folder-Excel-2003.xls	292.5 KB
list-files-in-folder-Excel-2007.xls	313 KB
list-files-in-folder-Excel-2007-Row-Limit-Extended.xlsm	242.52 KB

change category list

Submitted by Anonymous on 3 November, 2010 - 21:03.

I absolutely love this file. I would like to add or change the categories that the file selects. Like adding Genre or Artist. could you provide step by step instrucions to create this file from scratch?

Genre

Submitted by Anonymous on 3 November, 2010 - 22:31.

Not sure how to add Genre, I didn't see it in the list available to VBA... will leave that one open to the audience.

List files in folder

Submitted by Anonymous on 17 December, 2010 - 20:01.

I need to change the limit on the Stop_After value. My directory has more than 65000 rows. Can you help with this?

override the data validation

Submitted by Anonymous on 18 December, 2010 - 23:10.

You can override data validation by typing your value in a cell in a new workbook, then copying it and pasting onto the "Stop After This Many Files" cell.

File limit

Submitted by Anonymous on 9 April, 2011 - 08:45.

Can you please assist to allow up to the maximum rows in excel2007 please

File limit extended

Submitted by Nick on 9 April, 2011 - 14:37.

I have now added a new file:

list-files-in-folder-Excel-2007-Row-Limit-Extended.xlsm

General

Submitted by Anonymous on 13 February, 2011 - 06:01.

Many thanks for this. Whilst I have done this in cmd, this is far easier for the average excel user to be able to run, view, and make use of.

Using over network causes macro to freeze.

Submitted by Anonymous on 20 April, 2011 - 15:48.

If i run the macro over 50 or so files, it works fine. However, if there are alot of files in a folder, the macro will freeze and i have to use the system to shut down VBA and Excel. Any ideas?

Thanks,
Wayne Hilburn
whilburn at yahoo.com

network problem

Submitted by Nick (not verified) on 20 April, 2011 - 19:52.

sounds to me like you either have a folder with a huge amount of files, or a network problem.
one more thing it could be... if you deselect Excel when running code, sometimes it looks like it has frozen, but it hasn't..
Try running it on a single folder on the network

network problem

Submitted by Anonymous on 21 April, 2011 - 15:24.

I originally ran the macro at home on a local network. The number of files were around 4000. I waited approx. 30 min and excel was in a frozen status.
I reran the macro at work on a network folder that contained the same approx number of files. It ran successful in approx 12 min.
I stopped screen updating and it helped some. Overall it works fine, but i was wondering if there was something else to consider to make the macro run more efficiently.
thanks,
Wayne

Add Owner to Excel 2003 version

Submitted by Anonymous on 19 May, 2011 - 10:24.

Is there anyway I can add Owner of each file to the output.

Thanks

Steve Heron

owner

Submitted by Nick on 19 May, 2011 - 10:54.

Where do you see this info ?

owner

Submitted by Anonymous on 19 May, 2011 - 12:18.

I see it in Windows Explorer and can be seen through DOS Dir command so I have assumed it is available. Am I wrong in that assumption.

Owner

Submitted by Anonymous on 24 May, 2011 - 15:04.

I have now soted this.

Thanks

Steve

HyperLink to files

Submitted by Anonymous on 20 May, 2011 - 00:14.

Any way to add VB code to add a Hyperlink, to each file, so it can be opened from within Excel? I have 30,000 files in one Directory and it would be helpful if I could open them right from Excel...

Thanks!!

Hyperlink

Submitted by Anonymous on 4 March, 2012 - 19:51.

Case 6
Cells(iRow, Counter).Value = myFile.Name
ActiveSheet.Hyperlinks.Add Anchor:=Cells(iRow, Counter), Address:=myFile.Path

Changing the Code to Retrieve Only What You Need

Submitted by Anonymous on 15 June, 2011 - 17:57.

I have to collect file information data for my boss. It's well over 65,000 files. I am using excel 2003 and have two questions: how do dump data into multiple sheets and/or how do you get the code to only retrieve what you want? For example, I need to find information on all the files that date back before January 2010 so I can remove them.

Request a quote

Submitted by Nick on 16 June, 2011 - 11:48.

Happy to provide a customised solution:

Request a quote

hyperlinks?

Submitted by Anonymous on 30 July, 2011 - 20:23.

Hey Nick,

Is there a way to automatic generate hyperlinks to the listed files?
btw cool setup.

gr MRW

hyperlinks to files

Submitted by Nick on 30 July, 2011 - 21:05.

If they're Excel files, you should be fine... use hyperlinks.add

For other files, more complicated

Okay, I just gonna try

Submitted by Anonymous on 31 July, 2011 - 16:05.

Okay, I just gonna try somethings,
I let you know if it works.

thnx MRW

hyperlinks?

Submitted by Anonymous on 8 August, 2011 - 10:28.

Hey Nick,

I create a macro that generate Hyperlinks, and now i want to put those in a new sheet,
but the file is protected so I can modify/add the sheets,
Is there a way that i can get a not protected file?

thnx Mrw

post code

Submitted by Nick on 8 August, 2011 - 12:43.

post the code here, and I will incorporate..

Additional Folders!

Submitted by Matthew Recknagel on 1 November, 2011 - 21:59.

Hey Nick,

Great website you have, has been really helpful so far! I am trying to adapt your listing program here to accomodate additonal folder locations instead of copying the macro itself 20 different times. I have basic knowledge of coding, but nothing too intricate, though I am a quick learner. Im assuming you would have to edit the sub: ListMyFiles(mySourcePath, IncludeSubfolders) to have it run through additonal folders once you have the correct fields set up and information entered on the first worksheet. Any thoughts?

Thanks,
Matt

try this: sub

Submitted by Nick on 2 November, 2011 - 15:59.

try this:

sub RunMultipleFolders()
call ListMyFiles("c:\myFolder", false)
call ListMyFiles("c:\myFolder1", false)
end sub

Step Through

Submitted by Matthew Recknagel on 2 November, 2011 - 16:08.

Thanks Nick for the quick reply! I can continue calling the sub by editing the VBA but what if i had the layout set up in columns and wanted the VBA to continue down alist of folder locations/include_subfolders/file limit until there were no more folders listed?

put the list of folders in a

Submitted by Nick on 3 November, 2011 - 13:23.

put the list of folders in a separate sheet (XXX) starting in cell A1 and use this:

sub RunLotsOfFolders()
RowOffset = 0
while sheets("XXX").cells(RowOffset,1).value <>""
 'call the list files sub
  call ListMyFiles(sheets("XXX").cells(RowOffset,1).value,false)
 
 ' copy the results to a new sheet
 ' one for you to work out

 RowOffset = RowOffset +1
wend
end sub

combine with get values

Submitted by jerrymaguire on 12 March, 2012 - 15:55.

Is there a way to implement this "http://excelexperts.com/Get-Values-from-other-workbook-without-opening"
specified cells to this listing script

What i want is make a list of exel files in a directory and subdirectory and export exactly the same cells of the group of files to a specified row...

suggest asking the experts to

Submitted by Anonymous on 12 March, 2012 - 16:40.

suggest asking the experts to create something bespoke for you...

How to exclude a folder

Submitted by Anonymous on 15 February, 2012 - 15:24.

Hi Nick,

This is my favourite piece of coding ever. However, how could I get the code to exclude a certain subfolder from the search. Meaning taht it would look at folders, then subfolders and then the files but if he reaches a certain path then it should ignore that folder all together. For example, let's say that it needs to exclude anything that is inside the folder "C:\Archives".

How could I go about doing this?

Thanks,

Simon

Make this change:Sub

Submitted by Anonymous on 15 February, 2012 - 16:43.

Make this change:

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
Dim MyObject As Scripting.FileSystemObject
'######### NEW LINE
If mySourcePath = "C:\Archives" Then Exit Sub

Automatic Browsing

Submitted by Anonymous on 7 May, 2012 - 17:08.

Hi, Just addade a button next to the right cell where the directory is indicated.
This code help browsing for a folder and when you select it, it is placed on the cell (range) named "Folder".

Just copy-paste the following code. I did it in a new VBA module in the file.

Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260

Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type

Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long

Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long

Function BrowseFolder(Optional Caption As String = "") As String

Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long

With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With

FolderName = String$(MAX_PATH, vbNullChar)
ID = SHBrowseForFolderA(BrowseInfo)
If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, vbNullChar) - 1)
End If
End If

End Function

Sub browse()
Dim FName As String
FName = BrowseFolder(Caption:="Select A Folder")
If FName = vbNullString Then
Debug.Print "No Folder Selected"
Else
Debug.Print "Selected Folder: " & FName
Sheets("Main").Range("Folder") = FName
End If
End Sub

Problems on download

Submitted by Anonymous on 13 May, 2012 - 21:06.

When I download the 2007 version file onto a mac, transfer it onto a flash drive and load it onto a ms xp enviornment, it saves the file with a prefix with (.file-list-in-folder-Excel-2007.xls'. and then the function buttons are lost.
Not sure if this is compatibility of imac to windows or something.

Excel / VBA Consultancy

Free Training Videos

Free Spreadsheets

Current Excel / VBA Jobs

Excel / VBA Jobs

Navigation

Who's online

New Excel Experts

15-May-2009 NEW UTILITY: List Files In Folder

Last viewed:

Recent Blogs

Forums

Recent comments

User login