Home » Blogs » Nick's blog

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

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

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

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?

»
Nick's picture

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...

»

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.

»
Nick's picture

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

»
Nick's picture

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

»