2010 (32-bit and 64-bit)
2007
2003
This add-in creates a listing of the files and folders in a specified folder and all its sub-folders with a variety of customizable features. The 10 columns include the filename, the date created, the last access date, the last update date, the size, the type, the attributes, the full name, the unique number, and the nest depth as shown below.
The result of the add-in is a tabular range, which makes it very easy to extend the result. Examples of such extensions include: convert to an Excel table; add a hyperlink; sort the table differently; or filter the data to see only specific information.
Figure 1
Optionally, the add-in can create an outline of the folder hierarchy through Excel's Outline capability.
Figure 2
The Directory Listing dialog box
Extend the add-in's capabilities
1) The Nest Depth column. It indicates the file's nesting depth relative to the starting folder, which is marked as having a nest depth of 0. Each folder nested inside another folder increases the nesting depth by 1.
2) Outline Grouping. This lets one collapse or expand sub-trees within the directory listing. Once the add-in completes use Excel's menus / tabs to control the outline.
3) Help. The help button links to this file and the website includes a copy of this document.
4) A change in behavior in an unregistered add-in after the trial period. Before version 4, after the trial period an unregistered version of the add-in provided a list of all files in all subfolders in the specified folder. Now, it will display a "Add-in Expired" message and request the user to acquire a registration key for the add-in.
5) Version 4.2 shows the current status in a dialog box rather than in the application's status bar. Use of a dialog box works around an error that shows up under rare circumstances when using the status bar.
It also adds a new capability: cancel a in-process listing.
In Excel 2007 or later, select the TM tab | Utilities group | DIrectory Listing button to bring up the Directory Listing dialog box.
In Excel 2003, select TM|Directory ► Listing... to bring up the Directory Listing dialog box.
In Directory to be indexed specify the folder (directory) to be indexed.
Use the Browse... button to use the mouse to select the directory.
List all subdirectories does what the name implies, i.e., the add-in will search all sub-directories (sub-folders) of the specified directory .
Index subdirectories to a depth of controls the depth to which the add-in will search for nested (embedded) sub-directories (sub-folders). -1 means all subfolders irrespective of depth. 0 means only the specified directory.
List Directories indicates the add-in should list the directories (folders) found during the search.
List Files indicates that the add-in should list the files found during the search.
Result Location indicates the cell where the result table will start. Note that the add-in will overwrite the contents of the range used for the result!
Show Outline causes the add-in to group the nested folders using Excel's Outline Group feature. Note that this Excel feature is limited to 8 nesting levels. Consequently, the Outline Group feature will control only the first 8 levels of sub-folders and files.
The add-in has undergone a fair amount of testing and it has been used by many customers over the last 8 years. Nonetheless, given the large configurations in which it might be used (network drives, virtual drives, etc.) it may fail in some specific environments. It will definitely fail if the directory list requires more rows than are available in the user-specified worksheet.
While the add-in is working, it shows the directory it is currently processing in a dialog box. In addition, the dialog box also contains a Cancel Listing button. Click this button to have the add-in stop when it finishes the current directory.
I have been asked to extend the add-in's capabilities in a variety of ways. Examples of these requests include the option to allow people to restrict the results to certain file types, to add hyperlinks to the files, or to sort the result in various ways. As it turns out, it so easy to do these kinds of things with Excel's native capabilities that adding them to the add-in would be redundant. The result of the add-in is laid out in such a way that one can easily apply Excel's powerful tools to the data.
Select any cell in the range containing the result and sort the data using which column or columns you want. You can always restore the original data by sorting on the column Unique Number.
Use the Data | Filter capabilities to filter the data as required. Also, create a table (called a list in earlier versions of Excel) and then use the column headers (1st row of the results) to filter or sort the data.
The easiest way to create hyperlinks is through the HYPERLINK function. For example, if the add-in result starts in A1, then column H will contain the Full Name and column J will be the last column with data. In K2 enter the formula =HYPERLINK(H2,H2) Copy K2 as far down as there is data in column J. Now, column K will have clickable hyperlinks
And, I imagine other customers of the product will come up with other creative ways in which to manipulate the result of the add-in.
