Hey, Scripting Guy! How can I create a folder for each user in my domain? I’d like the folder name to consist of the user’s first initial and their last name (for example, KMyer).

— GN

Hey, GN. You know, this has been an interesting week for the Scripting Guys. Why? Because absolutely nothing interesting has happened to any of the Scripting Guys this entire week. Well, OK, maybe Scripting Guy Dean Tsaltas has had an interesting week. But Dean is currently off in the wilds of Nova Scotia, and no one has heard from him in the past few weeks. If his week is going anything like our week, however, he’s no doubt sitting in the wilds of Nova Scotia thinking, “Man, this is boring. I wonder if anything interesting has happened to the other Scripting Guys. And why exactly am I sitting in the wilds of Nova Scotia?”

Fortunately, today is Friday (although you don’t actually get to read this particular column until the following Monday), and no doubt all sorts of exciting things will happen over the weekend to help snap the Scripting Guys out of their lethargy. For example, the Scripting Guy who writes this column is going to pull weeds; that should get the old juices flowing again! Scripting Guy Jean Ross is seriously considering packing a few things into boxes, although she’s afraid that this might be too much excitement for a single weekend. (Maybe this weekend she’ll get the boxes out, then save the actual packing for next week.) As for Scripting Guy Peter Costantini – well, to tell you the truth, the other Scripting Guys never ask too many questions about what Peter does over the weekend. There are some things we’re just better off not knowing about.

Of course, the bottom line is this: we have no amusing anecdotes or witty repartee we can use to spice up today’s column. The truth is, we have nothing at all for today’s column. Nothing.

Well, except a script that can create a folder for each user in your domain:

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2
Set objConnection = CreateObject(“ADODB.Connection”)
Set objCommand = CreateObject(“ADODB.Command”)
objConnection.Provider = “ADsDSOObject”
objConnection.Open “Active Directory Provider”
Set objCommand.ActiveConnection = objConnection
objCommand.Properties(“Page Size”) = 1000
objCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE 
objCommand.CommandText = _
    “SELECT givenName, sn FROM ‘LDAP://DC=fabrikam,DC=com’ WHERE objectCategory=’user'” 
Set objRecordSet = objCommand.Execute
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    strInitial = Left(objRecordSet.Fields(“givenName”).Value, 1)
    strFolderName = “C:\Public\” & strInitial & objRecordSet.Fields(“sn”).Value
    Set objFolder = objFSO.CreateFolder(strFolderName)
    objRecordSet.MoveNext
Loop

As usual, we aren’t going to spend much time discussing the ins and outs of Active Directory search scripts: we’re far too lazy – uh, we simply don’t have the space in the daily column to do that. But have no fear; after all, we still have a two-part Tales from the Script series that tells you everything you need to know about searching Active Directory. And you are welcome to read that any time you want.

About all we will do with the search portion of our script is take a quick peek at the SQL query that returns a collection of user information:

objCommand.CommandText = _
    “SELECT givenName, sn FROM ‘LDAP://DC=fabrikam,DC=com’ WHERE objectCategory=’user'”

As you can see, all we’re doing here is selecting two attributes (givenName and sn) for all the objects in the fabrikam.com domain that meet a specific criterion. (Criterion, of course, being the singular form of criteria. Apparently we Scripting Guys aren’t just thorough, we’re also grammatical.) And just what is that criterion? It’s this: returned objects must have an objectCategory equal to user. That’s how we can return a collection of all the user accounts in our domain. (Well, along with the fact that we started our search in the domain root.) What if we wanted to return a list of all our computer accounts? Then we’d use the following WHERE clause:

WHERE objectCategory=’computer’

And so on.

And why givenName and sn? That’s easy: those are the two Active Directory attributes that correspond to, respectively, the user’s first name and last name. Wouldn’t firstName have been a better attribute name than givenName? Probably. But, like it or not, we’re stuck with givenName and sn.

After setting up our query we then call the Execute method to invoke the command and return a recordset consisting of all the user accounts in the domain fabrikam.com.

OK, where were we? Oh, right. After calling the Execute method (heh-heh; take that Microsoft editors!) we get back a recordset consisting of the first and last names of all the users in our domain. Because of that, our next step is to set up a Do Until loop that enables us to loop through the entire recordset (or, to make it sound as though we know what we’re talking about, until the recordset’s EOF – end-of-file – property is True):

Do Until objRecordSet.EOF

Oh, right: before we did that we also created an instance of the Scripting.FileSystemObject, the object needed to create our folders. Our apologies to Scripting.FileSystemObject for neglecting to mention this.

Inside the Do Until loop we use this line of code to grab the first letter of the user’s first name (i.e., their first initial):

strInitial = Left(objRecordSet.Fields(“givenName”).Value, 1)

As you can see, that’s pretty straightforward: we just use the Left function to grab the first character in the user’s givenName. And yes, the syntax for referring to recordset fields is a bit cumbersome: objRecordSet.Fields(“givenName”).Value. But don’t worry too much about that: just copy and paste the string and leave it at that.

Once we have the user’s first initial we can then construct the name of our first folder:

strFolderName = “C:\Public\” & strInitial & objRecordSet.Fields(“sn”).Value

Here we’re simply assigning a value to a variable named strFolderName. And what is that value? Well, assuming that the first user in our recordset is named Ken Myer that value will be C:\Public\KMyer. In other words,

C:\Public\ plus K (first initial of first name) plus Myer (sn, or last name) = C:\Public\KMyer

At that point we call the CreateFolder method to create a new folder, passing the variable strFolderName as the sole method parameter:

Set objFolder = objFSO.CreateFolder(strFolderName)

From there we call the MoveNext method to move on to the next record in the recordset. Etc., etc.

You know, that’s a good question: if this has been an especially boring week what kind of exciting things usually happen to the Scripting Guys? Wow; where do we begin with a question like that? Let’s see, the Scripting Guy who writes this column usually .. uh, writes this column. Scripting Guy Jean Ross usually – well, never mind. She didn’t actually do that; she just saw it on TV. Scripting Guy Peter Costantini – OK, we’re not sure about that: what exactly do you do with a package of Roman candles, a Speedo swimsuit, and two cartons of grated cheddar cheese? (That’s OK; we’d rather not know the answer to that question.)

You know what? We’ll have to get back to you on this.