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
Nick's picture

try this: sub

try this:

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

Step Through

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?

Nick's picture

put the list of folders in a

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

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

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

Changing the Code to Retrieve Only What You Need

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.

Nick's picture

Request a quote

Happy to provide a customised solution:

Request a quote

 

 

hyperlinks?

Hey Nick,

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

gr MRW

Nick's picture

hyperlinks to files

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

For other files, more complicated

Okay, I just gonna try

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

thnx MRW