113 Responses for "Query Active Directory from Excel"

1. Ian Platt May 16th, 2008 at 16:51 1

   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

2. Bill Bass May 19th, 2008 at 19:46 2

   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!

3. Remko May 19th, 2008 at 19:52 3

   @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

4. Bill Bass May 19th, 2008 at 20:32 4

   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.

5. Remko May 20th, 2008 at 23:16 5

   Bill, Have you tried the directwindow?

6. Bill Bass May 21st, 2008 at 13:41 6

   Yes, this produces an error as well –

   Compile Error:
   Expected: line number or label or statement or end of statement.

7. Dominic June 2nd, 2008 at 21:15 7

   @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.

8. Peter July 9th, 2008 at 11:29 8

   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.

9. John Kreis August 25th, 2008 at 17:18 9

   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!

10. Greg September 17th, 2008 at 22:57 10

    🙁 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

11. Per September 23rd, 2008 at 9:01 11

    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?

12. Dominic October 21st, 2008 at 13:20 12

    Bill, try retyping the ” characters. just erase the ones there and put in your own.

13. vasya November 12th, 2008 at 12:30 13

    =GetAdsprop(“cn”; A2; “description”)
    and
    =GetAdsprop(“cn”; A2; “memberof”)

    is not works!

    why?

14. Clyde March 18th, 2009 at 21:57 14

    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.

15. Elzan Sampson March 22nd, 2009 at 18:38 15

    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!!

16. vinay March 26th, 2009 at 18:05 16

    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 ?

17. Peter April 22nd, 2009 at 3:46 17

    Hi,
    I want to get account(domain user name/password) authenticated via AD. how can advise on this?

    thanks

18. lah May 27th, 2009 at 9:40 18

    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

19. Adam July 30th, 2009 at 0:08 19

    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!

20. Michel September 30th, 2009 at 22:52 20

    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…….

21. Gebre December 24th, 2009 at 18:29 21

    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

22. Sergio January 26th, 2010 at 22:22 22

    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

23. Fare query su AD via Excel · Serioblog! January 26th, 2010 at 22:54 23

    […] 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 […]

24. Khurram March 17th, 2010 at 21:00 24

    Hi,

    I have the user ids of 500 users and want to know the names of users using excel

25. Sergio March 23rd, 2010 at 16:56 25

    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

26. Eric April 20th, 2010 at 23:27 26

    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

27. Keshav June 29th, 2010 at 12:23 27

    Was very useful. Though the Add-in did not work for me, the code did. Thank you!

28. Check Folder ACL via HTA · Serioblog! July 26th, 2010 at 14:01 28

    […] parte ancora dal lavoro di Remko per controllare i membri dei gruppi, ho trasformato la macro in vbscript e ci ho aggiunto delle […]

29. Dear Visitor | Remko Weijnen's Blog (Remko's Blog) October 29th, 2010 at 17:36 29

    […] 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 […]

30. HR November 23rd, 2010 at 21:38 30

    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?

31. Tweets die vermelden Query Active Directory from Excel | Remko Weijnen's Blog (Remko's Blog) -- Topsy.com January 7th, 2011 at 18:22 31

    […] 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 […]

32. alexander den engelsman January 10th, 2011 at 11:49 32

    Bedankt Remko! Deze addin had ik echt nodig!

33. Ian2 January 17th, 2011 at 12:45 33

    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!!

34. links for 2011-01-21 « My Weblog January 22nd, 2011 at 0:03 34

    […] 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 » […]

35. Jessica January 27th, 2011 at 18:05 35

    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”)

36. alexander den engelsman January 28th, 2011 at 10:42 36

    Weet iemand hoe je de description fields kan gebruiken met deze addin? =GetAdsprop(“cn”;A1;”description”)
    lijkt niet te werken…

37. alexander den engelsman January 28th, 2011 at 11:45 37

    Ook de aanpassing van Michel werkte niet.

    Did someone has a working code for the
    “cn”;a1;”description”???

38. Ian2 January 28th, 2011 at 14:06 38

    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

39. Ian2 February 15th, 2011 at 11:13 39

    Thanks Jessica – but this doesn’t work for me.

    You have replaced this code:
    “;(&(objectCategory=User)” & _

    with this code:
    “;(&(” & SearchField2 & “=” & SearchString2 & “)” & _

    – is this correct?

40. abhay March 7th, 2011 at 19:07 40

    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

41. Hernandi March 14th, 2011 at 13:51 41

    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?

42. Verzekering March 22nd, 2011 at 14:30 42

    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?

43. Ian2 March 30th, 2011 at 14:46 43

    Verzekering:
    No idea about 2010 – but your formula should have commas (“,”) where you have typed semi-colons (“;”)…

44. Jin May 3rd, 2011 at 9:10 44

    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?

45. Brad Shaw May 6th, 2011 at 18:28 45

    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?

46. Marchew June 13th, 2011 at 10:04 46

    Hi All,

    How can we get this code to work in order to grab the “description” field. Currently it just says #Value.

    Thanks,

47. Ian2 July 8th, 2011 at 16:57 47

    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?

48. jacob August 24th, 2011 at 17:55 48

    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?

49. Sergio August 30th, 2011 at 11:38 49

    @jacob: I can help you. Are you using the original code posted by Remko?

50. Gunther August 30th, 2011 at 12:29 50

    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

51. Sergio September 1st, 2011 at 23:50 51

    @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. 🙂

52. Pat September 2nd, 2011 at 17:13 52

    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

53. Pat September 2nd, 2011 at 18:03 53

    I founf the issue:
    The issue was (-) converted to (–).

54. Pat September 2nd, 2011 at 19:23 54

    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

55. Gunther September 5th, 2011 at 12:53 55

    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(

56. Sergio September 5th, 2011 at 14:54 56

    @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 🙂

57. Gunther September 7th, 2011 at 8:26 57

    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

58. Sergio September 8th, 2011 at 10:54 58

    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 🙂

59. Gunther September 8th, 2011 at 12:45 59

    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

60. Sergio September 8th, 2011 at 16:03 60

    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.. 😀

61. Sergio September 8th, 2011 at 17:06 61

    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.

62. Sergio September 9th, 2011 at 13:07 62

    @Gunter

    Eureka: It Works!

    http://serio72.altervista.org/blog/wp-content/uploads/2011/09/GetAdsPropGL.txt

63. Gunther September 12th, 2011 at 8:40 63

    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)

64. Sergio September 12th, 2011 at 13:27 64

    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!

65. Gunther September 12th, 2011 at 13:41 65

    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.

66. Sergio September 12th, 2011 at 13:59 66

    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

67. jaime September 28th, 2011 at 18:26 67

    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.

68. Brian October 5th, 2011 at 23:46 68

    Has anyone used the GetStatus function posted by lah in post #18 above working using Excel 2007?

69. Sergio October 10th, 2011 at 23:20 69

    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

70. Pat October 20th, 2011 at 19:14 70

    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

71. Sergio October 21st, 2011 at 12:08 71

    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

72. ps4 November 3rd, 2011 at 15:44 72

    Very useful post, thanks a lot!

73. Maarten December 6th, 2011 at 13:00 73

    Excellent tool, very useful!

74. Ian2 December 13th, 2011 at 13:33 74

    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…

75. Ian2 December 13th, 2011 at 13:49 75

    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)” & _

    !!!!!

76. Brian December 22nd, 2011 at 21:26 76

    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,

77. Marius January 7th, 2012 at 23:44 77

    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.

78. Pritesh January 10th, 2012 at 14:45 78

    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)

79. Sergio February 14th, 2012 at 15:01 79

    @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= …

80. DAG March 14th, 2012 at 11:58 80

    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.

81. DAG March 15th, 2012 at 17:06 81

    @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!

82. SJRinOmaha March 21st, 2012 at 17:48 82

    AWESOME post! Thank you very much for shortening the time to complete my task!

83. Andy March 29th, 2012 at 15:22 83

    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

84. Vio April 20th, 2012 at 9:56 84

    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

85. Sergio April 23rd, 2012 at 11:30 85

    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

86. DAG April 23rd, 2012 at 11:39 86

    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.

87. Sergio April 23rd, 2012 at 12:13 87

    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

88. Carlos May 15th, 2012 at 12:05 88

    Thanks for the info! Very useful!!

89. Subbu May 15th, 2012 at 15:34 89

    Thanks to Remko and all other commenters. It has been very useful for me.

90. Bart September 17th, 2012 at 16:35 90

    I entered your function as module to excel 2012 and all i get is : “#VALUE!”

91. DaveC October 3rd, 2012 at 16:52 91

    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?

92. bob October 5th, 2012 at 8:35 92

    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

93. Amit October 26th, 2012 at 12:31 93

    Awesome Script!!! Too bad my company has a policy to disable MACRO imported from outside otherwise this could have saved me tons of work ! 🙂

94. mike croom November 23rd, 2012 at 12:15 94

    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?

95. Recalculation when changing filter- why/when? November 23rd, 2012 at 12:59 95

    […] 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 […]

96. Brian February 13th, 2013 at 21:56 96

    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.

97. Chris April 9th, 2013 at 15:53 97

    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?

98. Hayley May 17th, 2013 at 21:26 98

    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!!!

99. Rob martens June 10th, 2013 at 18:01 99

    this works great, but how do i make it search entire forest

100. Brian June 13th, 2013 at 17:06 100

     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.

101. DaveC October 7th, 2013 at 13:53 101

     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.

102. DaveC November 29th, 2013 at 13:56 102

     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?

103. Mike December 5th, 2013 at 8:02 103

     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

104. Mike December 5th, 2013 at 8:13 104

     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

105. davemcwish April 11th, 2014 at 18:00 105

     @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

106. DaveC May 12th, 2014 at 10:43 106

     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.

107. Kreeson February 27th, 2015 at 0:44 107

     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.

108. Ascy June 19th, 2015 at 2:00 108

     @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!

109. Kiran September 10th, 2015 at 9:59 109

     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.

110. Kiran September 10th, 2015 at 10:00 110

     Please help me out on this issue

111. Ano Nunga November 19th, 2015 at 1:42 111

     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?

112. Geoff Croxson January 11th, 2018 at 1:11 112

     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

113. Ken January 23rd, 2018 at 21:22 113

     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.