Get list of managers from list of employees (Active Directory from Excel) I have

Question

Get list of managers from list of employees (Active Directory from Excel)

I have an Excel 2010 workbook with one spreadsheet.

In column A, there are 2000 Active Directory user aliases. (E.g. bobsmith, jimjohnson, etc.) In other words, column A lists 2000 Active Directory username aliases of people at the company.

I would like to populate column B with the managers of these people.

E.g.

Cell A1 = bobsmith, Cell B1 = bobsmith's maanger
Cell A2 = jimjohnson, Cell B2 = jimjohnson's manager

How do I do this? I don't want to do programmming if I can help it since I find it hard, so I would like a ready-made solution I can copy from somewhere...

Explanation / Answer

>.< I don't want to do programming

Well, it may be better to follow the other suggestion for more general instructions for listing these names, then use a LOOKUP to match your names to the list. But the following may help but even if you don't consider "using the VBA editor" to be programming, you will almost certainly need to modify the approach to make it work for you. I'll assume you know roughly how to work with Excel VBA.

1. Open your Workbook

2. Open Excel VBA

3. Create a new Module in the workbook

4. Copy and paste in the VBA code that follows these notes. You may need to fix it if lines are split. You also need to use Tools->References to make a reference to Microsoft ActiveX Data Objects x.y Library, where "x.y" might be 6.0, 2.8 etc.

5. In the code, adjust the line below "'!!!!!!!!!!!!!" dependin gon the name in column A. I may or may not have used the one that you need.

6. In your workbook, let's suppose the names you want to look up are in column A, and you want the manager's names in column B, and row 1 has headings "Name" and "Manager Name"

Then in cell B2, put the following formula:

=ManagerDisplayName(A2)

then propagate that formula down column B in the usual way (i.e. so that Excel adjusts the cell references automatically).

7. Recalculate the sheet.

Here's the VBA:

'----------

Sub MDNtest()

Debug.Print ManagerDisplayName("myname")

End Sub

Function ManagerDisplayName(Name) As String

Dim objConnection As ADODB.Connection

Dim objrootDSE As IADs

Dim strDefaultNamingContext As String

Dim strManagerAttributes As String

Dim objManagerCommand As ADODB.Command

Dim strManagerID As String

Dim strManagerDisplayName As String

Dim objManagerRS As ADODB.Recordset

Dim strManagerScope As String

Dim strPersonAttributes As String

Dim objPersonCommand As ADODB.Command

Dim strPersonFilterPart1 As String

Dim strPersonFilterPart2 As String

Dim objPersonRS As ADODB.Recordset

Dim strPersonScope As String

' Display "" unless we can get the Manager's display name

strManagerDisplayName = ""

On Error GoTo finish

strPersonFilterPart1 = "(&(objectCategory=Person)"

strPersonManagerAttribute = "manager"

strPersonScope = "subtree"

strManagerNameAttribute = "displayName"

strManagerScope = "subtree"

Set objrootDSE = GetObject("LDAP://RootDSE")

strDefaultNamingContext = objrootDSE.Get("DefaultNamingContext")

Set objConnection = CreateObject("ADODB.Connection")

Set objPersonCommand = CreateObject("ADODB.Command")

Set objManagerCommand = CreateObject("ADODB.Command")

objConnection.Provider = "ADsDSOObject"

objConnection.Open "ADS"

objPersonCommand.ActiveConnection = objConnection

objManagerCommand.ActiveConnection = objConnection

' The value in Name should be passed in by Excel from the Worksheet

' Here, we assume it is the "Name" listed in Active Directory

'!!!!!!!!!!!!!

strPersonFilterPart2 = "(cn=" & Name & "))"

objPersonCommand.CommandText = _

"<LDAP://" & strDefaultNamingContext & ">;" & _

strPersonFilterPart1 & strPersonFilterPart2 & ";" & _

strPersonManagerAttribute & ";" & _

strPersonScope

Set objPersonRS = objPersonCommand.Execute

If objPersonRS.RecordCount = 0 Then

' Debug.Print "could not find this person"

Else

If IsNull(objPersonRS(strPersonManagerAttribute)) Then

    ' Debug.Print "no manager"

Else

    strManagerID = objPersonRS(strPersonManagerAttribute)

    objManagerCommand.CommandText = _

      "<LDAP://" & strManagerID & ">;;" & _

      strManagerNameAttribute & ";" & _

      strManagerScope

    Set objManagerRS = objManagerCommand.Execute

    If objManagerRS.RecordCount = 0 Then

      'Debug.Print "could not find manager"

    Else

      If IsNull(objManagerRS(strManagerNameAttribute).Value) Then

        'Debug.Print "Manager name unknown"

      Else

        strManagerDisplayName = objManagerRS(strManagerNameAttribute).Value

      End If

    End If

End If

End If

finish:

Err.Clear

On Error Resume Next

objManagerRS.Close

Set objManagerRS = Nothing

Set objManagerCommand = Nothing

objPersonRS.Close

Set objPersonRS = Nothing

Set objPersonCommand = Nothing

objConnection.Close

Set objConnection = Nothing

Set objrootDSE = Nothing

ManagerDisplayName = strManagerDisplayName

End Function

'----------

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.