If you manage people in your organization, either as a leader or a project manager, the Microsoft Active Directory (AD) is a gold mine of valuable information open to anyone inside the corporate network.
Active Directory (AD) is a directory service for Windows domain networks. It was original designed to keep track of computers and printers in the corporate office network, but it is also used for storing and managing users of the network. It is the user administration functionality that we will explore in this article.
Active Directory structures are arrangements of information about objects. The objects fall into two broad categories: resources (e.g. printers) and security principals (user or computer accounts and groups). Each object represents a single entity – whether a user, a computer, a printer, or a group – and its attributes.
You can extract the full name, employee number, picture, e-mail, phone number(s), office address, title, and much more for an employee in your organization. After I realized that this information is easily available, I have saved a lot of time creating stakeholder overviews, resource plans, and project plans. Extracting information from the AD is a great way of building your own dynamic contacts list in Excel instead of being dependent on Outlook. So when any of your team members changes their office address or phone number, you can just refresh all information with a single click.
Sounds great, right?
In Excel we can create our own queries to extract employee information based on the employee number or the e-mail address. However, if you build your searches based on the e-mail address, I’ve seen that the e-mail address is a scarce resource and they are often re-used if someone quit the company. In some cases, I’ve notices that the AD query might return both the expired (former employees) and the new information (current employee) – so it is a good idea to check the employee ID to ensure that you read the most recent data. When an employee has left the company, the employee number is cleared.
Query
To query data from Active Directory we will use the Lightweight Directory Access Protocol (LDAP). LDAP is an open protocol for accessing directory information over a computer network. In the below example we will read the following fields:
- Employee ID (LDAP: employeeid)
- Name (LDAP: name)
- Title (LDAP: title)
- Department (LDAP: department)
- Office Location (LDAP: physicaldeliveryofficename)
Other fields that might be usefull when creating a Contact list:
- Display Name (LDAP: displayname)
- Mobile Phone number (LDAP: mobile)
- E-mail address (LDAP: mail)
- Account Name (LDAP: samaccountname)
- First name (LDAP: givenName)
- Surname (LDAP: sn)
- Company: (LDAP: company)
- User Group (LDAP: usergroup)
For a more complete list of LDAP attributes you can read from AD, please have a look at
Code
In the below example we are not going to show you all the code needed to create your own contacts list in Excel VBA, but I hope it is enough to get you started.
Option Explicit ' Update Employee Information Public Sub UpdateEmployeeInformation(ByVal strUserId As String) Dim objLdap As Object Dim strLdapDomain As String Dim objLdapConnection As Object Dim objLdapCommand As Object Dim objLdapRecordSet As Object Dim objEmployeeName As Variant Dim objEmployeeNumber As Variant Dim objTitle As Variant Dim objDepartment As Variant Dim objWorkspace As Variant ' Connect to ActiveDirectory (AD) using LDAP. On Error Resume Next Set objLdap = GetObject("LDAP://rootDSE") On Error GoTo 0 ' Was able to connect to Active Directory? If (objLdap Is Nothing) Then ' Error Err.Raise 345, Description:="Unable to connect to Active Directory (AD) using LDAP!" Exit Sub End If ' Get the domain string from LDAP strLdapDomain = objLdap.Get("defaultNamingContext") ' Valid domain? If (Trim(strLdapDomain) = "") Then ' Error Err.Raise 345, Description:="Unable to get the current domain using LDAP!" Exit Sub End If ' Free LDAP. ' The above statements were used to get the LDAP Domain name ' which will be used below. Set objLdap = Nothing ' Connect to ActiveDirectory using ADODB Set objLdapConnection = CreateObject("ADODB.Connection") Call objLdapConnection.Open("Provider=ADsDSOObject;") ' Create command to queryActiveDirectory using LDAP Set objLdapCommand = CreateObject("ADODB.Command") ' Set the query properties With objLdapCommand ' Set the connection .ActiveConnection = objLdapConnection ' Search the AD recursively, starting at the root of the domain .CommandText = "<LDAP://" & Trim(strLdapDomain) & ">;" & _ "(&(objectCategory=User)" & _ "(mailNickName=" & Trim(strUserId) & "));" & _ "name,employeeid,title,department,physicaldeliveryofficename;" & _ "subtree" End With ' Execute LDAP query Set objLdapRecordSet = objLdapCommand.Execute ' Found the user? If (objLdapRecordSet.BOF Or objLdapRecordSet.EOF) Then ' Information Call MsgBox("The user with id '" & strUserId & "' does not exist!", vbExclamation, "Missing User") Else ' Multiple users might have the same short name. ' This is because the username is re-used when a consultant ' leaves the company. ' Old users won't have an employee number. ' Walk through all users Do While (Not objLdapRecordSet.EOF) ' Get the employee number objEmployeeNumber = objLdapRecordSet.Fields("employeeid") ' Valid employee number? If (objEmployeeNumber <> vbNull) Then ' Is number? If (IsNumeric(objEmployeeNumber)) Then ' Set the value ' [Your Code] = CLng(objEmployeeNumber) Else ' Error ' [Your Code] = "" End If ' Get the employee name objEmployeeName = objLdapRecordSet.Fields("name") ' Valid employee name If (objEmployeeName <> vbNull) Then ' Set the value ' [Your Code] = Trim(CStr(objEmployeeName)) Else ' Error ' [Your Code] = "" End If ' Title objTitle = objLdapRecordSet.Fields("title") ' Valid title? If (objTitle <> vbNull) Then ' Set the title ' [Your Code] = Trim(CStr(objTitle)) Else ' Clear the title ' [Your Code] = "" End If ' Department objDepartment = objLdapRecordSet.Fields("department") ' Valid department? If (objDepartment <> vbNull) Then ' Set the department ' [Your Code] = Trim(CStr(objDepartment)) Else ' Clear the department ' [Your Code] = "" End If ' Workspace objWorkspace = objLdapRecordSet.Fields("physicaldeliveryofficename") ' Valid workspace? If (objWorkspace <> vbNull) Then ' Set the workspace ' [Your Code] = Trim(CStr(objWorkspace)) Else ' Clear the workspace ' [Your Code] = "" End If ' We found the latest item we were looking for, ' so Exit loop Exit Do End If ' Next record Call objLdapRecordSet.MoveNext Loop ' Valid recordset? If (Not objLdapRecordSet Is Nothing) Then ' Close Call objLdapRecordSet.Close ' Free memory Set objLdapRecordSet = Nothing End If End If ' Free memory Set objLdapCommand = Nothing End Sub
In the above example we’re assuming that your computer is connected to the corporate network either by LAN or WiFi, or over VPN. If you’re not connected to an AD, an error will be raised.
In the example the user id (strUserId) is the user’s short name, i.e. the username specified in the e-mail address. If for example the user John Smith has the username “js” and the e-mail address js@somecompany.com, then “js” should be specified as the input parameter. You can also use other conditions to find one or several users in the Active Directory.
My brother recommended I might like this web site. He was totally right.
This post actually made my day. You cann’t imagine just how much
time I had spent for this information! Thanks!