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
»

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

»
Nick's picture

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

»
Nick's picture

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

»

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

»

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

»
Nick's picture

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

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