Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I delete a folder that has an apostrophe in the name?

-- JH

SpacerHey, Scripting Guy! AnswerScript Center

Hey, JH. Ah, yes, the apostrophe: every script writer’s arch-nemesis. The apostrophe seems like such a simple little character, but don’t let its appearance fool you: the apostrophe (or the single quote) is probably the most deadly character to be found on the keyboard. Whether you’re working with Active Directory, a database, or the file system, a single apostrophe can wreak total havoc on your scripts. As Bart Simpson once said of Hershey®’s Milk Duds, the apostrophe is “sweet on the outside, but poison on the inside.”

Note. No, Milk Duds don’t really have poison on the inside.

The problem with the apostrophe is that it’s a “reserved” character, a character that VBScript uses for its own purposes. For example, suppose you want to delete the folder C:\Scripts. Here’s a script that will do just that:

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

Set colFolders = objWMIService. _
    ExecQuery("Select * From Win32_Directory Where Name = 'C:\\Scripts'")

For Each objFolder in colFolders
    errResults = objFolder.Delete
Next

If you look closely at the Where clause, you’ll see that the apostrophe (or the single quote) is used to delineate a string value: ‘C:\\Scripts’. That’s why we run into problems with a folder that has an apostrophe in its name (say, Ken’s Scripts). What do you suppose happens when we try running a script like this one:

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

Set colFolders = objWMIService. _
    ExecQuery("Select * From Win32_Directory Where Name = 'C:\\Ken's Scripts'")

For Each objFolder in colFolders
    errResults = objFolder.Delete
Next

We won’t keep you in suspense: the script will flat-out fail. Why? Well, look at the Where clause:

Where Name = 'c:\\Ken's Scripts'

Because the apostrophe marks the beginning and ending of a string in a Where clause, VBScript thinks the name of the folder is this: ‘C:\\Ken’. That would be fine, except then we have a bunch of other characters (s Scripts’) following the end of the string. VBScript has no idea what that gibberish is supposed to mean, and so it simply gives up without even trying.

Yes, we know. But that’s just the way VBScript works. Because the apostrophe is a reserved character used to (among other things) mark the beginning and end of strings in a Where clause, VBScript simply has no idea what we’re talking about.

And no, no jokes about how that’s like most of the people who’ve had the misfortune of talking to the Scripting Guys. That one’s too easy!

So is there a way to work around this problem? You bet there is. As you probably know, any time we use a backslash (\) in a Where clause (like in a file path) we need to “escape” that backslash by putting another \ in front of it; that’s why we have a file path like C:\\Scripts instead of C:\Scripts. We have to do that because the \ is another reserved character; the only way to tell VBScript to use the \ as-is is by escaping it (which just means prefacing it with another \).

Hey, wait a minute: if you can use a reserved character like the \ simply by prefacing it with another \, maybe you could use a reserved character like the single quote by prefacing it with a \.

You know, that’s just crazy enough to work:

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

Set colFolders = objWMIService. _
    ExecQuery("Select * from Win32_Directory Where Name = 'C:\\Ken\'s Scripts'")

For Each objFolder in colFolders
    errResults = objFolder.Delete
Next

And there’s your answer right there, JH. Notice how we put a \ right before our apostrophe; that gives us a construction that looks like this: ‘C:\\Ken\’s Scripts’. Put a \ before any apostrophes in the folder name and the script will work just fine. Granted, it looks a little odd, but it does the trick.

Just like a Milk Dud.