Hey, Scripting Guy! Question

Hey, Scripting Guy! I have a folder with a bunch of files in it, files with names like Doc 1.pdf, Doc 2.pdf, and Doc 3.pdf. How do I remove the blank spaces from all those file names?

-- JRP

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JRP. You know, today’s column represents a landmark in Hey, Scripting Guy! history. Why’s that? Because the Scripting Guy who writes this column actually used this script to solve a problem he faced. The Scripting Guys actually practicing what they preach? Bet you never thought you’d see that, did you?

Of course, not being the smartest of Scripting Guys he came very close to not using the script to solve his problem. As is his wont, this Scripting Guy read your email and wrote a script several days before he actually sat down to write this column. In the interim, he faced a situation very similar to yours: he had a folder with over 100 files in it, files that had names like this:

Slide 1.gif
Slide 2.gif
Slide 3.gif

The problem with that? Because these files were destined for the Web, all the blank spaces in all those file names had to be removed. As you might expect, this Scripting Guy opened the folder in question, right-clicked the first file, clicked Rename, and began manually renaming each and every file.

It was right then that a little voice in his head said, “How dumb are you?” At first he ignored the little voice; after all, he’s been asked that question so many times he no longer bothers to answer. (Editor’s Note: And usually the voice is coming from the Scripting Editor, who he tends to ignore anyway.) This time, though, the voice grew a little more insistent. “Why are you manually removing blank spaces from all these file names? Why don’t you just use the script you wrote the other day?”

“Um, what script is that, little voice?”

The script that removes blank spaces from file names!

Oh. This script:

strComputer = "."

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

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

For Each objFile In colFileList
    arrNames = Split(objFile.Name, "\")
    intIndex = Ubound(arrNames)
    arrNames(intIndex) = Replace(arrNames(intIndex), " ", "")
    strNewName = Join(arrNames, "\")
    errResult = objFile.Rename(strNewName)
Next

With a little help from that voice inside our head let’s see if we can explain how this script works. We begin by connecting to the WMI service on the local computer (although this script can also be run against a remote computer). We then use this query to return a collection consisting of all the files in the folder C:\Data:

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

Now it gets interesting. To begin with, we set up a For Each loop to loop through the collection of files. Inside this loop we start out by executing this line of code:

arrNames = Split(objFile.Name, "\")

What we’re doing here is using the Split method to split the file path (or, as WMI refers to it, the Name property) on the \. For example, if the first file in the collection is C:\Data\File 1.pdf we’ll end up with an array (arrNames) consisting of the following elements:

C:
Data
File 1.pdf

Why do we bother with that? Because we need to separate the file name (File 1.pdf) from the rest of the path, something that has to be done before we start removing blank spaces. What difference does that make? In this particular script, no difference. But suppose our file had this file path:

C:\Documents and Settings\Ken Myer\My Scripts\File 1.pdf

If we did a blanket replacement of all the blank spaces in the path we’d end up with this:

C:\DocumentsandSettings\KenMyer\MyScripts\File1.pdf

That’s why we need to extract the file name, remove the blank spaces just from the file name, and then put the complete file path back together.

We see we have a question: how does putting the file name into an array help us with this chore? Well, we know that the file name has to be the last item in the file path; that means it will also be the last item in the array. By determining the Ubound (upper bound) value of the array, we can determine the index number for the file name:

intIndex = Ubound(arrNames)

Once we know the index number we can use this line of code to remove any blank spaces found in the file name:

arrNames(intIndex) = Replace(arrNames(intIndex), " ", "")

It’s a complicated-looking line of code, but it’s actually doing something quite simple. What we’re doing here is assigning a new value to the last item in the array, arrNames(intIndex). What’s the new value being assigned? This:

Replace(arrNames(intIndex), " ", "")

All we’re doing here is using the Replace function to replace any blank spaces (" ") in arrNames(intIndex) with, well, nothing (""). The net effect: any blank spaces found in the file name will be removed.

With the blank spaces gone we can then use the Join function to reconstruct the file path:

strNewName = Join(arrNames, "\")

Notice that we pass Join an optional, second parameter: “\”. Why? Well, by default, the Join method puts a blank space between each of the items being joined; that would mean we’d end up with a file path like this:

C: Data File1.pdf

And you’re right: that isn’t much of a file path, is it? By using this second parameter we’re telling the Join method to place a \ between each item instead of a blank space. That gives us a file path like this:

C:\Data\File1.pdf

Which is more like it.

As soon as we have a new file path (with the blank spaces removed from the file name) we can then use this line of code to rename the file C:\Data\File 1.pdf to C:\data\File1.pdf:

errResult = objFile.Rename(strNewName)

And then we loop around and repeat the process with the next file in the collection.

There you go, JRP; we hope this script proves as useful to you as it did to us. And speaking of us, now the little voice inside our head is telling us to go get a couple doughnuts and then sneak out early. And while we probably shouldn’t do it, well, the voice was right about using this script, wasn’t it?