Hey, Scripting Guy! Question

Hey, Scripting Guy (and the $12 million-a-year Scripting Editor)! Is there a way to mass-rename all the files in a folder, changing the letter case as needed?

-- MV

SpacerHey, Scripting Guy! AnswerScript Center

Hey, MV. To begin with, we should mention that we know that you actually asked two questions in your email. We’re going to deal with the first question – mass-renaming of all the files in a folder, changing the letter case as needed – today, then address your other question sometime in the next week or so. Originally the plan was to answer both questions today: the Scripting Guy who writes this column would address the mass-renaming issue, and the Scripting Editor would tackle your other question (a question that has to do with changing the tags on MP3 files). Unfortunately, though, the $12 million-a-year Scripting Editor is not available today; she had to fly down to California to buy a pair of sunglasses.

And yes, we know: that does sound a bit extravagant, flying all the way to California just to pick up a pair of sunglasses. To her credit, though, the Scripting Editor originally ran down to Walgreen’s, hoping to pick up a pair of sunglasses off the rack. Unfortunately, Walgreen’s didn’t have what she was looking for; she needed sunglasses in 18K Yellow Gold, with 132 hand-set, full-cut diamonds, as well as a 2.0 carat Canary yellow diamond. And, of course, ivory buffalo horn temples and brown lenses with gold mirror. Sadly, she couldn’t get ivory buffalo horn temples at Walgreen’s; all their temples are made out of Alaskan walrus tusk. That left her no choice but to zip down to Studio City, CA and pick up a pair of sunglasses down there.

Note. OK, so maybe she didn’t have to go down to California herself; she probably could have ordered them online. Which reminds us: if anyone out there is interested in being just like the Scripting Editor – and we have to assume that everyone is interested in being just like the Scripting Editor – these sunglasses are currently on sale for just $65,000. That means you can get both the sunglasses and a $2,600 pen-and-pencil set for less than $70,000!

Who said you can’t find bargains anymore? You just have to be willing to look for them.

At any rate, once the Scripting Editor returns from her skin tightening (gift cards available!) she’ll no doubt address MV’s question about MP3 files. In the meantime, the Scripting Guy who writes this column doesn’t need to have his skin tightened. (As far as he knows, anyway; after all, it hasn’t fallen off or anything, at least not yet.) Therefore, he has time to go ahead and tackle MV’s first question: is there a way to mass-rename all the files in a folder, changing the letter case as needed?

The answer, of course, is yes, depending on what you mean by “changing the letter case as needed.” Let’s start with a very simple example. Suppose you have files (in the folder C:\Test) that look like this:

This is A file.Txt
THIS IS ANOTHER FILE.TXT
this is the third file.TXT
THIS IS THE LAST FILE.txt
Needless to say, this isn’t the prettiest directory listing we’ve ever seen. (Especially when compared to the Madera bathtub, hand-crafted from natural solid woods and priced to sell
at just $30,000 apiece. Pick up one for each member of the family; after all, you can never have too many hand-crafted wooden bathtubs.) Because of that, we might want to convert
all the letters in both the file name and the file extension to their lowercase equivalent, giving us a directory listing that looks like this:
this is a file.txt
this is another file.txt
this is the third file.txt
this is the last file.txt
So how are we going to do that? Why, by running the following script, of course:
strComputer = "."

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

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

For Each objFile In colFileList
    strName = LCase(objFile.FileName)
    strExtension = LCase(objFile.Extension)

    strNewName = objFile.Drive & objFile.Path & strName & "." & strExtension
    objFile.Rename(strNewName)
Next
Let’s see what we have here. We kick things off by connecting to the WMI service on the local computer. And yes, we can run this script – and thus rename files – on a remote computer;
all we have to do is assign the name of that remote machine to the variable strComputer:
strComputer = "atl-fs-001"

After connecting to the WMI service we then use this line of code to return a collection of all the files found in the folder C:\Test:

Set colFileList = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _
        & "ResultClass = CIM_DataFile")
And now it’s time to have some fun. (As much fun as an $18,000 stainless steel pool table? Well, no. But close. Very close.)

To begin with, we set up a For Each loop that enables us to loop through all the files in the collection. Inside that loop the first thing we do is execute these two lines of code:

strName = LCase(objFile.FileName)
strExtension = LCase(objFile.Extension)
In the first line we’re assigning a value to the variable strName. What value are we assigning? Well, we’re taking the value of the FileName property and using the LCase function to
convert all the characters in that file name to their lowercase equivalent. In other words, suppose the name of our first file is This is A file. The LCase function will convert
that file name to the following:
this is a file

In the second line of code we use the same approach to convert all the letters in the file extension to their lowercase equivalent.

Believe it or not, at this point we’re almost done. When you rename files using WMI you must supply the Rename method with a complete file path; this line of code helps us put together
a complete file path, substituting the variables strName and strExtension for the file name and the file extension:

strNewName = objFile.Drive & objFile.Path & strName & "." & strExtension

All that’s left now is to call the Rename method and rename the first file:

objFile.Rename(strNewName)

And then it’s back to the top of the list, where we repeat the process with the next file in the collection. When all is said and done, the folder C:\Test should now look like this:

this is a file.txt
this is another file.txt
this is the third file.txt
this is the last file.txt
Not bad, eh? And, of course, you could easily convert all the letters in the file names to their uppercase equivalent. To do that, simply use the UCase function rather than the LCase 
function.

Well that was easy, wasn’t it? And fast, too: we’ve still got time to do something else today. Enough time to spend a night at the Atlantis Bridge Suite in the Atlantis Hotel in the Bahamas
($25,000 per night)? Sadly, no. (Besides, the Scripting Editor says the place is overrated, although she does like the fact that the kitchen has a separate entrance, meaning that she never
actually has to see the cook or butler.) However, we do have time to tackle a more ambitious script, one that applies “title casing” to our file names. In other words, a script that can turn our
file names into these:

This Is A File.txt
This Is Another File.txt
This Is The Third File.txt
This Is The Last File.txt
How are we going to do that? Good question; maybe we should try running this script and see what happens:
strComputer = "."

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

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

For Each objFile In colFileList
    strName = objFile.FileName
    strExtension = LCase(objFile.Extension)

    arrWords = Split(strName, " ")

    For i = 0 to UBound(arrWords)
        intLength = Len(arrWords(i))
        strFirstLetter = UCase(Left(arrWords(i), 1))
        strOtherLetters = LCase(Right(arrWords(i), intLength - 1))
        strName = strFirstLetter & strOtherLetters
        arrWords(i) = strName
    Next

    strNewName = Join(arrWords, " ")
    strNewName = objFile.Drive & objFile.Path & strNewName & "." & strExtension
    objFile.Rename(strNewName)
Next
So how does this script work? Well, in many ways it works the same as the first script we showed you: it connects to the WMI service, retrieves a collection of all the files 
in the folder C:\Test, and then assigns the file name and extension to a pair of variables:
strName = objFile.FileName
strExtension = LCase(objFile.Extension)
At this point, however, things start to diverge a little. To begin with, we use the Split function to convert our file name (This is A file) to an array, an array consisting of the 
following items:

This

is

A

file

As you can see, by splitting on the blank space we’ve created an array where each item represents a single word in the file name. Once we’ve done that we can
execute this block of code:

For i = 0 to UBound(arrWords)
    intLength = Len(arrWords(i))
    strFirstLetter = UCase(Left(arrWords(i), 1))
    strOtherLetters = LCase(Right(arrWords(i), intLength - 1))
    strName = strFirstLetter & strOtherLetters
    arrWords(i) = strName
Next
What we’ve done here is set up a For Next loop that runs from 0 to the number of items in the array (the “upper bound” of the array, something we can determine
using the UBound function). Inside that loop we use the Len function to determine the number of characters in the first item (word) in the array. From there we can
use this line of code to grab the first letter in that value and convert it to an uppercase letter:
strFirstLetter = UCase(Left(arrWords(i), 1))

And then we can use this line of code to grab all the remaining characters in the value (that is, everything except the first character) and convert them to lowercase
letters:

strOtherLetters = LCase(Right(arrWords(i), intLength - 1))

We next combine our uppercase first letter with our lowercase remaining letters, then assign that combined value back to the array item:

strName = strFirstLetter & strOtherLetters
arrWords(i) = strName
What does that mean? Well, suppose the first item in the array was tHIs. After running through this For Next loop, that first item will now be This. In other words, we’ve capitalized 
the first letter in the word, and made the remaining letters all lowercase.

After we repeat this process for all the items in the array we use the Join function to convert the array back to a string, separating each item (word) with a blank space. In other
words, if we started out with this file name:

This is A file

We’re going to end up with this file name:

This Is A File

At that point we simply call the Rename method to rename the file, then go back to the top of our For Each loop and try again with the next file in the collection.

Is that really going to apply title casing to all our file names? Give it a try and see for yourself.

That’s all we have time for today, MV; like we said, we’ll address your other question after the Scripting Editor gets back. In the meantime, the Scripting Guy who writes this
column needs to get some new sunglasses himself. However, he will simply pick up a pair at Walgreen’s; the Scripting Guy who writes this column doesn’t need to jet down
to California just to buy sunglasses. The truth is, he doesn’t need sunglasses that have temples made of ivory buffalo horn. No, sir: as long as the sunglasses keep the glare
out of his eyes he’s happy.

Well, provided they also come with 132 hand-set, full-cut diamonds. But if the temples are made out of Alaskan walrus tusk? Hey, no problem; he’s not picky.