How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?

How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?

  • Comments 8
  • Likes
Hey, Scripting Guy! Question

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?

-- SD

SpacerHey, Scripting Guy! AnswerScript Center

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:

Microsoft Excel


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:

Microsoft Excel


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.


Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • As usual, this is also a very good and interestingly written article by scripting guy. Many thnaks and it just helped me to complete otherwise a very time consuming task.

    If possible, if you are not stuck with Turkey, Duck and Chicken (and perhaps Wine), can you please let me know how to access a remote computer registry within the main script by way of using a different user name and password in case if the user who is running the script does not have permission to read the remote registry in the main script. Am I sound confused here! May be it is cristal clear for a guy like you but if you need to know about the problem in details, please let me know. In my main script, I have a module to read the remote registry (HKLM) and extract some details. But, in some of the computers, due to a permission issue, this module fails. I am running this code for a list of computers stored in an Access databse table. What I want is to access the remote registry using a differetn id and pwd (and may be to change it if required) on any machine where the main script does not have permission.

    Once again, thanks a lot for the above code.

    chandanarodrigo@hotmail.com

  • I am assuming you will want to stay with VBScript, you will need to use WMI to read the registry so you can supply alternate credentials for the remote connection. Using WMI to read the registry can be a bit complicated. Here are some Hey Scripting Guy articles that should send some light. blogs.technet.com/.../registry

    Here are some scripts from the repository: http://bit.ly/gnBfI5

    Here is a Script Repository template: http://bit.ly/eLm3H7

    If you get stuck you can post to the Scripting Guys Forum where there are lots of people available to help.  social.technet.microsoft.com/.../threads

  • I hope some is still looking at this. I wanted to know how can I adjust this script to search if the users in the excel are in a specific group. I have sheet of users that I need to verify if there in a access group and if not them put them in it but the list is 408 so I wanted to script it.

  • Similarly, I would like to perform an AD search for computer objects from either a spreadsheet or a .txt file.  Any information is greatly appreciated.

  • This is a great script, and I was wondering if there is any way to move the "Found" users to a specific OU within AD>

    Thanks

  • I was wondering how to convert this to run in powershell.

  • For Net-Eng.
    I am a little bit old school.

    I modified the script to add the fully qualified name of the account, if found. Then copied the "found" accounts into a txt file. Run a FOR command with DSMOVE.

    example: FOR /F %i in C:\Script\MoveUser.txt) do dsmove %i -newparent OU=NewOU,DC=company,DC=net

    This is a great little core script to get you thinking about other ways to do things.

  • thanks