| Downloads |
|---|
| 99574.zip |
|
Executive Summary: The default file properties that you typically see when you open a document folder in the Details view in Windows Explorer are adequate for everyday file listings, but there are times when you might want to see additional property details. When that's the case, you can use a HTML Application (HTA) named GetExtendedFilePropertyDetails.hta to view the file properties in a Microsoft Excel spreadsheet. This HTA works on Microsoft Windows Server 2008, Windows Vista, Windows XP, Windows Server 2003, and Windows 2000. |
Windows Server 2008 and Windows Vista have 267 properties for each file—quite a jump from the 38 properties available in Windows XP, Windows Server 2003, and Windows 2000. The default file properties (e.g., Name, Date modified, Type) that you typically see when you open a document folder in the Details view in Windows Explorer are adequate for everyday file listings, but there are times when you might want to see additional property details. For example, on my Vista machine, I like to see the Bit rate, Channel number, Composers, Date created, Genre, and Publisher property details for music files and the Authors, Categories, Comments, Date created, Pages, Status, Subject, Title, Total editing time, and Word count property details for Microsoft Office documents.
Admittedly, changing the file properties displayed in folders is easy to do—you just right-click the heading bar in Windows Explorer's Details pane. However, this approach grew old for me because I was constantly changing the file properties for various folders. To make matters worse, I didn't always consistently apply and maintain the properties. So, I wrote an HTML Application (HTA) named GetExtendedFilePropertyDetails.hta.
With GetExtendedFilePropertyDetails.hta, you don't have to manually change the file properties in a folder's heading bar when you want to see some extended properties for files. Instead, you can use the HTA to view the file properties in a spreadsheet. The HTA includes four predefined categories of properties that pertain to certain types of files (e.g., music files, Office files). The HTA also lets you handpick file properties if desired. Let’s take a look at how to use GetExtendedFilePropertyDetails.hta and how this HTA works.
How to Use the HTA
You can download GetExtendedFilePropertyDetails.hta by clicking the Download the Code Here button at the top of the page. Note that:
- Microsoft Excel is the reporting medium, so you need Excel to run this HTA. Versions prior to Excel 2007 have a limitation of 256 columns, so you won't be able to retrieve all 267 file properties at one time should you choose to do so. Excel 2007 doesn't have this limitation.
- This HTA is designed to be run locally and not against remote computers.
- You can run this HTA on Server 2008, Vista, XP, Windows 2003, and Win2K. When you run it on XP, Windows 2003, or Win2K, you'll just have fewer extended file properties to choose from.
When you first launch GetExtendedFilePropertyDetails.hta, the HTA's UI and a Help window appear, as Figure 1 shows. The Help window presents a general overview of how to use the HTA. This window disappears when you click anywhere inside the UI, but you can access it again at any time by pressing the F1 key. The HTA also has context-sensitive help. If you need detailed information relating to the functionality of a specific area, you can place your mouse cursor in that area and press F1.
Let’s take a look at each component in the UI. In the List Files in Folder text box, you enter the path to the target folder. If you’d rather not key in the path, you can use the Browse button to visually traverse your file system and pick the folder. Your folder selection will then be entered into the List Files in Folder text box.
You can manually select the extended file properties for that folder, or you can let the HTA automatically select them for you. To manually select the properties, you must first choose the You Select option from the User Selection Categories list box, then click the properties you want in the Extended File Property Items list box. To select more than one item, hold down the Ctrl key while clicking the various properties. If the properties are grouped together, you can click the first item in the group, then hold down the Shift key while selecting the last item in the group.
To let the HTA automatically select the properties, you need to choose a predefined category from the User Selection Categories list box. There are predefined categories for picture files, music files, and Office document files. There's also a predefined category that's applicable for almost any type of file.
These four predefined categories are constructed as arrays in the HTA's code, so you can easily customize the extended file properties in each category if desired. You can also construct your own predefined categories. For example, you might want to create categories for graphics files or video files. If you're interested in doing so, check out the sidebar "Determining the Properties to Include in Predefined Categories" (www.windowsitpro.com/articles/articleid/99577/99577.html).
Besides the You Select option and the four predefined categories, the User Selection Categories list box includes a Clear All Property Selections option. You can use this option to clear all the extended file properties currently selected in the Extended File Property Items list box.
Clicking the ProcessSelected button starts the process of collecting extended file property information based on your selections. If you select a limited number of extended file properties (5 to 20 for instance) and your target folder is small to moderately sized, the HTA performs relatively quickly. However, if your target folder contains a very large number of files (several thousand for instance), you might have to wait longer for the process to complete. You'll likely experience a long wait if you select many extended file properties and the target folder contains numerous files.
The Add Worksheets check box controls the behavior of Excel. When this check box is selected (the default), each extended file property list that you generate by clicking the ProcessSelected button will be created in a separate worksheet within the same Excel spreadsheet. When this check box is cleared, a new Excel spreadsheet is created every time you click the ProcessSelected button.
The last component in the UI is the Exit button. Clicking the Exit button closes the HTA.
Although the UI is simple and easy to use, there's a lot of activity taking place behind the scenes. When you launch GetExtendedFilePropertyDetails.hta, the HTA's BODY element code and Window_Onload subroutine perform several actions before you even see the UI. Once the UI appears and you start entering information into it, the preselect, showsele, and showxl subroutines go to work.
The BODY Element Code
As soon as the HTA is launched, the BODY element code in Listing 1 springs into action. To begin, the On Help event (onhelp="showhelp") in callout A sets up an event trigger so that if the F1 key is pressed while the HTA is open, the ShowHelp subroutine will run.
Next, the UI window is created. Most of the UI components are created in the typical fashion, so I won't cover them here. If you're unfamiliar with how to create HTAs, see the Learning Path box near the top of the page for articles that can help you learn the basics.
The Extended File Property Items list box, though, isn't your typical list box. Callout B shows the code for it. (Note that in the code, the Extended File Property Items list box is referred to as the PropertySelect list box.) The Extended File Property Items list box is dynamically filled, so its code differs considerably from the code you'd use for static list boxes, such as the User Selection Categories list box (i.e., the CategorySelect list box) in callout C.
Probably the most noticeable difference in the code for two list boxes is that the Extended File Property Items list box doesn't contain any option elements when it's initially created. The properties in the Extended File Property Items list box will be added later. Because this list box doesn't include any hard-coded options, you can't use the select size element to set its size. (The select size element lets you specify the number of options you want visible, without having to scroll.) Even if you were to include the select size element, it wouldn't have an effect on the list box's size because the option elements aren't included. The list box would default to a size that accommodates the height of the list box's scroll bar, which means you'd see only three or four options at a time. I found that viewing so few options at a time didn’t look good and required a lot of scrolling to get through the entire list, so I hard-code the size of the list box by using the style element to define the list box's height and width. Another difference in the code for the two list boxes is that the Extended File Property Items list box is initially disabled. This prevents you from selecting specific file properties when a predefined category is selected. When you choose the You Select option from the User Selection Categories list box, the Extended File Property Items list box is enabled.
The Window_Onload Subroutine in Listing 2 shows the Window_Onload subroutine, which runs prior to the UI being displayed. Besides bringing the UI to the forefront and sizing it, this subroutine dynamically populates the Extended File Property Items list box with the extended file properties available in the system. Callout A shows the code that populates this list box. Here's a step-by-step look at how this code works:
Step 1: The code begins by creating an instance of the Shell object, which represents the Windows Shell.
Step 2: The code uses the Shell object's NameSpace method to return a Folder object (objFolder). For this HTA, I chose the C directory to make the code as generic as possible because that folder is virtually in every Windows system. However, at this point, I could've specified any local folder because the goal in this step is simply to create a Folder object in order to get the extended file properties' names (and not their values).
Step 3: The code uses a For…Next statement to loop through all the file properties in the C folder. I initially used a While…Wend statement to loop through the properties until I reached an empty property, but I soon discovered that this approach didn't work. When I used it on XP, only about two-thirds of the available properties were displayed in the Extended File Property Items list box. After some testing, I discovered the reason: Although all Windows OSs use a zero-based index for file properties (0 to 266 in Server 2008 and Vista, and 0 to 40 in XP and earlier), not all the index numbers have assigned properties. Whether all the index numbers have assigned properties depends on the OS. For example, every index number has an assigned property in Server 2008 and Vista, whereas index numbers 27, 28, and 31 don't have assigned properties in XP, Server 2003, and Win2K.
To filter out incomplete index number-property pairings, I included the If…Then…Else statement
If objFolder.GetDetailsOf(objFolder.Items,i) <> "" Then
in the For…Next loop. This statement uses the Folder object's GetDetailsOf method, which lets you retrieve detailed property information about items in a folder. I use this method twice in the code at callout A. I first use it in this step to see whether each index number has an assigned property. I then use it in step 4 to obtain the file property names.
The GetDetailsOf method's syntax is
Folder.GetDetailsOf(FolderItem,Index)
where
- FolderItem is the item for which you want to retrieve information. It can be a file or another item in a folder (a FolderItem object), a collection of folder items (a FolderItems object, which you can obtain using the Folder object's Items method), or a null string (""). When referencing a file, you can retrieve the values assigned to the file properties, such as the file's name (e.g., GetExtendedFilePropertyDetails.hta) and size (e.g., 16KB). When referencing a folder-items collection or null string, you can retrieve the names of the file properties, such as Name and Size.
- Index is an integer that specifies what to retrieve. This integer corresponds to the file property's index number.
- Folder is the Folder object.
So, the code
For i = 0 to 266
If objFolder.GetDetailsOf(objFolder.Items,i) <> "" Then
loops through all the file properties in the C folder. For each property, it uses the Items method to access the folder-items collection representing the C folder. Because the collection is a FolderItems object, the GetDetailsOf method retrieves the file property's name. If GetDetailsOf returns an empty string because it encountered an incomplete index number-property pair, the code loops to the next file property in the folder. If GetDetailsOf returns a value (i.e., a name), the code proceeds to step 4.
Note that if you prefer to keep your code as short as possible, you can specify a null string instead of objFolder.Items, like this
For i = 0 to 266
If objFolder.GetDetailsOf("",i) <> "" Then
and get the same results.
Step 4: With a complete index number-property name pair at hand, the code dynamically loads the Extended File Property Items list box with that item. To do so, the code first creates a Dynamic HTML (DHTML) Option object, which represents the option element in an HTML file. Then, the code uses the GetDetailsOf method to retrieve the file property's name and assigns it to the Option object's text property. The text property's value is the option you'll see in the Extended File Property Items list box. Next, the code assigns the file property's index number to the Option object's value property. The value property is used later in the HTA to obtain the value assigned to file property (e.g., the file's name or size), which gets displayed in the spreadsheet. Finally, the code adds the new option to the Extended File Property Items list box.
When you run the HTA on Server 2008 and Vista, the For…Next statement loops through all 267 file properties, adding each one to the Extended File Property Items list box. When you run the HTA on XP, Windows 2003, and Win2K, the For…Next statement loops 267 times, but only 38 properties are added to the Extended File Property Items list box. The index numbers without assigned properties (i.e., 27, 28, 31, and 41 through 266) result in empty strings, which means they never get to step 4 and thus don't create blank options in the Extended File Property Items list box.
After the Extended File Property Items list box is populated, the Window_Onload subroutine sets a flag variable named FirstTime to True, as callout B in Listing 2 shows. This flag variable is used to determine whether the Excel Application object needs to be instantiated in order to create a new spreadsheet. As I mentioned previously, when the Add Worksheets check box is selected, each extended file property list that you generate is created in a separate worksheet within the same Excel spreadsheet. When that check box is cleared, a new Excel spreadsheet is created every time. Before the UI appears, the Window_Onload subroutine sets FirstTime to True so that the Excel Application object is instantiated the first time you click the ProcessSelected button to create a file property list. Immediately afterward, the flag is set to False unless you cleared the Add Worksheets check box.
The last action that takes place before the UI appears is a call to the ShowMainHelp function. This function creates the general Help window that appears when the UI opens.
The preselect Subroutine
After the HTA's UI opens, you enter your target folder, select the properties you're interested in, then click the ProcessSelected button. When you click the ProcessSelected button, you trigger that button's onclick event, which calls the preselect subroutine. This subroutine consists of three lines of code:
ProcessSelectedButton.value="This may take a few moments"
ProcessSelectedButton.disabled = True
TimerInterval = window.setInterval("showsele",100)
The first line temporarily changes the text on the button's face from ProcessSelected to This may take a few moments while the HTA is processing your selections. The second line disables the ProcessSelected button so that you can't click it again until the HTA is done with that processing. The third line implements a workaround for a problem that the HTA encounters when trying to change the button-face text.
When you make a cosmetic change such as changing button-face text while a subroutine process is running in the background, you often won’t see the cosmetic change until the subroutine process completes. In this case, that's too late. So, the preselect subroutine works around this problem by using two DHTML methods called setInterval and clearInterval. The setInterval method runs a block of code, pauses for a specified number of milliseconds, then runs the code again. This continues until the clearInterval method is used to cancel the setInterval timer. The new process that's spawned runs in the background as a separate thread. This short break from the main process acts as a pause and gives the HTA time to render the cosmetic change.
The basic syntax for setInterval is
iTimerID = window.setInterval(vCode, iMilliSeconds)
where vCode is the code to execute (or a pointer to it) after the specified interval has elapsed and iMilliSeconds is the interval in milliseconds. So, the line
TimerInterval = window.setInterval("showsele",100)
runs the showsele subroutine every 100 milliseconds. The showsele subroutine should run only once, so the first thing the showsele subroutine does is use the clearInterval method to cancel the timer. The syntax for clearInterval is
window.clearInterval(iIntervalID)
where iIntervalID is the value returned by the setInterval method. So, the line
window.clearInterval(TimerInterval)
in the showsele subroutine cancels timer. For more examples of how to use the setInterval and clearInterval methods, see "Using setInterval to Overcome an HTA Shortcoming" (www.windowsitpro.com/articles/articleid/98720/98720.html).
The showsele Subroutine
The showsele subroutine in Listing 3 determines which OS is being used and which extended properties the user has selected. As I just mentioned, this subroutine begins by canceling the timer, after which it declares the On Error Resume Next statement. It then checks to see whether the Clear All Property Selections option is selected in the User Selection Categories list box by using the DHTML Select object.
List boxes' option elements are similar to zero-based arrays in that you can refer to a particular option element by specifying its position within an array. As callout C in Listing 1 shows, the User Selection Categories (i.e., CategorySelect) list box has six option elements, so the first option Clear All Property Selections is considered element 0, the second option Typical Picture Details is considered element 1, and so on. You can use the Select object's options collection with an option element's number to retrieve the Option object representing that option element. You then use the Option object's selected property to see if that option is selected. The syntax looks like
ListBox.options(x).selected
where ListBox is the Select object representing the list box and x is the number for the option element you're checking. For example, the code
CategorySelect.options(0).selected
checks to see if the Clear All Property Selections option is selected in the User Selection Categories list box.
The selected property returns True when the specified option is selected and False when it isn't selected. Thus, you can incorporate this code into an If…Then…Else statement so that a block of code executes when the option is selected. The showsele subroutine does just that in the code in callout A in Listing 3. If the Clear All Property Selections option is selected in the User Selection Categories list box, the subroutine uses a For Each…Next statement to loop through and deselect all the options in the Extended File Property Items list box. Afterward, the subroutine re-enables the ProcessSelected button and resets the button-face text to ProcessSelected. Finally, because an extended file property list isn't created when the Clear All Property Selections option is selected, the subroutine exits.
If the Clear All Property Selections option isn't selected, the showsele subroutine uses Windows Management Instrumentation's (WMI's) Win32_OperatingSystem class to determine the computer's OS version, as callout B shows. The OS information is needed because the arrays for the remaining categories in the User Selection Categories list box use the file properties' index numbers—and those index numbers are OS-specific, as I describe in the sidebar "Determining the Properties to Include in Predefined Categories" (www.windowsitpro.com/articles/articleid/99577/99577.html).
The code at callout C in Listing 3 uses the OS version to determine which arrays to create. If the OS is later than version 5.0, the subroutine declares four category arrays for use on Server 2008 and Vista machines. Otherwise, the subroutine sets up four category arrays for use on XP, Windows 2003, and Win2K computers.
VBScript's Split function is used to create the arrays. For example, the code
PicArray = Split("233,227,223,221,219,218,153,151,12,1,0",",")
uses the Split function to create the array for the Typical Picture Details category on Server 2008 and Vista machines. The first parameter contains the index numbers of the file properties in that predefined category. Table 1 shows the property names associated with those index numbers. The second parameter specifies the delimiter, which is a comma.
The array for the Typical Picture Details category on XP, Windows 2003, and Win2K machines is initialized with the code
PicArray = Split("26,25,24,1,0",",")
Table 2 shows the associated property names for those property index numbers.
After initializing the arrays, the showsele subroutine determines which predefined category of file properties was selected. It then uses the array associated with that category to programmatically select those properties in the Extended File Property Items list box.
For example, in callout D, the showsele subroutine determines whether the Typical Picture Details category (element 1) was selected in the User Selection Categories list box. If it was, the subroutine uses an outer For Each…Next loop to iterate through each item in the Extended File Property Items list box, deselecting each one to remove any items that might have been previously selected. An inner For Each…Next loop then checks to see if each item's value (i.e., each property's index number) is equal to any of the index numbers in PicArray. If so, the subroutine programmatically selects that item.
After all the appropriate items have been selected, the showsele subroutine uses the timer object again to call the showxl subroutine. The timer object creates a pause so that the selected items in the Extended File Property Items list box appear highlighted while the showxl subroutine process runs in the background. Without this timer object, you wouldn’t see the selected properties until the showxl subroutine processing was complete.
Code similar to that in callout D is used for the other three predefined categories. However, the code for the last category—You Select (element 5)—differs. When this option is selected in the User Selection Categories list box, the showsele subroutine makes sure that at least one option in the Extended File Property Items list box is selected. If no options are selected, the subroutine ends. If at least one option is selected, the subroutine uses the timer object to call the showxl subroutine.
The showxl Subroutine
The showxl subroutine connects to the target folder, retrieves the values for the selected file properties for every file in the folder, then writes those values in an Excel spreadsheet. As Listing 4 shows, the subroutine starts by canceling the timer object, after which it declares some constants and the On Error Resume Next statement. Next, the subroutine creates an instance of the Shell object and uses the Shell object's NameSpace method to obtain a Folder object representing the target folder, just like the Window_Onload subroutine did to obtain a Folder object representing the C folder. However, unlike Window_Onload, showxl will use the Folder object to get the file properties' values as well as their names.
Before using that Folder object, though, the showxl subroutine makes sure that the target folder exists by running the code in callout A. If the target folder doesn't exist, the subroutine resets the ProcessSelected button, displays an error message, then exits.
Next, as callout B shows, the showxl subroutine uses an If…Then…Else statement to check for two conditions: It checks to see if the Add Worksheets check box is cleared and if the FirstTime variable is set to True. As I mentioned previously, FirstTime is used to determine whether the Excel Application object needs to be instantiated in order to create a new spreadsheet. If either condition is met, the subroutine instantiates the Excel Application object, sets FirstTime to False, creates a new spreadsheet, and initializes the TabColor variable, which will be used later to set the colors of worksheet tabs.
The Else clause in callout B executes only when users generate consecutive file property lists. The code in this clause minimizes and hides Excel as a precautionary measure. It prevents users from clicking within an open spreadsheet while a new file property list is being generated; doing so would cause the process to write the new property data to unpredictable areas in the spreadsheet.
When using the HTA, some users might generate a file property list, close Excel even though the Add Worksheets check box is selected, then click the ProcessSelected button to generate another file property list, which results in an error. The code at callout C handles this error if it occurs. As you can see, the subroutine simply instantiates the Excel Application object again, creates a new workbook, and resets FirstTime to False.
The HTA colorizes worksheet tabs, selecting a new color for each new tab. To obtain a new color, the showxl subroutine increments the color number in the TabColor variable by 1. However, Excel has a limited number of colors. Trying to set a tab to a color number that doesn't exist results in an error. The code in callout D resets the starting color to number 1 if this error occurs.
At this point, the showxl subroutine is ready to retrieve the index numbers and names of the selected file properties and write them to the worksheet as headers. As callout E shows, the subroutine basically uses the same technique used by the Window_Onload subroutine to retrieve property names and index numbers. The showxl subroutine cycles through the each property in the Extended File Property Items list box and uses the GetDetailsOf method with a folder-items collection (i.e., a FolderItems object) to retrieve the index number and name of each selected file property. After the property's index number and name are combined in a string that follows the format number:name, the string is written to the first row in the worksheet as a header.
Next, the showxl subroutine retrieves the property details for each file in the target folder and writes that information to the worksheet. As callout F shows, the subroutine uses an outer For Each…Next loop to cycle through each file in the target folder and an inner For Each…Next loop to cycle through each property for that file. The subroutine again uses the GetDetailsOf method to retrieve the property information, except this time the method is used with a file (i.e., a FolderItem object) instead of a folder-items collection. Thus, the values assigned to the file properties are obtained. Keep in mind that the worksheet contains only the values for the selected properties.
The showxl subroutine ends by performing a few housekeeping tasks, such as such as freezing the top row of the spreadsheet (see callout G) and adding the name of the folder as a comment to cell A1 (see callout H). The latter feature is helpful if you’re unsure of the folder used to generate a list. You can simply hover your cursor over cell A1 to find out which folder was used.
More Details Are Just a Few Clicks Away
That pretty much covers all of the major components in GetExtendedFilePropertyDetails.hta. There are a few other processes I didn't cover, such as the code behind the Browse button. For information about how to implement browse functionality, you can check out the Microsoft article "Binding to a Folder by Using the Browse for Folder Dialog Box" (www.microsoft.com/technet/scriptcenter/guide/sas_fil_wqra.mspx?mfr=true). To see the browse code or any other code in GetExtendedFilePropertyDetails.hta, you just need to double-click the HTA file, right-click anywhere in the HTA's UI, then click the View Source option. Alternatively, you can open the HTA in a text editor such as Notepad.