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.


9 thoughts on “Excel VB | XLA addin to display content of a folder

  1. Jc Cuadra

    Hi! very nice add in! it really helped a lot in my line of work..

    ive tried ASAP utilities aswell and its good to.

    by the way, will you be putting options to put HYPERLINKS as well? thanks! i hope to receive the version with hyperlinks! thank you bro! and more power!

  2. Simon Page Post author

    Glad you found it useful. I found ASAP lacking and not free.

    Hyperlinks are straightforward (If you download the file again you will see it now prompts you at the end if you want to add hyperlinks).

    Here is the quick do statement code I used to do that:

    TheAddress = ActiveCell.Value & ActiveCell.Offset(0, 1).Value
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, address:=”” & TheAddress & “”, TextToDisplay:=”” & TheAddress & “”
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.Value = “”

  3. Sash

    Hey… This is the best software for the purpose.. I had the need a couple of times to create such spreadsheets so that my team can comment, but I had always gone through the more laborious route. At the extreme I once coded a simple macro, but that is nothing compared to what you have done…. Your XLA rocks!!!
    Many thanks and kudos for the work….

  4. HL

    Is it possible (PLEASE!!) to help me with a similar problem..??
    I need to to list all folders in a certain folder (not sub folders), i need the folder name and last date modified….
    Can you help??
    Any help is appreciated!

  5. Simon Page Post author


    If you aren’t bothered with having a neat coded solution then just use sysexporter (point 1. above).

    If you open up a window with the list of folders in and run it (in explorer detailed view with modified date showing) you will get a text file of the folder names and modified dates as shown.

    HTH, All the best

  6. Charles V. Rennaker

    After reading all those good reports above, I must be the dumbest one out there, I need to be able to record the contents of CD’s I have saved image files into. When I had Office 2003 it was easy to add a file directory xla, now that I use Office 2007 I am undable get it installed with Excel 2007. Have tried yours as well. I have activated Macors, still can’t seam to make it work. Hope you can help.


Leave a Reply

Your email address will not be published. Required fields are marked *