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.
