![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n<\/p>\n
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?<\/p>\n
— SD<\/p>\n
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\u2019t believe that you write a daily scripting column. (Well, OK: many people believe that you write one, they just can\u2019t figure out why<\/i> you write one.)<\/p>\n For another, they aren\u2019t always convinced that you know what you\u2019re talking about, at least when it comes to this<\/i> daily scripting column. After all, this column touches on everything from the exploits of Home Run Baker<\/b><\/a> to Schrodinger\u2019s cat<\/b><\/a> to the origin of Grandparents Day<\/b><\/a>. That\u2019s all nice, but is any of it actually true<\/i>?<\/p>\n For example, there\u2019s little doubt that many people were skeptical when this column first broached the notion of the turducken<\/b>: a chicken cooked inside a duck cooked inside a turkey. \u201cA chicken inside a duck inside a turkey?\u201d they sniffed. \u201cThen why not stuff the turkey inside a deer, the deer inside a bear, and the bear inside a hippopotamus?\u201d <\/p>\n Hmmm \u2026. Are you thinking what we\u2019re thinking?<\/p>\n 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\u2019Leary:<\/p>\n \u201cJust so ya’ll know the Turducken is a reality and purty darn good eatin\u2019. 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<\/b><\/a> on the West Bank and I had secured a wonderful Thanksgiving meal.\u201d<\/p>\n See: the Scripting Guy who writes this column was right<\/i>! Now do you believe him when he says that the Scripting Editor rides a broom<\/b><\/a> to work each day? (Editor\u2019s Note: Given that the Scripting Guy who writes this column has also accused the Scripting Editor of <\/i>being a Muggle<\/i><\/b><\/a>, he apparently can\u2019t be right <\/i>all the time.)<\/i><\/p>\n The moral of the story is that you really can trust Hey, Scripting Guy!<\/i>, 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\u2019ll have to judge that for yourself:<\/p>\n You\u2019re right: this<\/b>is<\/i> 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\u2019re just going to ignore a large portion of the script anyway. In particular, we won\u2019t discuss Active Directory searching in any detail; that\u2019s a subject way that\u2019s simply too complex for Hey, Scripting Guy!<\/i> But don\u2019t despair: if you\u2019re 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\u2019s My Printer?<\/b><\/a><\/p>\n With that in mind we\u2019ll 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<\/b> object, use the Open<\/b> method to open to file C:\\Scripts\\Test.xls, and then set Excel\u2019s Visible<\/b> property to True:<\/p>\n What does that do for us? That gives us a running instance of Test.xls that we can see on screen, an instance that \u2013 for our purposes \u2013 looks like this:<\/p>\n As you can see, there\u2019s not much to our version of Test.xls. In column A we have a list of samAccountNames<\/b>; in column B we have, well, nothing. But don\u2019t worry: before we\u2019re through we\u2019ll use column B to indicate whether or not these samAccountNames can be found in Active Directory.<\/p>\n Once Excel is up and running our next task is to assign the value 1 to a variable named i<\/i>, a counter variable that we\u2019ll 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:<\/p>\n And yes, that\u2019s a good point: this script assumes that you didn\u2019t 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\u2019s out of data and automatically exits the Do Until loop. <\/p>\n Just something to watch out for: don\u2019t skip rows when entering your data.<\/p>\n So what happens inside this Do Until loop? Well, for starters, we assign the Value<\/b> of cell row 1, column 1 to a variable named strName (remember, i<\/i> starts off equal to 1):<\/p>\n After we\u2019ve 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<\/b> equal to user<\/i>; and, 2) have a samAccountName equal to the value of strName:<\/p>\n We then use the Execute<\/b> method to run the query and return a recordset consisting of all the users with a samAccountName equal to strName:<\/p>\n As you probably know, samAccountNames must be unique within a domain. That means our recordset will either have 0 records (because it couldn\u2019t find a user account with that name) or 1 record (because the script could<\/i> 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\u2019s RecordCount<\/b> property (as the name implies, this tells us the number of records in the recordset):<\/p>\n If the RecordCount is equal to 1, we set the value of row 1, column 2 to Found<\/i>; otherwise we set the value of row 1, column 2 to Not found<\/i>. That\u2019s what we do here:<\/p>\n We then increment the value of i by 1, meaning that, the second time through the loop, we\u2019ll 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\u2019re all done we close our connection to Active Directory and display the finished results:<\/p>\n Not bad, if we do say so ourselves.<\/p>\n OK, now that this scripting stuff is out of the way let\u2019s get back to something that people really care about: the turducken. According to Bannon, \u201cMy 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.\u201d<\/p>\n Yes, that does<\/i> sound like a problem, doesn\u2019t 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. <\/p>\n Oh: and that goes for anyone else who might have Thanksgiving leftovers, too.<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a><\/p>\nConst ADS_SCOPE_SUBTREE = 2\nSet objConnection = CreateObject(\"ADODB.Connection\")\nSet objCommand = CreateObject(\"ADODB.Command\")\nobjConnection.Provider = \"ADsDSOObject\"\nobjConnection.Open \"Active Directory Provider\"\nSet objCommand.ActiveConnection = objConnection\nobjCommand.Properties(\"Page Size\") = 1000\nobjCommand.Properties(\"Searchscope\") = ADS_SCOPE_SUBTREE\nSet objExcel = CreateObject(\"Excel.Application\")\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nobjExcel.Visible = True\ni = 1\nDo Until objExcel.Cells(i, 1).Value = \"\"\n strName = objExcel.Cells(i,1)\n objCommand.CommandText = _\n \"SELECT * FROM 'LDAP:\/\/dc=fabrikam,dc=com' WHERE objectCategory='user' \" & _\n \"AND samAccountName='\" & strName & \"'\"\n Set objRecordSet = objCommand.Execute\n If objRecordset.RecordCount = 1 Then\n objExcel.Cells(i,2) = \"Found\"\n Else\n objExcel.Cells(i,2) = \"Not found\"\n End If\n i = i + 1\n objRecordset.Close\nLoop\nobjConnection.Close\n<\/pre>\n
Set objExcel = CreateObject(\"Excel.Application\")\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.xls\")\nobjExcel.Visible = True\n<\/pre>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/samexcel1.jpg\")
<\/p>\nDo Until objExcel.Cells(i, 1).Value = \"\"\n<\/pre>\n
strName = objExcel.Cells(i,1)\n<\/pre>\n
objCommand.CommandText = _\n \"SELECT * FROM 'LDAP:\/\/dc=fabrikam,dc=com' WHERE objectCategory='user' \" & _\n \"AND samAccountName='\" & strName & \"'\"\n<\/pre>\n
Set objRecordSet = objCommand.Execute\n<\/pre>\n
If objRecordset.RecordCount = 1 Then\n<\/pre>\n
If objRecordset.RecordCount = 1 Then\n objExcel.Cells(i,2) = \"Found\"\nElse\n objExcel.Cells(i,2) = \"Not found\"\nEnd If\n<\/pre>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/samexcel2.jpg\")
<\/p>\n
<\/p>\n\n
![\"Top](\"http:\/\/www.microsoft.com\/technet\/mnplibrary\/templates\/MNP2.Common\/images\/arrow_px_up.gif\"){kind=icon}
<\/a>