Excel VB | XLA addin to display content of a folder

Recently I wanted to list in an email the contents of a windows folder containing 250+ files and folders so that I could comment on some of them rather than copying and pasting. Excel to me seemed like the obvious application to use. I could attach an .xls file to the email with a nice structured comment list. I found a few solutions but in the end decided to write my own Excel .xla file to do exactly what I needed.

1. SysExporter

Description: SysExporter is a small tool that allows you to extract information from any windows application or window and export it as text, HTML, or even XML files. It is designed to retrieve information from programs that do not normally allow you to simply copy/paste information, such as the files list inside a folder or zip archive, list of emails and/or contacts in an email client, registry values in the right pane of the registry editor, error message prompts, etc.

This works perfectly for grabbing text from Windows but this won’t work if you want to list a folder which has sub directories (as it just displays what you see). It also fails to work (in Vista anyway) for any folders that are displaying a search result. Otherwise its a great application for grabbing text from anything you see on your computer which you can’t just easily copy and paste from - and best of all its freeware.

2. Filemaker

A plugin that I have mentioned before from Troi (Troi File Plug-in) has a function that will allow you to list the contents of a folder path in to a fields with paragraph returns after each file or folder (files are left with their extension to distinguish).

TrFile_ListFolder( switches ; FileSpec )

“switches” offer options like files, folders, hidden files, shortcuts etc.

The only problems are:

1. I would need to add in some sort of recursive loop for it to go through the other folders that it finds as it only does 1 level.
2. I want to grab details like file/folder size and modified dates which I would need to factor in to the solution.
3. I want to have it in a database grid like format (a record per file or folder) and not listed all in one field - again more work involved in achieving this.

I felt this wasn’t the most ideal route and could end up being a little messy.

3. Excel Addin

There are a couple of commercial addins out there that you can buy with this function in albeit not all have subfolders. A lot of the addin also relied too heavily on dll file and additional references need to be made in vb - I want something simple, portable and with little setup. The only one I found which ticked most of my boxes was ASAP Utilities (it also offers a few more options for adding hyperlinks, sort order and filtering by file type).

4. Do it yourself

One problem I found with the ASAP addin (v4.2.5) was that it wasn’t quick and on top of that not free. So I decided to write my own - and 1hour later I had it cracked.

From a speed test on “My Documents” folder, with 10,000+ files and folders, I managed to display the full list in about 14.8 secs where as the ASAP addin did it in, a poor, 50.5 secs (and that with most of the options turned off like sort order and hyperlinks).

Some nice options I have added with mine on top of this is that it will display folders on a separate lines not included with the files, greys out the folder names on lines for files (so visually pleasing) and also has another sheet named “Folders Summary” which display a summary of the folders and subfolders and their size in bytes.

My Excel addin is in an XLA format (zipped) and is included below and offered unlocked - so feel free to pull it to bits - if you use it please give some credit or let me know of any improvements you would make?

The file as best as I can tell is compatible with Excel 2000, 2003 and 2007. In Excel 2007 you will find the button to bring up the form to browse for a folder path in the Custom Toolbars section of the Add-ins ribbon. Enjoy.

filedir.zip

Related Posts

• Excel VB | XLA addin for auto cell colour format
• Excel VB | unique random numbers
• Excel VB | random password generator
• Excel VB | positive to negative & vice versa
• Excel VB | duplicate range highlight and remove