Excel VBA: Get Employee information from Active Directory

Microsoft ExcelLeave a Comment on Excel VBA: Get Employee information from Active Directory

Excel VBA: Get Employee information from Active Directory

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. 

Retrieve Employee information from Active Directory with LDAP queries
Retrieve Employee information from Active Directory with LDAP queries

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.

Related

Ulf Emsoy has long working experience in project management, software development and supply chain management.

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top


Subscribe to our newsletter