Hey, Scripting Guy! I have a folder that has about 50 files in it. How can I delete all those files except the one most-recently created?
— GA

Hey, GA. You know, today would be a good day to review some of the items that have recently been in the news. (You know that life must be exceedingly dull for the Scripting Guy who writes this column when he resorts to talking about the news rather than relating some absolutely fascinating anecdote drawn from his own life.)

Let’s see … oh, here’s one. A few weeks ago the state of Washington passed a law mandating very strict requirements – and mandatory testing – on toys, the better to ensure that toys sold in the state do not contain toxic levels of lead, phthalates, or cadmium. (And yes, now that you mention it, Phthalates is Scripting Guy Peter Costantini’s middle name: Joseph Peter Phthalates Costantini.)

As you might expect, the passage of this law has engendered a considerable amount of controversy. Some people hail it as breakthrough legislation that will guarantee the safety of our children and our children’s children; others claim that it will destroy the toy industry as we know it today. (Which, considering the lameness of many of the toys sold today, might not be such a bad idea.) As a Microsoft employee, the Scripting Guy who writes this column has no opinion on this issue one way or the other.

Which, considering the lameness of most of his opinions, might not be such a bad idea.

Far more interesting than the law itself, however, was the hype and hysteria that accompanied its trip through the legislature. Proponents of the law were convinced that the state’s children would all drop dead tomorrow if the law failed to pass. (Which, considering the lameness – no, hey, just joking there.) Opponents of the law were convinced that the legislation would ban any toy that could pose even the remotest hazard to a child. What if you had, say, a soft foam-rubber ball, a ball made entirely out of non-toxic, fully edible material? Harmless, right? Hardly. After all, a kid could trade that “harmless” foam-rubber ball for, say, a boomerang, throw that boomerang, and then conk some other poor kid on the head. Consequently, harmless foam-rubber balls would have to be banned, too.

Which, of course, was silly. After all, what kid is going to trade a boomerang for some dumb foam-rubber ball?

One the bright side, however, the state of Washington has yet to pass any legislation restricting the use of system administration scripts, not even scripts that can delete all the files in a folder except for the most-recently created file. Which is a good thing; after all, this column would make even less sense than usual if it didn’t include the following chunk of code:

strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Logs'} Where " _
        & "ResultClass = CIM_DataFile")

dtmCurrentDate = #1/1/1980 8:00 AM#

For Each objFile In colFiles
    dtmFileDate = WMIDateStringToDate(objFile.CreationDate)
    If dtmFileDate > dtmCurrentDate Then   
        If strLatestFile <> "" Then
            strLatestFile = Replace(strLatestFile, "\", "\\")
            Set colOldFiles = objWMIService. _
                ExecQuery("Select * From CIM_DataFile Where Name = '" & strLatestFile & "'")

            For Each objOldFile in colOldFiles
                objOldFile.Delete
            Next
        End If

        strLatestFile = objFile.Name
        dtmCurrentDate = dtmFileDate
    Else
        objFile.Delete
    End If
Next

Function WMIDateStringToDate(dtmCreationDate)
    WMIDateStringToDate = CDate(Mid(dtmCreationDate, 5, 2) & "/" & _
        Mid(dtmCreationDate, 7, 2) & "/" & Left(dtmCreationDate, 4) _
            & " " & Mid (dtmCreationDate, 9, 2) & ":" & _
                Mid(dtmCreationDate, 11, 2) & ":" & Mid(dtmCreationDate, _
                    13, 2))
End Function

As you can see, we start things out by connecting to the WMI service on the local computer. Can we use this exact same script to delete files from a remote computer? Of course we can; to delete files from a remote computer, just assign the name of that computer to the variable strComputer:

strComputer = "atl-fs-001"

If only everything in life was that easy, huh?

After we connect to the WMI service we then use this weird-looking Associators Of query to return a collection of all the files in the folder C:\Logs:

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Logs'} Where " _
        & "ResultClass = CIM_DataFile")

Don’t worry too much about the syntax here; just note that we’re asking for all the files (that is, all the instances of the CIM_DataFile class) that happen to reside in (are associated with) the directory named C:\Logs.

Now, in a perfect world, we’d ask WMI to return this collection of files sorted by creation date; that would make it very, very easy to delete everything except the newest file in the folder. Unfortunately, though, this is not a perfect world.

Like we said, WMI can’t sort our collection of files; instead, it’s simply going to return all the files in alphabetical order. If we wanted to, we could come up with an alternate way to sort the files; for example, we could stash all the files in a disconnected recordset and then sort that recordset. But that sounded like too much work, especially for the Scripting Guys. Therefore, we decided to do this the old-fashioned way: we’ll simply look at each file in the collection, and “manually” identify the latest file to be created. We’ll then hang on to that file, and delete everything else.

Because if it’s not the latest and greatest, well, then who really cares, right?

So how do we determine which file is the most-recently created? Well, for starters we pick an arbitrary date to use as a file creation baseline; in this case, we chose January 1, 1980 at 8:00 AM (coincidentally, the same day that the Scripting Editor celebrated her 40^th birthday):

dtmCurrentDate = #1/1/1980 8:00 AM#

What do we need a baseline date for? Well, like we said, we’re going to look at the creation date for each and every file, checking to see if this creation date is newer than, well, than what? We have to start somewhere, so we decided to start with January 1, 1980; we’re assuming that you don’t have any files that were created before that date. If you do have files that were created before January 1, 1980 then simply assign dtmCurrentDate a different starting point.

Like, say, October 14, 1857, the same day that the Scripting Guy who writes this column first told people to stay tuned for part 3 of the HTA tutorial.

After we establish our baseline date we set up a For Each loop to loop through each and every file in the collection. Inside that loop the first thing we do is take the value of the file’s CreationDate property and pass it to a function we’ve named WMIDateStringToDate:

dtmFileDate = WMIDateStringToDate(objFile.CreationDate)

What’s the point of that? Well, as most of you know by now, WMI uses the UTC (Universal Time Coordinate) format when working with dates and times; that means that the creation date for a file is going to look something like this:

20080418125447.687500-420

Admittedly, that looks like pure gibberish. As it turns out, however, there’s method to the madness: it’s possible to parse out individual date-time pieces from a UTC value. For example, the first four digits – 2008 – represent the year; the next two digits – 04 – represent the month, and so on. In fact, the value 20080418125447.687500-420 can be broken down like this:

See? It’s not gibberish after all. (But you already knew that. After all, frequent readers of this column should be very good at spotting real gibberish by now.)

The function WMIDateStringToDate simply takes a UTC value and converts it to a regular old-date time value, like this one:

4/18/2008 12:54:57 PM

After we’ve converted the CreationDate value we check to see if the creation date for the first file in our collection is greater than (i.e., newer than) our baseline date:

If dtmFileDate > dtmCurrentDate Then

Most likely the creation date is newer than the baseline date. In that case, we then check to make sure that the variable strLatestFile has a value:

If strLatestFile <> "" Then

Why do we do that? Well, we’re going to use the variable strLatestFile to hold the path to the most-recently created file; any time we find a newer file we’ll use WMI’s Delete method to delete the file that used to be the most-recently created. And how do we know which file to delete? That’s right: we use the path stored in strLatestFile

Of course, the first time through the loop strLatestFile won’t have a value; that’s because we haven’t assigned it a value yet. Because of that, our script will blow up if we try to delete the file: we can’t delete a file that doesn’t exist. Our If-Then statement is simply a safeguard to ensure that the script doesn’t blow up.

Let’s assume that strLatestFile doesn’t have a value yet. In that case, we execute these two lines of code:

strLatestFile = objFile.Name
dtmCurrentDate = dtmFileDate

As you can see, in line 1 we’re – at last! – assigning strLatestFile a value: we’re giving it the Name (file path) of the file we’re currently looking at. In addition, we’re assigning the file creation date (via the variable dtmFileDate) to the variable dtmCurrentDate. What does that mean? That means that our baseline date is no longer 1/1/1980; now – in keeping with our example – our baseline date, the date which all subsequent creation dates will be compared against, is 4/18/2008.

See how that works? Each time we find a more-recently created file we update the variables strLatestFile and dtmCurrentDate with the new file path and file creation date.

Now, what if strLatestFile does have a value? Well, in that case we execute the following block of code:

strLatestFile = Replace(strLatestFile, "\", "\\")
Set colOldFiles = objWMIService. _
    ExecQuery("Select * From CIM_DataFile Where Name = '" & strLatestFile & "'")

For Each objOldFile in colOldFiles
    objOldFile.Delete
Next

In the first line we’re taking the value of strLatestFile and replacing any instances of the \ character with two \\ characters. That means a file path like C:\Logs\Test.log will end up looking like this:

C:\\Logs\\Test.log

Why do we do that? Well, in our very next line of code we’re going to use WMI’s ExecQuery method to bind to the file C:\Logs\Test.log. Because we need to use the file path (the Name property) in our query, and because the \ is a reserved character in WMI, that means we have to “escape” each \ in the file path. And how do you escape a character in WMI? You got it: you preface each instance of the character with a \.

As we hinted at above, in line 2 we use the ExecQuery method to return a collection of all the files with the specified file path. Because file paths must be unique on a computer there will be only one such file in our collection; nevertheless, a collection is a collection, regardless of the number of items in that collection. Therefore, we next set up a For Each loop to loop through the items in our new collection:

For Each objOldFile in colOldFiles

And what do we do inside this loop? One thing and one thing only: we delete the file that used to be the most-recently created file in the folder. Once we’ve done that we assign new values to strLatestFile and dtmCurrentDate, then go back to the top of the loop and repeat the process with the next file in the collection.

Of course, there’s one other possibility we’ve failed to cover: what if the file we’re looking at is actually older than the most-recently created file? In that case our task is much easier: we simply delete the file in question, then go back to the top of the loop and try again.

Admittedly, it’s a bit confusing. But if you sketch it out – taking care to update the values of strLatestFile and dtmCurrentDate as you do so—you should see how it all works.

We hope that helps, GA. Incidentally, we know that today’s column is a little longer than usual, but whatever you do, please do not print out the article. After all, that printout could be made into a paper airplane, someone could throw it, and some poor kid could get his eye poked out. And then system administration scripting would be banned forever, at least in the state of Washington.