Query to LDAP from VBA / Excel

Question

Our company has a directory of employees where Employee ID and E-mail address are stored. I would like to create a VBA in Excel that will search for a particular person's e-mail address if Employee ID is given.

Our IT department has given me a line of code that is applicable for unix shell script:

ldapsearch -H ldaps://companydomain.net:636 -D “<your system account dn> -w <your system account password” -b “ou=people,o=company” employeeNumber=12345678 companyprimaryemailaddress

Could you please help me to write a similar code for VBA? I have searched a lot of forums and tried different solutions with ADODB.Connection but was not able to succeed. Maybe it is because I was not able to find an example with "ldaps://" instead of "LDAP://"...

Many thanks in advance!

Please show what you have tried and explain how *exactly* that didn't work. Include all errors you were getting. — Ansgar Wiechers, Oct 01 '18 at 14:08
Thanks a lot for your comments. The issue is that I was trying to get data not from Active Directory but rather from a specific server and I didn't really know how to query that server. GetObject("LDAP://RootDSE") was pointing me out to Active Directory and I was not able to modify the code to query a specific server. Meanwhile (because there was no choice for me) I have looked in the entire Active Directory of my company and found out that E-mail and ID information is also available there. So I have built the following Power Query that worked for me: — Mikhail K, Oct 02 '18 at 10:36

score 1 · Answer 1 · answered Oct 02 '18 at 10:38

Following Power Query worked for me ("companysite.net" needs to be changed to the actual site name that can be found with GetObject("LDAP://RootDSE")):

let
    Source = ActiveDirectory.Domains("companysite.net"),
    #"Expanded Object Categories" = Table.ExpandTableColumn(Source, "Object Categories", {"Category", "Objects"}, {"Category", "Objects"}),
    Objects = #"Expanded Object Categories"{0}[Objects],
    #"Expanded organizationalPerson" = Table.ExpandRecordColumn(Objects, "organizationalPerson", {"employeeID", "mail"}, {"organizationalPerson.employeeID", "organizationalPerson.mail"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded organizationalPerson",{"organizationalPerson.employeeID", "organizationalPerson.mail"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([organizationalPerson.employeeID] <> null))
in
    #"Filtered Rows"

Query to LDAP from VBA / Excel

1 Answers1