Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I change file names using a file mask? You know, like when you run the command-line command ren ??x???p?.* ?a????bc.x??.
-- AM

SpacerHey, Scripting Guy! AnswerScript Center

Hey, AM. Well, the big news out of Seattle is that the Mariners have fired their general manager, Bill Bavasi; that’s to be expected when you spend $117 million on salaries and the team you put together is – by a comfortable margin – the worst in baseball. Bill Bavasi seems like a nice-enough guy and he no doubt knows a lot about baseball; for some reason, however, everything he tried ended up going down in flames. For example, two years ago Jeff Weaver was a post-season hero for the St. Louis Cardinals. Bavasi and the Mariners promptly signed Weaver to an $8.35 million-a-year contract, and the pitcher responded by winning 8 whole games.

This past year, believing that Carlos Silva had finally reached his potential, the Mariners signed him to a 4-year, $48 million contract. Currently Carlos has a record of 3-8, and, in his most memorable outing (one witnessed in person by the Scripting Guy who writes this column), he managed to get two Detroit Tigers out before having to leave in the first inning. (Why did he have to leave in the first inning? Beats us, although the fact that he’d already given up 7 runs on 7 hits might have had something to do with it.)

Interestingly enough, we know just how the Mariners feel: after all, the Scripting Guys are paying the Scripting Editor $12 million a year as well. And while she’s doing a decent-enough job, well, to be honest, we’re not convinced she’s worth that much money. But when we hired her she told us that $12 million was the going rate for editors these days, and she obviously knows more about that than we do.

Note. Of course we checked up on this; we wouldn’t just take her word on something like that. As it turns out, the average editor makes around $50,000 a year. However, as the Scripting Editor was quick to point out, she’s anything but an average editor. And, apparently, non-average editors tend to make around $12 million a year.

So much for the biggest news out of Seattle. What’s the second-biggest news story circulating through the Seattle area these days? That would have to be the new Scripting Guys script that can apply a “file mask” when renaming files. You know, this script:

strComputer = "."

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

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

For Each objFile In colFileList
    strName = objFile.FileName
    strExtension = objFile.Extension

    If Mid(strName, 3, 1) = "x" and Mid(strName, 7, 1) = "p" Then
        strNewName = ""

        For i = 1 to Len(strName)
            Select Case i
                Case 2 strNewName = strNewName & "a"
                Case 7 strNewName = strNewName & "b"
                Case 8 strNewName = strNewName & "c"
                Case Else strNewName = strNewName & Mid(strName, i, 1)
             End Select
        Next

        strNewExtension = "x" & Right(strExtension, 2)
        strNewPath = "C:\Scripts\" & strNewName & "." & strNewExtension

        errResult = objFile.Rename(strNewPath)
    End If
Next

Let’s see if we can figure out what’s going on here. If you haven’t used DOS in awhile, we should begin by pointing out that the command ren ??x???p?.* ?a????bc.x?? simply means this:

Look for a file where the third character is an x and the seventh character is a p. We don’t care what the other characters are; in fact, the question mark (?) is just a wildcard placeholder for “any character.” We also don’t care what the file extension is, or how many characters that file extension might contain; that’s why we use the asterisk (*) to represent the file extension.

If we find a file meeting the above criteria we want to rename that file; that’s what the ren command is for. And what new name do we want to give this file? Well, we want to leave characters 1, 3, 4, 5, and 6 in the file name exactly as they are; in addition, we want to make the second character in the name an a, the seventh character a b, and the eighth character a c. Oh, and we want the first character in the file extension to be an x, leaving the second and third characters in the extension as-is.

Note. Technically there’s one more thing that command means: it will look for files that have exactly eight characters in the file name. Our script doesn’t check for the number of characters in the file name, but if you want to do that feel free. Why didn’t we include that? Well, it would have added some complexity to the script we didn’t really think was necessary. That and the fact that we were afraid of going over the Scripting Editor’s weekly word count: $12 million only pays for so much, you know.

So what does all of that mean? Good question. Suppose we have a file name like this one, one that meets our naming criteria:

12x456p7.abc

After we run our script, this file should now be named this:

1ax456bc.xbc

Crazy!

Note. Well, maybe a name like that isn’t all that crazy, at least not in this day and age. For example, when he was at the University of Washington the Scripting Guy who writes this column worked with a Cyndee, a Syndee, a Cyndi and a Sinthia. But he never once worked with a Cindy or a Cynthia.

Lucky for them, huh?

So how do we actually perform this wild and crazy file renaming? Well, to begin with, we connect to the WMI service on the local computer. And why are we using WMI to rename our files? That’s right: because that enables us to use this same script to rename files on a remote computer. In fact, to do that all we need to do is assign the name of that remote computer to the variable strComputer, like so:

strComputer = "atl-fs-001"

Note. Say, does this mean you guys really have been paying attention to us all these years? No, don’t bother answering; we’ll just pretend you have.

Our next step is to use the following Associators Of query to retrieve a collection of all the files found in the folder C:\Scripts:

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

That’s a good question: instead of returning a collection of all the files in the folder C:\Scripts couldn’t we write a query that returned just the files that met our criteria? And the answer to that is this: probably. However, we were concerned that such a query (assuming we could write one) would be needlessly-complicated. On top of that, taking that approach would prevent anyone who is running Windows 2000 from using the script; that’s because you can’t use WMI wildcard queries on any version of Windows prior to Windows XP. With all that in mind we decided to retrieve all the files, then examine each one individually, checking to see if the name meets our criteria.

In fact, that’s the very thing we do next: we set up a For Next loop to loop through the collection of files. Inside that loop the first thing we do is grab the value of the FileName and Extension properties and assign them to the variables strName and strExtension:

strName = objFile.FileName
strExtension = objFile.Extension

From there it’s time to see if the file name meets our selection criteria. We could have used a regular expression to determine whether or not each file name passes the test; however, we decided that, because we had only two simple criteria that had to be met (third letter is an x, seventh letter is a p) we could do this much easier using an If Then statement:

If Mid(strName, 3, 1) = "x" and Mid(strName, 7, 1) = "p" Then

See? We told you this was easy. All we’re doing is using the Mid function to check two things: 1) is the third character in the variable strName equal to x? and, 2) is the seventh character in strName equal to p? If either of those checks are False then our file name doesn’t meet our test; consequently, we go back to the beginning of the loop and try again with the next file in the collection. But what if both checks come back True? Well, in that case, we have some file renamin’ to do.

To that end, the first thing we do is set a variable named strNewName to an empty string; as the name implies, we’re going to use this variable to hold our new file name:

strNewName = ""

Next we set up a For Next loop that starts at 1 (representing the first character in strName) and continues looping until we reach the last character in strName, something we can determine by using the Len function:

For i = 1 to Len(strName)

The first thing we do inside this For Next loop is set up a Select Case statement that takes action based on the value of the counter variable i. What action is Select Case going to take? Well, needless to say, that’s going to depend on the value of i. For example, the first time through the loop i will be equal to 1. There’s nothing special about the first character in the file name; as you might recall, we want to leave the first character exactly as it is. Therefore, we execute the Case Else portion of our Select Case statement:

Case Else strNewName = strNewName & Mid(strName, i, 1)

What are we doing here? You got it: we’re simply appending the current character in the file name (e.g., character i) to the variable strNewName. If our file name is 12x456p7.abc then that means that, once we complete our first trip through the loop, strNewName will be equal to this:

1

Things get a little more interesting the second time through the loop. Why? Because we don’t want to use the second character in the file name; instead, we want to replace that character (whatever it is) with the letter a. That’s why we execute this line of code:

Case 2 strNewName = strNewName & "a"

In other words if our counter variable is equal to 2 then we want to append the letter a to strNewName, discarding whichever character is currently the second character in the file name.

By the time we finish the entire loop strNewName should look like this:

1ax456bc

Our next chore is to modify the file extension. As it turns out, that’s easy; after all, the new file extension is simply the letter x followed by the second and third characters in the existing file extension. That means that we can create a new file extension using a single line of code:

strNewExtension = "x" & Right(strExtension, 2)

Note. As a matter of fact, we could have created a new file name using a single line of code; we used the Select Case statement simply because we thought it would be easier for people to follow the logic of what we were doing. (Well, assuming that there is some logic to anything that the Scripting Guys do.) If you don’t like typing, and if you simply hate Select Case statements, well, this line of code will do the trick:

strNewName = Mid(strName, 1, 1) & "a" & Mid(strName, 3, 1) & Mid(strName, 4, 1) & _
Mid(strName, 5, 1) & Mid(strName, 6, 1) & Mid(strName, 1, 1) & "bc"

All we have to do now is construct a complete file path; that’s because WMI’s Rename method requires us to pass a full file path as opposed to just a new file name. We’ll let you figure out for yourself what this line of code is for (hint: it’s for constructing a complete file path):

strNewPath = "C:\Scripts\" & strNewName & "." & strNewExtension

And once that’s done, renaming the file is a simple as calling the Rename method:

errResult = objFile.Rename(strNewPath)

From there it’s back to the top of the loop, where we repeat the process with the next file in the collection. When all is said and done – well, this time we will let you see for yourself what happens when all is said and done. (Hint: all the files meeting the criteria will be renamed using our new naming convention.)

And that takes care of the second-biggest news story in the Seattle area. By the way, we should clarify that we don’t know for sure that the Scripting Editor makes $12 million a year; Microsoft doesn’t like employees telling other people how much they make each year. All that we do know is that the Scripting Editor drives a Lexus. Granted, it’s a Lexus that – for some reason – smells like turpentine. Oh, and the Check Engine light is currently on, and the trunk leaks water like a sieve. But hey, it’s still a Lexus, and even a luxury car like that is bound to have an occasional problem or two, right?

Note. So is the Scripting Editor using her car to smuggle bootleg turpentine across the Washington-Oregon border? That’s the rumor, and we tend to believe there might be something to that rumor. After all, you can’t expect someone with a lifestyle like the Scripting Editor’s to get by on a measly $12 million a year, can you?