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

Nick's picture


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:

AttachmentSize
list-files-in-folder-Excel-2003.xls292.5 KB
list-files-in-folder-Excel-2007.xls313 KB
list-files-in-folder-Excel-2007-Row-Limit-Extended.xlsm242.52 KB

List Files in Folder

Hi Nick

I just wanted to ask if the 2007 version of the utility is still OK for Office 2010, Or will you be upgrading the same in the future. It is a great utility, and I use the same all the time.

Can you please advise

Regards

Mosquito

Attributes

This has helped me a great deal already. As a novice, and non-IT, can you define what these fields are?

Attributes: default is 32 (what does the 32 represent?)
Name / Path / Short Name / Type. Do these cells / choices need an entry in the cell? How or what are other options?

Thanks for your help.

Dianne

64 Bit OS

This looks like just what I'm looking for. I am new to VBA, and am running Windows 7 and 64-Bit OS.
Excel 2010.

This 2007 workbook is giving me a "Compile Error
The code in this project must be updated for use in 64 bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute." Can you help?

Thanks so much.

Dianne

Exclude file types

Love this code, but i don't need it to show everything, is there a way to have the code to exclude information not needed, eg; only show xls;xlsx;pdf etc...

cannot run the macro

Hi nick,

i am supposed to list files in folders and sub folders. i dowloaded your file and tried to run " list files", but it gives the error " cannot run Macro, macro may not be available...". Can you please help me.

thanks
Ann

Problems on download

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.

Automatic Browsing

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

How to exclude a folder

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

Make this change:

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

Additional Folders!

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