Hey, Scripting Guy! I Using VBScript, how can I identify the 20 most-recently modified files in a folder?
— TW

Hey, TW. You know, the Scripting Guy who writes this column was just reading – thanks to the magic of Spam email – about a new voice mail service. With this service you don’t have to listen to all those annoying voice mails; instead, whenever someone leaves you a voice mail a piece of software “listens” to that voice mail and then sends you an email or text message summarizing the voice mail. This is considered “revolutionary,” although, to be honest, the Scripting Guy who writes this column isn’t exactly sure why it’s “revolutionary” to receive a text message rather than a voice mail, but he’ll take everyone’s word for it.

At any rate, for awhile now we’ve had software that can read your email, call you on the phone, and then read that email back to you. Now we have software that can take a voice mail and turn it into email. Which leads to an obvious question. Suppose the first piece of software reads an email, calls someone’s cell phone, and leaves that email as a voice mail. Now, what do you think will happen if the second piece of software listens to the voice mail, converts it to email, and then emails that same message back to the first piece of software (which, of course, will then call you and try to read the email back to you)? Would life as we know it come to a sudden end?

Well, let’s hope so; after all, the past month hasn’t exactly been seashells and balloons for the Scripting Guys.

Of course, if cell phones can send email and email can make calls to cell phones you also have to wonder why these devices even need human beings. Does this mean that the human race is now doomed to extinction? Well, let’s hope so; like we said, the past month hasn’t exactly been seashells and balloons for the Scripting Guys.

Besides, fewer people means fewer Christmas cards that the Scripting Guy who writes this column will have to send out this year.

As far as we know, however, the human race is not yet extinct. That means two things: 1) the Scripting Guy who writes this column needs to hang onto his Christmas card list a little longer; and, 2) he also needs to come up with a script that can identify the 20 most-recently modified files in a folder. Fortunately, a script like that is the very thing that the Scripting Guy who writes this column sent out as a Christmas card this past year. Happy holidays, everyone:

Const adVarChar = 200
Const MaxCharacters = 255
Const adFldIsNullable = 32

strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colFiles = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Scripts’} Where ” _
        & “ResultClass = CIM_DataFile”)
Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “FileName”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Fields.Append “ModifiedDate”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Open
For Each objFile In colFiles
    DataList.AddNew
    DataList(“FileName”) = objFile.Name
    DataList(“ModifiedDate”) = objFile.LastModified
    DataList.Update
Next
DataList.Sort = “ModifiedDate DESC”
For i = 1 to 20
    dtmWMIDate = DataList.Fields.Item(“ModifiedDate”)
    dtmConvertedDate = CDate(Mid(dtmWMIDate, 5, 2) & “/” & Mid(dtmWMIDate, 7, 2) & _
        “/” & Left(dtmWMIDate, 4) & ” ” & Mid (dtmWMIDate, 9, 2) & “:” & _
            Mid(dtmWMIDate, 11, 2) & “:” & Mid(dtmWMIDate, 13, 2))
    Wscript.Echo DataList.Fields.Item(“FileName”) & ” — ” & dtmConvertedDate
    DataList.MoveNext
Next

As you can see, our script starts off by defining three constants, constants that will be required to create a disconnected recordset.

Anyway, the constant adVarChar will be used to create a pair of fields with a Variant data type; the constant MaxCharacters will set the maximum number of characters that can be stored in that field to 255. As for adFldIsNullable, well, that tells the script that it’s OK to have a record with null values in it. We won’t actually have any records with null values (unless we somehow encounter a file that has neither a file path nor a last modification date), but because adFldIsNullable is a useful thing to know about we decided to toss it in, just for the heck of it.

After defining our constants we next connect to the WMI service on the local computer. And yes, as a matter of fact we can run this script against a remote computer; to do that, just assign the name of the remote computer to the variable strComputer. You know, like this:

strComputer = “atl-fs-001”

Once we’re connected to the WMI service we can use the following query to retrieve a collection of all the files (instances of the CIM_DataFile class) found in the folder C:\Scripts:

Set colFiles = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Scripts’} Where ” _
        & “ResultClass = CIM_DataFile”)

Of course, that leads to another obvious question: now that we have this collection of files what are we supposed to do with it? Well, for the moment at least, we’re not going to do anything with it; instead, we’re going to use this block of code to create a disconnected recordset (a database table that exists only in memory, and isn’t tied to an actual database file):

Set DataList = CreateObject(“ADOR.Recordset”)
DataList.Fields.Append “FileName”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Fields.Append “ModifiedDate”, adVarChar, MaxCharacters, adFldIsNullable
DataList.Open

As you can see, there really isn’t much to this block of code. In the first line we create an instance of the ADOR.Recordset object, the object used to create a disconnected recordset. In line 2, we create a variant field (with a maximum of 255 characters) named FileName; in line 3, we create a similar field named ModifiedDate. Finally, in line 4, we call the Open method to open the disconnected recordset and ready it for data entry.

Whew. And to think we said that there wasn’t much to that block of code!

Now, back to our collection of files. (Remember, the collection of all the files found in the folder C:\Scripts? The collection we just retrieved using the Associators Of query? Right, right: that collection of files.)

What we’re going to do now is loop through this collection, adding the path and last modified date for each file to our disconnected recordset. Why are we adding this information to a disconnected recordset? Well, we need to determine the 20 most-recently modified files; the only way we know of to do that is to grab all the files, sort them by the LastModified property, and then report back the first 20 items in the list. As far as the Scripting Guys are concerned, the easiest way to store all these files and then sort them by modification date is to stash all the information in a disconnected recordset.

Which is just exactly what we do inside our For Each loop. To begin with, we call the AddNew method to create a new, blank record for our disconnected recordset; we then use these two lines of code to add the value of the file’s Name and LastModified properties to our new record:

DataList(“FileName”) = objFile.Name
DataList(“ModifiedDate”) = objFile.LastModified

As soon as we’ve assigned values to the two fields (FileName and ModifiedDate) we call the Update method and add the record to the disconnected recordset:

DataList.Update

And then it’s back to the top of the loop, where we repeat the process with the next file in the collection.

Before we go any further let’s talk about what these records actually look like. Better yet, and in keeping with the spirit of the times, let’s show you what these records look like:

The FileName field doesn’t require much explanation; it’s simply the path to the file in question. As for the ModifiedDate, well, strange as it might seem, that is the date that the file was last modified. WMI, as many of you know by now, doesn’t use a standard date-time format; instead, it uses the UTC (Universal Time Coordinate) format. With the UTC format, date-time values are broken down like this:

Admittedly, that’s an odd way to format dates and times. On the bright side, however, it turns out to be a good way to sort dates and times. Consequently, we can enter these UTC values into our disconnected recordset as-is, then sort by ModifiedDate to determine the 20 files that were most-recently modified.

In fact, that’s what we do the moment we exit our For Each loop:

DataList.Sort = “ModifiedDate DESC”

All we’re doing here is calling the Sort method followed by the field we want to sort on (ModifiedDate) and the DESC parameter, which tells the script to sort the recordset in descending order; that’s how we make sure that the most-recently modified file winds up at the top of the list.

After the files are sorted we can report back the 20 most-recently modified files by setting up a For Next loop that runs from 1 to 20:

For i = 1 to 20

Inside this For Next loop we start off by storing the value of the ModifiedDate field in a variable named dtmWMIDate; we then use this block of code to convert the UTC date to a real date-time value:

dtmConvertedDate = CDate(Mid(dtmWMIDate, 5, 2) & “/” & Mid(dtmWMIDate, 7, 2) & _
    “/” & Left(dtmWMIDate, 4) & ” ” & Mid (dtmWMIDate, 9, 2) & “:” & _
        Mid(dtmWMIDate, 11, 2) & “:” & Mid(dtmWMIDate, 13, 2))

We won’t explain all that craziness in any detail today; suffice to say you can find a more complete explanation in the Microsoft Windows 2000 Scripting Guide. For now, we’ll merely note that this block of code simply picks out and rearranges the individual date-time pieces found in the UTC value. In other words, it takes a UTC value like 20080430205414.000000-420 and magically transforms it into a date-time value like this: 4/30/2008 8:54:14 PM.

Once the conversion has been made we can then echo back the file path and the last modified date:

Wscript.Echo DataList.Fields.Item(“FileName”) & ” — ” & dtmConvertedDate

And once that’s done we call the MoveNext method to move to the next record in the recordset. By the time we exit the For Next loop we should see something similar to this onscreen:

c:\scripts\test.txt — 4/30/2008 8:54:14 PM
c:\scripts\names.txt — 4/30/2008 8:53:34 PM
c:\scripts\subscriber.txt — 4/30/2008 1:09:16 PM
c:\scripts\hey0501.doc — 4/30/2008 10:02:27 AM
c:\scripts\test.xls — 4/30/2008 10:00:04 AM
c:\scripts\enumerator.txt — 4/29/2008 1:11:14 PM
c:\scripts\q1.vbs — 4/28/2008 10:26:16 PM
c:\scripts\test.ps1 — 4/28/2008 9:46:11 PM
c:\scripts\test 1.ps1 — 4/26/2008 10:36:45 PM
c:\scripts\test.vbs — 4/23/2008 9:58:43 AM
c:\scripts\namechange.bat — 4/23/2008 9:21:44 AM
c:\scripts\test.doc — 4/22/2008 9:50:53 AM
c:\scripts\remove_access_rule_all.ps1 — 4/18/2008 1:06:03 PM
c:\scripts\remove_access_rule.ps1 — 4/18/2008 1:03:26 PM
c:\scripts\add_access_rule.ps1 — 4/18/2008 12:54:47 PM
c:\scripts\z.ps1 — 4/17/2008 3:00:02 PM
c:\scripts\fv.ps1 — 4/9/2008 10:22:06 AM
c:\scripts\temp.txt — 4/4/2008 10:50:55 AM
c:\scripts\get_all.vbs — 4/4/2008 7:27:53 AM
c:\scripts\os_info.vbs — 4/3/2008 5:55:16 PM

In other words, we have the 20 files from the folder C:\Scripts that were most-recently modified. It’s everything you’ve ever wanted!

Well, OK. But it is information that’s useful to TW.

Regardless, that’s all we have time for today. Before we go, however, and at the risk of tooting our own horn, we’d like to point out that the Scripting Guys – as usual – are way ahead of the rest of the world. After all, you don’t have to buy any special software or subscribe to a special service in order to receive Hey, Scripting Guy! in text format; far-sighted visionaries that we are, we already supply Hey, Scripting Guy! in text format.

And yes, that’s exactly why the Scripting Guys are where they are today. And deservedly so.