Hey, Scripting Guy! How can I move the oldest file in the folder C:\Upload to C:\Download?

— DD

Hey, DD. Funny you should mention the word “oldest.” The Scripting Guy who writes this column recently attended a family reunion and, for the first time ever, came face-to-face with the whole idea of getting old. This Scripting Guy has fond memories of family reunions past, recalling the good times he had with his Scripting Siblings and Scripting Cousins. Of course, there was always one problem with family get-togethers: you had to try and steer clear of all the old people, the grownups who wanted to rub your head, tell you that they could remember back when you were in diapers, and insist on telling you a long, confusing story about your dad, a story that didn’t make a bit of sense. (It would only be sometime later when you’d find out they had gotten you mixed up with someone else, and the story was really about your grandpa or uncle or some obscure cousin you never heard of.) But as long as you stayed away from the grownups section, well, family reunions were fun.

At this latest family reunion, however, this Scripting Guy noticed something very disturbing: his siblings and cousins had turned into the old people! Now they were the ones who, instead of playing Wiffle ball or badminton, sat around and talked, swapping recipes and complaining about their knees. They shied away from the cake (too rich for my blood!) and the fried chicken, and spent most of their time drinking coffee, despite the fact that the temperature was right around 100 degrees. Somewhere along the line, all the Scripting Siblings and Cousins had gotten old!

Fortunately, that didn’t happen to the Scripting Guy. He’s as young and as vibrant as ever.

And because he is still as young as ever, this Scripting Guy has no qualms about writing a script that gets rid of something old; after all, what does being old have to do with him? For example, here’s a script that moves the oldest file in the folder C:\Upload to the folder C:\Download:

Set objFSo = CreateObject(“Scripting.FileSystemObject”)
Set objFolder = objFSO.GetFolder(“C:\Upload”)

Set colFiles = objFolder.Files
dtmOldestDate = Now
For Each objFile in colFiles
    If objFile.DateCreated < dtmOldestDate Then
        dtmOldestDate = objFile.DateCreated
        strOldestFile = objFile.Path
    End If
Next
objFSO.MoveFile strOldestFile, “C:\Download\”

Before we explain how this script works we should note that DD specifically talked about moving a file from one folder on the local computer (C:\Upload) to another folder on the same computer (C:\Download). Because of that we went ahead and used the FileSystemObject to tackle this chore. By using the FileSystemObject we were able to quickly piece together a script that not only works but is also fairly short and easy to understand. The only drawback to using the FileSystemObject? That pretty much limits the script to running on the local computer.

But what if you need to run this script against a remote computer? Well, at the end of this column we’ll show you a variation of this script that uses WMI; that enables the script to be used against remote machines. Of course, because of a few WMI eccentricities, that also makes the script a tad bit longer and a shade more complicated. We won’t take the time today to explain how the WMI script works; if you need that kind of clarification let us know and we’ll revisit this sometime in the near future.

No thank you, no potato salad for us. We’ll just have coffee.

As for the script that we will try to explain, our first thought upon reading this question was this: we’ll just write a script that grabs all the files in the folder, sorts them by file creation time, and then grabs the oldest file from that sorted list. Admittedly, that’s probably the way you should do it. Unfortunately, though, VBScript doesn’t have any sorting capabilities built into it; that means we’d have to write code that used something like a bubble sort or a disconnect recordset in order to get a sorted list of files. (For more information about these sorting techniques, see the Scripting Week 2 webcast Things the Scripting Guys Never Told You.)

Either of those techniques will work, of course. However, some of that coding can be a bit complicated, and to what end? After all, we don’t really need a sorted list of files; we just need to determine the oldest file in the folder. Therefore, we decided to take a less-elegant – but much easier – approach: instead of sorting all the files we’d simply look at the creation time for each file, one-by-one. We’d start with file 1 and keep a note of the file path; after all, seeing as how it’s the only file we’d looked at so far it would, by default, be the oldest file. We’d then look at the creation time for file 2; if that time was older than file 1’s creation time then we’d keep a note regarding the path to file 2 instead. This process would continue until we’d looked at all the files and determined the oldest one in the bunch.

Which will probably be the cranky old file telling the younger files to go play in the front yard so the grownups can talk.

To do all that we begin by creating an instance of Scripting.FileSystemObject; we then use this line of code to bind to the folder C:\Upload:

Set objFolder = objFSO.GetFolder(“C:\Upload”)

After we’ve made a connection to the Upload folder we can then use this line of code to retrieve a collection of all the files found in the folder:

Set colFiles = objFolder.Files

Now the fun begins. What we need to do is pick a baseline time to start with; if the creation time for the first file in the collection is older than the baseline time then we’ll call file 1 – for the time being – the oldest file in the collection. To get the starting baseline, we create a variable named dtmOldestDate and assign that variable the current date and time:

dtmOldestDate = Now

Once we’ve established this baseline time we set up a For Each loop to loop through all the files in the collection. For the first file in the collection we use this line of code to see if the value of the file’s DateCreated property is older than the baseline value dtmOldestDate:

If objFile.DateCreated < dtmOldestDate Then

Needless to say, it’s likely that this first file will have a creation time older than dtmOldestDate. But suppose it doesn’t; what then? No big deal; in that case we simply loop around and check the next file in the collection.

For the sake of argument, however, let’s say that dtmOldestDate has the value 7/25/2006 8:00 AM and the first file in the collection (a.txt) has a creation date of 7/24/2006 7:30 AM. In this case, the file a.txt truly is older than the baseline value. Therefore, we execute these two lines of code:

dtmOldestDate = objFile.DateCreated
strOldestFile = objFile.Path

We’re doing two things here. First, we’re assigning dtmOldestDate a new value, the value of a.txt’s DateCreated property. Why? Because this value now represents the oldest file in the folder. Second, we assign the path of the file (C:\Upload\a.txt) to a variable named strOldestFile. We use the variable dtmOldestDate to keep track of the oldest creation time; likewise, we use the variable strOldestFile to keep track of which file (by file path) is the oldest file in the folder.

And then, of course, we simply loop around and repeat this process with the next file in the collection. When all is said and done strOldestFile will contain the path to the oldest file in the folder. In turn, that means we can move the file using one line of code:

objFSO.MoveFile strOldestFile, “C:\Download\”

Nothing too complicated there: we simply call the MoveFile method, using strOldestFile to represent the file we want to move, and using C:\Download\ (note the \ at the end) to indicate the Folder we want to move the file to. There you have it: the oldest file in C:\Upload will now reside in C:\Download.

Now, where were we? Oh, that’s right: we were going to tell you a story about your mother, weren’t we? Well, you won’t believe this, but your mother was a bit of a rapscallion when she was younger; she was always getting into mischief. For example, one time she wrote a WMI script that could identify and move the oldest file in a folder. That’s right: your mother did that. The script looked something like this:

Const CONVERT_TO_LOCAL_TIME = True

Set dtmOldestDate = CreateObject(“WbemScripting.SWbemDateTime”)
dtmOldestDate.SetVarDate Now, CONVERT_TO_LOCAL_TIME
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set FileList = objWMIService.ExecQuery _
    (“ASSOCIATORS OF {Win32_Directory.Name=’C:\Upload’} Where ” _
        & “ResultClass = CIM_DataFile”)
For Each objFile In FileList
    If objFile.CreationDate < dtmOldestDate Then
        dtmOldestDate = objFile.CreationDate
        strOldestFile = objFile.Name
    End If
Next
strFile = Replace(strOldestFile, “\”, “\\”)
Set colFiles = objWMIService. _
    ExecQuery(“Select * from CIM_DataFile where Name = ‘” & strFile & “‘”)
For Each objFile in colFiles
    strOldName = LCase(objFile.Name)
    strNewName = Replace(strOldName, “c:\upload”, “c:\download”)
    objFile.Copy(strNewName)
    objFile.Delete
Next

Of course, because this script uses the SWbemDateTime object it runs on only Windows XP and Windows Server 2003. But that was just like your mother: all she ever talked about was Windows XP this and Windows Server 2003 that. For example, there was this one time – this was shortly after the war, mind you – when she came to our house – that was back when we lived on 38^th, in the blue house, you remember, your dad used to bring you by every now and then and you’d pick blackberries in the backyard – boy, you liked those blackberries although you didn’t like the thorns, there was this one time – this is such a cute story – there was this one time ….