How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
Hey, Scripting Guy! How can I take user names from an Excel spreadsheet, search for those users in Active Directory, and then indicate whether or not each user was found?
Hey, SD. You know, one of the drawbacks to writing a daily scripting column is that people never seem to believe you. For one thing, they don’t believe that you write a daily scripting column. (Well, OK: many people believe that you write one, they just can’t figure out why you write one.)
For another, they aren’t always convinced that you know what you’re talking about, at least when it comes to this daily scripting column. After all, this column touches on everything from the exploits of Home Run Baker to Schrodinger’s cat to the origin of Grandparents Day. That’s all nice, but is any of it actually true?
For example, there’s little doubt that many people were skeptical when this column first broached the notion of the turducken: a chicken cooked inside a duck cooked inside a turkey. “A chicken inside a duck inside a turkey?” they sniffed. “Then why not stuff the turkey inside a deer, the deer inside a bear, and the bear inside a hippopotamus?”
Hmmm …. Are you thinking what we’re thinking?
As it turns out, though, this time the Scripting Guy who writes this column knew what he was talking about. Take a look at this email he received from Bannon O’Leary:
“Just so ya’ll know the Turducken is a reality and purty darn good eatin’. As a recent transplant to Nashville, TN from New Orleans I thought this was going to be my first Thanksgiving without a Turducken in a long time. Luckily a business trip took me back to New Orleans; a quick trip to the Gourmet Butcher Block on the West Bank and I had secured a wonderful Thanksgiving meal.”
See: the Scripting Guy who writes this column was right! Now do you believe him when he says that the Scripting Editor rides a broom to work each day? (Editor’s Note: Given that the Scripting Guy who writes this column has also accused the Scripting Editor of being a Muggle, he apparently can’t be right all the time.)
The moral of the story is that you really can trust Hey, Scripting Guy!, at least when it comes to esoteric trivia that no one else ever cares about. How about when it comes to answering scripting questions, especially questions like how someone can take user names from an Excel spreadsheet, search for those users in Active Directory, and then indicate whether or not each user was found? You’ll have to judge that for yourself:
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 Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") objExcel.Visible = True i = 1 Do Until objExcel.Cells(i, 1).Value = "" strName = objExcel.Cells(i,1) objCommand.CommandText = _ "SELECT * FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user' " & _ "AND samAccountName='" & strName & "'" Set objRecordSet = objCommand.Execute If objRecordset.RecordCount = 1 Then objExcel.Cells(i,2) = "Found" Else objExcel.Cells(i,2) = "Not found" End If i = i + 1 objRecordset.Close Loop objConnection.Close
You’re right: thisis a bit longer than the typical script discussed in this column. Is that going to be a problem for the Scripting Guys? Of course not; after all, we’re just going to ignore a large portion of the script anyway. In particular, we won’t discuss Active Directory searching in any detail; that’s a subject way that’s simply too complex for Hey, Scripting Guy! But don’t despair: if you’re not familiar with the ins and outs of writing scripts that can search Active Directory then just take a peek at our two-part series Dude, Where’s My Printer?
With that in mind we’ll thus skip past the first eight lines of code and start here, with three lines in which we create an instance of the Excel.Application object, use the Open method to open to file C:\Scripts\Test.xls, and then set Excel’s Visible property to True:
Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") objExcel.Visible = True
What does that do for us? That gives us a running instance of Test.xls that we can see on screen, an instance that – for our purposes – looks like this:
As you can see, there’s not much to our version of Test.xls. In column A we have a list of samAccountNames; in column B we have, well, nothing. But don’t worry: before we’re through we’ll use column B to indicate whether or not these samAccountNames can be found in Active Directory.
Once Excel is up and running our next task is to assign the value 1 to a variable named i, a counter variable that we’ll use to keep track of our location within the spreadsheet. We then set up a Do Until loop that runs until we encounter an empty cell in column A:
Do Until objExcel.Cells(i, 1).Value = ""
And yes, that’s a good point: this script assumes that you didn’t skip any rows when entering your user names. For example, suppose you have samAccountNames in rows 1 and 3, but for some reason have a blank cell in row 2. If you run this script it will only search for the name listed in row 1. Why? Because when it hits the blank cell in row 2 the script assumes that it’s out of data and automatically exits the Do Until loop.
Just something to watch out for: don’t skip rows when entering your data.
So what happens inside this Do Until loop? Well, for starters, we assign the Value of cell row 1, column 1 to a variable named strName (remember, i starts off equal to 1):
strName = objExcel.Cells(i,1)
After we’ve assigned this first samAccountName to strName we then construct a SQL query we can use to search fabrikam.com for all objects that: 1) have an objectCategory equal to user; and, 2) have a samAccountName equal to the value of strName:
objCommand.CommandText = _ "SELECT * FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user' " & _ "AND samAccountName='" & strName & "'"
We then use the Execute method to run the query and return a recordset consisting of all the users with a samAccountName equal to strName:
Set objRecordSet = objCommand.Execute
As you probably know, samAccountNames must be unique within a domain. That means our recordset will either have 0 records (because it couldn’t find a user account with that name) or 1 record (because the script could find a user with that name). In turn, that means we can verify whether or not a given name was found simply by checking the value of the recordset’s RecordCount property (as the name implies, this tells us the number of records in the recordset):
If objRecordset.RecordCount = 1 Then
If the RecordCount is equal to 1, we set the value of row 1, column 2 to Found; otherwise we set the value of row 1, column 2 to Not found. That’s what we do here:
If objRecordset.RecordCount = 1 Then objExcel.Cells(i,2) = "Found" Else objExcel.Cells(i,2) = "Not found" End If
We then increment the value of i by 1, meaning that, the second time through the loop, we’ll check the value of row 2, column 1. After that we close the recordset and then loop around and repeat the process with the next row in the spreadsheet. When we’re all done we close our connection to Active Directory and display the finished results:
Not bad, if we do say so ourselves.
OK, now that this scripting stuff is out of the way let’s get back to something that people really care about: the turducken. According to Bannon, “My big issue is what to do with it, my wife is a vegetarian and my son is just working on getting his first tooth. 16 pounds of Turkey, Duck, and Chicken is gonna make for a lot of sandwiches.”
Yes, that does sound like a problem, doesn’t it? Tell you what, Bannon: any time you have 16 pounds of Thanksgiving leftovers feel free to send them to the Scripting Guy who writes this column.
Oh: and that goes for anyone else who might have Thanksgiving leftovers, too.