15-May-2009 NEW UTILITY: List Files In Folder
This utility will list all the files in a folder and subfolders.
- It's like a big browser
- You choose:
- The folder to start at
- Whether to include subfolders
- 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
- Choose the format you want by formatting the cells on the sheet
- 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...
- You can do lots of things with the pivot table
Here's a screen shot of the main screen in Excel:
Here's the output in Raw form:
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
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 |
»
- Nick's blog
- Login or register to post comments
- 80528 reads
Using over network causes macro to freeze.
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
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
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
General
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.
List files in folder
I need to change the limit on the Stop_After value. My directory has more than 65000 rows. Can you help with this?
File limit
Can you please assist to allow up to the maximum rows in excel2007 please
File limit extended
I have now added a new file:
list-files-in-folder-Excel-2007-Row-Limit-Extended.xlsm
override the data validation
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.
change category list
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
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.