$theTitle=wp_title(" - ", false); if($theTitle != "") { ?>
About Virtualization, VDI, SBC, Application Compatibility and anything else I feel like
I had to lookup some users in Active Directory today which I received by mail. Offcourse I got full users name while I needed either samAccountName or full adsPath. Usually I write a small VBS script to do the lookup and paste this in Excel for further processing. But today I decided that an Excel function to do the lookup would be nice. So I wrote it.
The function is called GetAdsProp and allows you to search on a specific AD field in the whole AD tree and return the value of another field.
So how does it work? In this example I have full name in Cell A2, in B2 I want to lookup the Accountname and in C2 the E-Mail address.
In Cell B2 use the formula: =GetAdsprop(“cn”; A2; “samAccountName”)
In Cell B3 use the formula: =GetAdsprop(“cn”; A2; “mail”)
This is the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String) As String ' Get the domain string ("dc=domain, dc=local") Dim strDomain As String strDomain = GetObject("LDAP://rootDSE").Get("defaultNamingContext") ' ADODB Connection to AD Dim objConnection As ADODB.Connection Set objConnection = CreateObject("ADODB.Connection") objConnection.Open "Provider=ADsDSOObject;" ' Connection Dim objCommand As ADODB.Command Set objCommand = CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection ' Search the AD recursively, starting at root of the domain objCommand.CommandText = _ "<LDAP://" & strDomain & ">;(&(objectCategory=User)" & _ "(" & SearchField & "=" & SearchString & "));" & SearchField & "," & ReturnField & ";subtree" ' RecordSet Dim objRecordSet As ADODB.Recordset Set objRecordSet = objCommand.Execute If objRecordSet.RecordCount = 0 Then GetAdsProp = "not found" ' no records returned Else GetAdsProp = objRecordSet.Fields(ReturnField) ' return value End If ' Close connection objConnection.Close ' Cleanup Set objRecordSet = Nothing Set objCommand = Nothing Set objConnection = Nothing End Function |
That’s nice, but want if we want to perform some action on the looked up results? In my case I needed to move the users to another OU. So I made a function for that too. It’s called MoveADObject.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | Function MoveADObject(ByVal strObjectCN, ByVal strDestinationOU) Dim objDestination As IADsContainer Set objDestination = GetObject("LDAP://" & strDestinationOU) ' Let the user confirm the move If MsgBox("Move " & strObjectCN & vbCrLf & "to " & objDestination.AdsPath, vbQuestion + vbYesNo) = vbYes Then ' Move the object! Dim objMoved As IADs Set objMoved = objDestination.MoveHere(strObjectCN, vbNullString) ' Set the new AdsPath as the function result MoveADObject = objMoved.AdsPath End If 'Cleanup Set objMoved = Nothing Set objDestination = Nothing End Function |
To move an object (eg a user) in Active Directory you need it’s full path which is something like (“CN=User,OU=MyOU,DC=MyDomain,DC=local”). Every object in Active Directory has this stored in a propery adsPath. So we use the GetAdsProp function to do a lookup. Put this formula in Cell B4: =GetAdsProp(“cn”;A2;”AdsPath’)
Now use the following formula in Cell D2: =MoveADObject(C2;”OU=Admin Users,OU=Users,OU=Netherlands,DC=europe,dc=unity”)
The function asks for your confirmation first:
And the the object is moved. After the moving the value of the Cell will be the new adsPath.
I added both functions to an Excel XLA Addin which you can add in your Addins directory, then it will be available in all you Excel sheets. (The Addins directory is usuall in %userprofile%\Application Data\Microsoft\Addins)
RWADAddin.zip (20488 downloads)
113 Responses for "Query Active Directory from Excel"
This is a great script.
To build on this would it be possible to query the AD on multiple fields e.g. I have “GivenName” and “sn” and I want to return “mail”
Regards,
Ian
This looks like a good start toward something I have been planning to develop for some time now. Thanks for the head start!
Unfortunately I am running into an early roadblock, and I hope you can shed some light on the cause of the issue. I am using Excel 2003, and I have downloaded your Addin and placed it in the appropriate directory. When I bring up the VB Editor, it appears to be loading without a problem.
However, when I try to enter the formula:
=GetAdsprop(”cn”; A2; “samAccountName”)
… into a cell, I am told that the formula contains an error. Closing the error box results in the “cn” portion of the formula becoming highlighted.
Any idea what would cause this error?
Thanks in advance!
@Ian: you need to add 2 extra parameters to the function (SearchField2 and SearchString2). Then add those to the query in objCommand.CommandText in the part between the ().
@Bill: Maybe macro’s/functions are disabled in your Excel? You can test by lowering security or by using the Direct Window from the VB Editor
Thanks for the quick response!
Security is set to Low, and it still will not accept the formula. It doesn’t seem like a security issue, where I would expect an error regarding access to the data, or something like that. I am unable to even enter the formula into the cell, as if the syntax itself is incorrect. I have triple checked the spelling, and I have cut/paste the formula and tried to type it in manually, but neither method allows the formula to be accepted by Excel.
Bill, Have you tried the directwindow?
Yes, this produces an error as well –
Compile Error:
Expected: line number or label or statement or end of statement.
@Bill
Replace the semi-colon in the cell to a comma.
Incorrect:
=GetAdsprop(”cn”; A2; “samAccountName”)
Correct:
=GetAdsprop(“cn”, A2, “samAccountName”)
This worked for me.
One thing I noticed was that the lookup would not find the account name using the user name.
I had to use:
Cell A1 = Joseph
Cell B1 = Bloefish
Cell C1 = =LEFT(B1,4)&LEFT(A1,3)
Cell D1 = =GetAdsprop(“cn”,C1,”samAccountName”)
Cell C1 = bloejos
Cell D1 = bloejos
If C1 did not match the accountname in AD then I would get a message “not found” in cell D1.
I would like to see about using firstname and lastname as a search option, but I did not write the original code.
Hope this all helps.
To make it run in Office 2007 I also had to
“In the VBE select Tools -> References… From the dialog box that pops up, scrolll down until you find an entry that reads something like Microsfot ActiveX Data Objects 2.7 Library (the number might be different – pick the highest number you can see). Check the tick box next to this entry and then click ok. You should be good to go now.”
found at http://p2p.wrox.com/topic.asp?TOPIC_ID=40704
Now it works like a charm.
Is there a way to get this to do the opposite? (This may be a basic question…)
I have a list of users Account logins, and want to search AD to see if they exist, and if so, display the name.
Any help is greatly appreciated!
🙁 Seems the odds are stacked against me and I’m not 100% familar with VB…
I have Excel 2007 and did what Peter suggested. Dominic’s syntax change fixed my first problem related to changing out the semicolons for commas.
But I continue to recieve #Value in the cell with the function.
So my first question is do i have to modify the function to reflect my domain name or will it query the pc its running from?
Thanks
great tool!
it works perfectly on my PC
I have a task where I need to lookup group members from specific groups. I have tried with your tool an it works fine on groups except when I look up member. I gues the problem is that the member field contains multible lines and the variable is only a string. does anybody have any suggestions how to modify the tool?
Bill, try retyping the ” characters. just erase the ones there and put in your own.
=GetAdsprop(“cn”; A2; “description”)
and
=GetAdsprop(“cn”; A2; “memberof”)
is not works!
why?
Great Addin! Really nice.
When I added mine in the examples in the blog did not work until I replaced the semicolons with commas. Thought I would mention it. I am currently on office 2003.
Excellent tool!! Thanks!!
I can get a return for ‘proxyAddresses’. I just get a ‘#VALUE!’ return.
Also, when the script gets the domain string, it returns my current domain in the forest (e.g., “dc=123,dc=abc,dc=domain,dc=com”) which doesn’t give me full forest query ability, so I manually set the string myself but it doesn’t work. Any idea?
strDomain = “dc=domain,dc=com”
Thanks again!!
For some i am getting #NAME and for some i am getting #VALUE and for some i am getting “not found” can i know what is the reason for it ? how can i know the attributes of the table ?
Hi,
I want to get account(domain user name/password) authenticated via AD. how can advise on this?
thanks
Thank you for this very useful addin. It has saved me a lot of time. I have added a function of my own to determine whether an account is enabled or disabled.
Function GetStatus(ByVal SearchField As String, ByVal SearchString As String) As String
‘ Get the domain string (“dc=domain, dc=local”)
Dim strDomain As String
Dim ReturnField As String
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
ReturnField = “distinguishedName”
‘ ADODB Connection to AD
Dim objConnection As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘ Connection
Dim objCommand As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
‘ Search the AD recursively, starting at root of the domain
objCommand.CommandText = _
“;(&(objectCategory=User)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘ RecordSet
Dim objRecordSet As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetStatus = “not found” ‘ no records returned
Else
Set objUser = GetObject(“LDAP://” & objRecordSet.Fields(ReturnField))
If objUser.AccountDisabled = True Then
GetStatus = “Disabled” ‘return value
End If
If objUser.AccountDisabled = False Then
GetStatus = “Enabled” ‘ return value
End If
End If
‘ Close connection
objConnection.Close
‘ Cleanup
Set objUser = Nothing
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
Very nice Remko!
Works great with Office 2007.
A couple of things:
do not cut and paste from the site directly to your VBA editor. The formatting is all wrong including the ‘
Also avoid pasting the actual function syntax into the worksheet cell. re-type
If you do not see your new function listed when you browse using the “fx” button then something is wrong with where you put the code. Be sure to copy the function into a new module.
Verify Macro “Trust Center” settings, add the references mentioned above
good luck!
I’ve moddified the function so it works with multi-valued fields (MemberOf, ObjectClass etc.) and with other objects, such as Group or Computer.
To get it working i’ve added two extra optional parameters;
The third (optional) parameter defaults to “User” but can be any valid object.
The fourth (optional) parameter defaults to “;” but can be any string and is used as seperator string between the multivalues.
Example1:
=GetAdsprop(“cn”; “MyWindowsXPComputer”; “operatingSystem”;”computer”)
returns ‘Windows XP Professional’
Example2:
=GetAdsprop(“cn”; B6; “memberOf”)
returns ‘CN=CitrixAdmin,OU=Security,OU=Groups,DC=TestAD,DC=local;CN=SQLDBA,OU=Security,OU=Groups,DC=TestAD,DC=local;CN=VMwareAdmin,OU=Security,OU=Groups,DC=TestAD,DC=local;CN=3rdLineAdmin,OU=Security,OU=Groups,DC=TestAD,DC=local;CN=TestAdministrators,CN=Users,DC=TestAD,DC=local’
Here’s the modified code;
=======================================
Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String, Optional ByVal ObjectType As String = “User”, Optional ByVal strSepChar As String = “;”) As String
Dim strDomain As String
Dim varItem As Variant
Dim varProp As Variant
Dim strOut As Variant
Dim lngLen As Integer
‘ Get the domain string (“dc=domain, dc=local”)
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
‘ ADODB Connection to AD
Dim objConnection As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘ Connection
Dim objCommand As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
‘ Search the AD recursively, starting at root of the domain
objCommand.CommandText = _
“;(&(objectCategory=” + ObjectType + “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘ RecordSet
Dim objRecordSet As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetAdsProp = “not found” ‘ no records returned
Else
‘ Will be an variant-array if multi-value field.
varProp = objRecordSet.Fields(ReturnField).Value
If VarType(varProp) > vbArray Then
For Each varItem In varProp
strOut = strOut & varItem & strSepChar
Next
‘ Remove trailing separator character(s).
lngLen = Len(strOut) – Len(strSepChar)
If lngLen > 0 Then
GetAdsProp = Left(strOut, lngLen)
Else
GetAdsProp = “”
End If
Else
‘ Not a multi-value field: just return the value.
GetAdsProp = objRecordSet.Fields(ReturnField)
End If
End If
‘ Close connection
objConnection.Close
‘ Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
=======================================
Hope this is to any use for someone…….
I have the resverse requirment. Get the list of users for groups already in excel. If you can give me a clue to get that function would be my chirstmas gift.
THanks
Great job Remko!
Starting from your code, I created a set of macro that I’m using to monitorize my job on huge active directory.
If you agree, I’d like to talk about your work on my blog (which is coming).
@ GEBRE:
Starting from remko work, change adoCommand.CommandText as follows:
adoCommand.CommandText = _
“;(&(objectCategory=Group)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree
and fit following function at your scope:
======================================
Function MemberList(ByVal SearchString As String) As String
Dim strDomain As String
Dim objRootDSE, strDNSDomain, adoCommand, adoConnection, dove
Dim strBase, strFilter, strAttributes, strQuery, adoRecordset
Dim strDN, strUser, strPassword, objNS, strServer, objmemberOf, objgroup, arrGroup, strlist
Const ADS_SECURE_AUTHENTICATION = &H1
Const ADS_SERVER_BIND = &H200
Const SearchField = “cn”
Const ReturnField = “adspath”
‘ Specify a server (Domain Controller).
strServer = “”
strUser = “”
strPassword = “”
Set objNS = GetObject(“LDAP:”)
Set objRootDSE = objNS.OpenDSObject(SearchString, _
strUser, strPassword, _
ADS_SERVER_BIND Or ADS_SECURE_AUTHENTICATION)
strlist = “”
If IsArray(objRootDSE.member) Then
For Each objgroup In objRootDSE.member
objgroup = Mid(objgroup, 4)
arrGroup = Split(objgroup, “,”)
strlist = strlist & arrGroup(0) & Chr(10)
Next
Else: If Not (IsEmpty(objRootDSE.member)) Then strlist = Split(Mid(objRootDSE.member, 4), “,”)(0)
End If
MemberList = strlist ‘ return value
End Function
=====================================
Now, assuming A1=groupname:
B1=getadsprop(“samaccountname”,A1,”adspath”)
C1=memberlist(B1)
🙂
Because I’m working on it, my vba can contains some unused variables.. I will clean.. sooner or later..
Sorry for my poor English (tnx google translator)
Bye
[…] miei vagabondaggi nella Rete mi sono imbattuto nel fantastico lavoro di Remko che ho adattato alla mia situazione. Ma vi consiglio di leggere il suo articolo per i dettagli pur […]
Hi,
I have the user ids of 500 users and want to know the names of users using excel
Hi Khurram,
you can use the original Remko work!
Download his xla and add in your Excel add on; in A column insert ids, in B column =GetAdsProp(“cn”;A1;”givenname”), in C column =GetAdsProp(“cn”;A1;”sn”). Drag for all id!
Bye
I was following lah’s advice for find if users are disabled or not. I copied the function and added to to the one I downloaded from you. How do I get this to work if all I have is a list of their usernames?
Thanks
Was very useful. Though the Add-in did not work for me, the code did. Thank you!
[…] parte ancora dal lavoro di Remko per controllare i membri dei gruppi, ho trasformato la macro in vbscript e ci ho aggiunto delle […]
[…] of my freeware tools such as LaunchRDP, RDP Password Encryption, Active Directory Excel Addin, RDP Clipboard Fix (well this list can go on and on, see also the Downloads […]
Not sure what I’m doing worong but I only get not found on the cells. How can I debug to verify it’s actually running?
[…] Dit blogartikel was vermeld op Twitter door John Boyer. John Boyer heeft gezegd: RT @qdessa: Query Active Directory from Excel http://t.co/n9mVw4f via @@remkoweijnen Fantastic utility – saved me hours […]
Bedankt Remko! Deze addin had ik echt nodig!
Excellent work.
I have same query as Ian Platt above (Post #1).
I’m trying to use your reply (Post #3).
I have added 2 extra parameters to the function like this:
Function GetAdsProp2(ByVal SearchField As String, ByVal SearchField2 As String, ByVal SearchString As String, ByVal SearchString2 As String, ByVal ReturnField As String) As String
And added those to the query like this:
“;(&(objectCategory=User)” & _
“(” & SearchField & “=” & SearchField2 & “=” & SearchString & “=” & SearchString2 & “));” & SearchField & “,” & ReturnField & “;subtree”
Assuming I’ve done this correctly so far –
What would the formula syntax be?
Finding a solution to entering First Name and Last Name and returning their email address is the holy grail for me!!
[…] Query Active Directory from Excel | Remko Weijnen's Blog (Remko's Blog) (tags: excel microsoft activedirectory) Leave a Comment LikeBe the first to like this post.Leave a Comment » […]
For Ian2:
This worked for me.
Function GetAdsProp2(ByVal SearchField As String, ByVal SearchString As String, ByVal SearchField2 As String, ByVal SearchString2 As String, ByVal ReturnField As String) As String
objCommand.CommandText = _
“;(&(” & SearchField2 & “=” & SearchString2 & “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
=GetAdsProp2(“givenName”,B1,”sn”,C1,”mail”)
Weet iemand hoe je de description fields kan gebruiken met deze addin? =GetAdsprop(“cn”;A1;”description”)
lijkt niet te werken…
Ook de aanpassing van Michel werkte niet.
Did someone has a working code for the
“cn”;a1;”description”???
alexander:
“Beschrijving” werkt niet voor mij. Misschien dat onze bedrijven geen gebruik maken van deze eigenschap … http://www.computerperformance.co.uk/Logon/LDAP_attributes_active_directory.htm
Thanks Jessica – but this doesn’t work for me.
You have replaced this code:
“;(&(objectCategory=User)” & _
with this code:
“;(&(” & SearchField2 & “=” & SearchString2 & “)” & _
– is this correct?
Hi Remko,
Its a great addin and working perfectly fine. I dont have much idea about programming, is it possible to write a function which take’s Ad group name and wndows login name and checks whether the user is member of that group or not. Only true false is required. we have approx 1600 hundred users and they are member of certain security groups which needs to keep track of.
If done it will be a great help.
Thanks
Hi. I try days ago the script and it work great.
But I try again now, and I get just “not found”.
someonde know something about this error?
Could it be that the function =GetAdsprop(“cn”; A2; “samAccountName”) doesn’t work in Excel 2010? I just can’t seem to get it to work. Or should I look at my settings or something like that?
Verzekering:
No idea about 2010 – but your formula should have commas (“,”) where you have typed semi-colons (“;”)…
Hi, very useful post. can you please tell me how to use the add-in? It’s frustrating becausse I added it to the directory and even see it in my add-ins but how do I access its interface?
Not knowing enough about VB Script to know if I can or can’t do what I want to do with this code. I want to lookup a computer name and return the OU that it belongs to. Is it possible to do that with this code as it is?
Hi All,
How can we get this code to work in order to grab the “description” field. Currently it just says #Value.
Thanks,
Marchew:
I get the same (#Value). For me, it because our company does not use that field in our Active Directory.
I found a webpage that emphasises Description and Display Name are different:
http://www.computerperformance.co.uk/Logon/LDAP_attributes_active_directory.htm
Maybe this helps?
This is exactly what I have been looking for however I am getting a compile error: User-defined type not defined. I am running excel 2003. can anyone help me out?
@jacob: I can help you. Are you using the original code posted by Remko?
This could be the solution I’m looking forward, although I’m getting an #Value error, I just want the surname and the forname and perhaps a notice if the UserID doesn’t exist
@Gunther, @jacob
before all: check quotes.
If the pc where you are working isn’t in domain, take a look at post n° 22
Sergio
@abhay
You probably know me, if you’re who I think you are. 🙂
My VB skills need to be dusted off, but I’m trying to use this code in the vb editor in Excel 2007. I’m getting an error:
User-defined type not defined
on the following line:
Dim objConnection As ADODB.Connection.
I have pasted inthe code from the vb editor. As stated above coping from this page to VB editor has some issues that need to be cleaned up:
Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String, Optional ByVal ObjectType As String = “User”, Optional ByVal strSepChar As String = “;”)
Dim strDomain As String
Dim varItem As Variant
Dim varProp As Variant
Dim strOut As Variant
Dim lngLen As Integer
‘ Get the domain string (“dc=domain, dc=local”)
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
‘ ADODB Connection to AD
‘Dim objConnection As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘ Connection
Dim objCommand As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
‘ Search the AD recursively, starting at root of the domain
objCommand.CommandText = _
“;(&(objectCategory=” + ObjectType + “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘ Recordset
Dim objRecordSet As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetAdsProp = “not found” ‘ no records returned
Else
‘ Will be an variant-array if multi-value field.
varProp = objRecordSet.Fields(ReturnField).Value
If VarType(varProp) > vbArray Then
For Each varItem In varProp
strOut = strOut & varItem & strSepChar
Next
‘ Remove trailing separator character(s)
lngLen = Len(strOut) – Len(strSepChar)
If lngLen > 0 Then
GetAdsProp = Left(strOut, lngLen)
Else
GetAdsProp = “”””
End If
Else
‘ Not a multi-value field: just return the value.
GetAdsProp = objRecordSet.Fields(ReturnField)
End If
End If
‘ Close connection
objConnection.Close
‘ Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
I founf the issue:
The issue was (-) converted to (–).
I have a spredsheet with AD logins, All I want to do is get their primary email address. Below is the code that I’ve pulled from the website, but I can’t seem to get it to work. her is the formula I am using:
=GetAdsprop(“samAccountName”, B2, “mail”)
Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String, Optional ByVal ObjectType As String = “User”, Optional ByVal strSepChar As String = “;”)
Dim strDomain As String
Dim varItem As Variant
Dim varProp As Variant
Dim strOut As Variant
Dim lngLen As Integer
‘ Get the domain string (“dc=domain, dc=local”)
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
‘ ADODB Connection to AD
Dim objConnection As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘ Connection
Dim objCommand As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
‘ Search the AD recursively, starting at root of the domain
objCommand.CommandText = _
“;(&(objectCategory=” + ObjectType + “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘ Recordset
Dim objRecordSet As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetAdsProp = “not found”
‘ no records returned
Else
‘ Will be an variant-array if multi-value field.
varProp = objRecordSet.Fields(ReturnField).Value
If VarType(varProp) > vbArray Then
For Each varItem In varProp
strOut = strOut & varItem & strSepChar
Next
‘ Remove trailing separator character(s)
lngLen = Len(strOut) – Len(strSepChar)
If lngLen > 0 Then
GetAdsProp = Left(strOut, lngLen)
Else
GetAdsProp = “nothing”
End If
Else
‘ Not a multi-value field: just return the value.
GetAdsProp = objRecordSet.Fields(ReturnField)
End If
End If
‘ Close connection
objConnection.Close
‘ Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
Hi Sergio,
I’ve checked the qutes, I’m using i.e.
=GetAdsprop(“cn”; A1; “samAccountName”) as mentioned in the description, but always I get #value ;o(
@Gunther
I’m sorry, I meant to tell you to replace the quotes so that they are the standard ones (in Italian keyboard double quotes are above the ‘2’ and the single one under the’?’) because the blog format text and replace the quotes with quotes oriented.
Take a look here on my blog http://serio72.altervista.org/blog/wp-content/uploads/2010/01/getadsprop.txt replacing “Server/baseDN” with those specific to your domain.
@Pat: I’m using this also in Office2007 🙂
Hi Sergio,
sorry, now I’m totally confused ;o(
As VB code I use:
Function GetAdsProp(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String) As String
‘ Get the domain string (“dc=domain, dc=local”)
Dim strDomain As String
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
‘ ADODB Connection to AD
Dim objConnection As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘ Connection
Dim objCommand As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
‘ Search the AD recursively, starting at root of the domain
objCommand.CommandText = _
“;(&(objectCategory=User)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘ Recordset
Dim objRecordSet As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetAdsProp = “not found” ‘ no records returned
Else
GetAdsProp = objRecordSet.Fields(ReturnField) ‘ return value
End If
‘ Close connection
objConnection.Close
‘ Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
As I understand your code the doamin specific is determined automatically (by row
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
)
or I’m wrong and I’ve to replace which row which the specific of my domain?
Thx
Hi Gunther,
You’re right!
Because I’m working in a forest, not single domain, I must specify where do execute the query.
Sorry for the confusion 🙂
Hi Sergio,
I’m also working in a forest (have you got a detailed sample for forest use?) and I need to query some “domains” (i.e. na.contoso.com, eu.contoso.com and ap.contoso.com) if a user exists.
Thx
Hi Gunther,
I haven’t..
Take a look here http://msdn.microsoft.com/en-us/library/ms675564(v=vs.85).aspx , after “To search the entire forest” 🙂 It seems right for our scopes 🙂
I’ll try it too.. 😀
Better here:
http://www.indented.co.uk/index.php/2008/10/21/listing-all-domains-in-a-forest/
I hope looping through “ncname” values we can search values in all domain in our forest.
@Gunter
Eureka: It Works!
http://serio72.altervista.org/blog/wp-content/uploads/2011/09/GetAdsPropGL.txt
Hio Sergio,
sorry to bother you again and again.
In the VB Editor (ALT-F11) I copied your new code into the “code page of the sheet and changed the formula to =GetAdsPropGL(“cn”; A2; “samAccountName”), but I always get an error.
Normally there should be now personal data in the sheet, so would it be possible to download your actual sheet with examples of (running) ADS queries?
Thx (I’m going beserk)
Hi Ghunter,
add the code as “Module” 🙂
I don’t remember why, but in this way it works 😉
Don’t worry, it’s fun to feel useful!
Hi Sergio,
I’ve created a module in this sheet, but same proceudre as every year, I’m getting the same error.
This code would be the icing on the cake.
I don’t know what to do.
Gunther,
write me through my blog, so I can send a file with code that works for me.
link: http://serio72.altervista.org/blog/?page_id=2
I wanted to Query Active Directory computer objects. Each OU should have it’s own tab in excel then each column should have OU name, object name, serial, model, if this is possible point me to the right direction.
Has anyone used the GetStatus function posted by lah in post #18 above working using Excel 2007?
Hi Brian,
Can I suggest you to use GETADSPROP with the following one:
Function DisabledChck(ByVal SearchField)
Dim objuser, adspath
adspath = GetAdsProp(“cn”, SearchField, “adspath”)
Set objuser = GetObject(adspath)
If objuser.AccountDisabled = False Then
DisabledChck = “Enabled”
Else
DisabledChck = “Disabled”
End If
End Function
Remember to re-type quotes manually after cut & paste!
Bye
Enhancement request:
Workflow: I have excel data, with AD user login (samaccountname) I look up Emploee Type using getadsprop, and display that in a cell.
I want to change that attribute bassed on a 3rd column in the sheet. What code enhancements would I need?
Thanks
Pat,
take a look here: http://www.computerperformance.co.uk/ezine/ezine47.htm
I think is possible to search the account and modify the property you need with one of methods described.
Bye
Sergio
Very useful post, thanks a lot!
Excellent tool, very useful!
Query AD on multiple fields.
Solution to Post 1 (Ian Platt) & Post 33 (Ian2), using corrected code from Post 35 (Jessica):
Code:
Function GetAdsProp2(ByVal SearchField As String, ByVal SearchString As String, ByVal SearchField2 As String, _
ByVal SearchString2 As String, ByVal ReturnField As String) As String
‘Get the domain string (“dc=domain, dc=local”)
Dim strDomain As String
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
‘ADODB Connection to AD
Dim objConnection ‘As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘Connection
Dim objCommand ‘As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
‘Corrected code from Jessica to include ldap
objCommand.CommandText = _
“;(&(objectCategory=User)” & _
“(” & SearchField2 & “=” & SearchString2 & “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘Recordset
Dim objRecordSet ‘As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetAdsProp2 = “not found” ‘no records returned
Else
GetAdsProp2 = objRecordSet.Fields(ReturnField) ‘ return value
End If
‘Close connection
objConnection.Close
‘Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
Formula:
=GetAdsProp2(“givenName”,A1,”sn”,B1,”mail”)
Hope this might help people…
Post 74 continued…
Ahh – now I see what the problem is – some characters from the vb code wont paste correctly into this blog post.
So,
after:
objCommand.CommandText = _
insert:
the LDAP line from Remko’s original code at the top
and delete this corrupted line:
“;(&(objectCategory=User)” & _
!!!!!
I can’t seem to get the Move function to work…
I’ve added the code and all, but when I try to use it in Excel, it seems to be confused with the OU information…
What I use in Excel (Example):
=MoveADObject(C2;”OU=Admin Users,OU=Users,OU=Netherlands,DC=europe,dc=unity”)
If I evaluate the Formula, it replaces “OU” with #NAME?, and keeps doing so for the whole OU…
Of course I tried it with an OU in my domain, but still no luck… Any ideas?
Thanks,
if the computer is not joined to a domain the code gives an error;
is it possible that first of all to verify if the computer is joined to a domain and mabe after that if has domain controller available for query ldap ?
Thanks.
I’ve solved the issue with the ‘Description’ field showing up as #Value:
The ‘returned’ value for description actually shows as an array so in this example as text we want is in element (0).
I suggest creating a separate Function for description field and modify the code like i have below:
Description = objRecordSet.Fields(ReturnField) ‘ return value
GetComputerDescription = Description(0)
@Marius:
I you use a computer that isn’t joined to a domain you can bind AD using “alternate credentials”:
….
Const ADS_SECURE_AUTHENTICATION = &H1
Const ADS_SERVER_BIND = &H200
‘ Specify a server (Domain Controller).
strServer = “<>”
‘ Specify your credentials.
strUser = “domain\user”
strPassword = “password”
‘ Determine DNS domain name. Use server binding and alternate ‘ credentials. The value of strDNSDomain can also be hard coded.
Set objNS = GetObject(“LDAP:”)
Set objRootDSE = objNS.OpenDSObject(“LDAP://” & strServer & “/RootDSE”, strUser, strPassword, ADS_SERVER_BIND Or ADS_SECURE_AUTHENTICATION)
strDNSDomain = objRootDSE.Get(“defaultNamingContext”)
Set objCommand = CreateObject(“ADODB.Command”)
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Provider = “ADsDSOObject”
objConnection.Properties(“User ID”) = strUser
objConnection.Properties(“Password”) = strPassword
objConnection.Properties(“Encrypt Password”) = True
objConnection.Properties(“ADSI Flag”) = ADS_SERVER_BIND Or ADS_SECURE_AUTHENTICATION
objConnection.Open “Active Directory Provider”
Set objCommand.ActiveConnection = objConnection
objCommand.CommandText= …
Pritesh, could you post your code for me?? I’m having similar issues where the description field for computer objects returns with #VALUE, seems to be a “mismatch type” error….
I can query a user object and return the sAMAccountName just fine.
@Pritesh
Managed to get the description out of AD as Pritesh said (thanks for that!!) now I have the same issue when trying to update the description back to AD, the idea is to check, modify and feed back to AD.
Here is my code:
Function SetAdDescription(ByVal IgsmNumber As String, ByVal InputDesc As String) As String
Dim strDomain As String
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
Dim PutDesc(0) As String
PutDesc(0) = InputDesc
Set objComputer = GetObject(“LDAP://CN=” & IgsmNumber & “,dc=contoso,dc=com”)
objComputer.PutEx “Description”, PutDesc
objComputer.SetInfo
Set objComputer = Nothing
End Function
So I would just call the function with the two cells with the information:
SetAdDescription (A1, B1)
have the same #Value issue…
Any help would be appreciated!
AWESOME post! Thank you very much for shortening the time to complete my task!
Big thanks! Working great!
In terms of modifying the return value, I am trying to return the manager name.
I can do this by changing the formula, and the return is:
CN=Doe\, John,OU=Users, OU=Department, OU=Company, DC=Server
What do I need to change the the script to return simply
Doe, John
??
Thanks for any help! Andy
Hi,
I seem to be having some troubles with this script as whatever I do it’s just giving me the “#NAME?” error.
My goal is to retrieve the description from the group name. But for the sake of testing the script I’ve tried to search samAccountName based on first name last name, as per the OP example.
Both attempts give me the same error, Is anyone able to assist with this? I’ve tried the various responses on the comments but nothing seems to be working.
Thanks in advance!
Vio
Hi Vio,
To make it working you must add the RWADAddin.xla as Addon Component to excel or paste the code as MODULE in your workbook.
Sergio
Hi Vio,
For what I understand it seems you need to put the contents of Description into an array otherwise it won”t work. This is what I did:
Function GetDescription(ByVal SearchField As String, ByVal SearchString As String, ByVal ReturnField As String) As String
‘ Get the domain string (“dc=domain, dc=local”)
Dim strDomain As String
strDomain = GetObject(“LDAP://rootDSE”).Get(“defaultNamingContext”)
‘ ADODB Connection to AD
Const ADS_SCOPE_SUBTREE = 2
Dim objConnection As ADODB.Connection
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open “Provider=ADsDSOObject;”
‘ Connection
Dim objCommand As ADODB.Command
Set objCommand = CreateObject(“ADODB.Command”)
objCommand.ActiveConnection = objConnection
objCommand.Properties(“Page Size”) = 1000
objCommand.Properties(“Searchscope”) = ADS_SCOPE_SUBTREE
‘ Search the AD recursively, starting at root of the domain
objCommand.CommandText = _
“;(&(objectCategory=computer)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
‘ Recordset
Dim objRecordSet As ADODB.Recordset
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount = 0 Then
GetDescription = “not found” ‘ no records returned
Else
Description = objRecordSet.Fields(ReturnField) ‘ return value
GetDescription = Description(0)
End If
‘ Cleanup
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
End Function
Let me know if that helps!
Dan.
Hi all,
to understand what is the attribute type are you trying to retrieve, can be usefull “Active directory explorer” from SYSINTERNALS / MICROSOFT (http://technet.microsoft.com/en-us/sysinternals/bb963907).
For example to know type of the Description attribute, you can search an account, click right, properties, attributes and here scroll down to find info you need.
Sergio
Thanks for the info! Very useful!!
Thanks to Remko and all other commenters. It has been very useful for me.
I entered your function as module to excel 2012 and all i get is : “#VALUE!”
Has anyone managed to get the GetAdsProp2 working with multiple criteria?
I’m trying and keeping getting “Not Found”. I know how this all works and I ahve double checked everything and still nothing found.
This is a brilliant thread and I have had much success with GetAdsProp, but now my goal posts have moved (no surprises there) I need to get the multiple input working.
Section of code:
objCommand.CommandText = _
“;(&(objectCategory=User)” & _
“(” & SearchField2 & “=” & SearchString2 & “)” & _
“(” & SearchField & “=” & SearchString & “));” & SearchField & “,” & ReturnField & “;subtree”
Formula:
=GetAdsProp2(“givenName”,A1,”sn”,B1,”mail”)
Any ideas?
I copied the code in the VB but i need to know what formula do i write in the sheet to get all the users
Awesome Script!!! Too bad my company has a policy to disable MACRO imported from outside otherwise this could have saved me tons of work ! 🙂
This appears to behave like a volatile function.
Every time you change something in the sheet everything gets recalculated. There is no declaration of volatile in the zipped add-in.
So why do I get all of these recalculations?
[…] when changing filter- why/when? I am using the function GetAdsprop from Query Active Directory from Excel. I have it stored as an Add-In. I have a long column of Windows IDs and use GetAdsprop to get the […]
I know this thread is kind of old but could use some help.
I was able to get the GetAdsProp working however I can only get it to work Via my logged on Domain and I need it to query all Domains.
I saw Sergio’s post and reviewed his code but could not get it to work, same as Gunther but there is no resolve listed her for that.
Any help would be great.
I have an excel sheet that has last name in A column and first name in B column. Is there any way for this to search AD and provide email in C column?
This is a fantastic AddIn!!!! It saved me loads of time and I was very surprised at how simple it was to get working when my other choices were beyond my understanding!
THANK YOU!!!
this works great, but how do i make it search entire forest
Rob, I had tried to get it to do the same, and was not successful. Check reply #96.
If you happen to find a way I would still be interested to know as well.
Hi All,
I have been using GetAdsprop for sometime now, with massive benefits to myself and my work. However, since trying to use it within Excel 2010 I find that the response time is extremely poor, to the point of being unusable.
Office 2003 is fine.
Has anyone else noticed this at all?
Any help would be greatly received.
Has anyone successfully used GetADSProp with Excel 2010? I have used it in Office 2003 and it is perfect, when I or other colleagues try to access using Excel 2010 it is slow, to the point of being unusable.
Any thoughts?
Hi. Trying to get up to speed with your script. I have zero experience with VBA, but I am wondering how the ADO connection can work. How do you connect to the AD server? It is not at all clear from the scripts.
Are there missing pieces that are assume to exist? What sort of ancillary datasource/other pieces are necessary to connect to the active directory server?
I have used Softera LDAP browser before, but this required a server name and a domain userid and password. Where is this specified in the big picture?
Thanks
Mike
Hi. Trying to use the script with Excel 2010: How exactly do I need to code the connection strings to succesfully connect to a non local AD server? i.e. I would be running this on a different server (a workstation, really) than the AD server.
In essence, what pieces am I missing as far as specifying the connection to the actual AD server? I guess the term is ‘not a connectionless’ situation.
This script would be perfect for me, I just need to tie the pieces together. Thanks. Mike
@DaveC
Not sure what you’re doing or how you call it but using:-
a = “cn”
b = CreateObject(“WScript.Network”).UserName
c = “givenName”
sAD_givenName = GetAdsProp(a, b, c)
works just fine
Hi @davemcwish thanks for getting back to me, I am using GetAdsProp as I have always been using it, for instance in a formula like this; =GetAdsProp(“samaccountname”,D2,”name”)
In Excel 2003 I could have 300 plus rows and numerous columns looking up AD attributes and Excel would return those attributes fairly quickly. In Excel 2010 using the same formula’s I find that I can only return a few rows in the same time period. To do 300 rows takes inordinately longer, we are talking minutes.
Not sure why this is.
If anyone can share their experiences, I would be most grateful.
Hi, Thanks a lot for the work on this.
I’m trying to figure out how to configure this Function to connect to a specific Hostname. I’m trying to replicate an LDAPSEARCH function.
Please advise how I can connect to a specific Hostname, will the function return or search the entire schema.
Thanks.
@DaveC: I am having the same issue with Excel 2010 taking many minutes to produce results for 50x queries. If you come across any type of fix please publish it here!
Hi guys,
thanks for the script, and I am trying to give a username as input, based on username provided, I has to return all the user properties of the usernames. I tried above codes and facing some issues like automation issues. I am placing ldap url in domain string.
Regards,
Kiran Kasu.
Please help me out on this issue
Hi,
Thanks for this great function. It’s helped me get started on automating some really tedious work for my Service Desk team.
I was hoping you could help though with an issue I’ve encountered.
How can I apply this function to retrieve DepartmentNumber?
For ‘description’, and this would apply to all fields which are arrays that you only have one element assigned (like ‘departmentNumber’ I altered the existing code to below the below.
If objRecordSet.RecordCount = 0 Then
GetAdsProp = “not found” ‘ no records returned
ElseIf ReturnField = “description” OR ReturnField = “departmentName” Then
GetAdsPropArray = objRecordSet.Fields(ReturnField)
GetAdsProp = GetAdsPropArray(0)
Else
GetAdsProp = objRecordSet.Fields(ReturnField) ‘ return value
End If
You can just add a list of ‘Or’ values appended to the ‘ElseIf’. If you want the entire array returned, then you’ll need to do your own research on modifying the VBA for that.
Geoff
I tweaked the call to the function so I could pull the name of the person after being given the network ID and it worked ok; however, I had 14,000 network IDs I had to pull.
Is there a way to pull everything in AD into a stand alone file, sort the list by network ID, and then search for the associated network ID? Pulling it and sorting it may take a long time, but pulling names from 14,000 records takes about three hours.
Leave a reply