Lookup AD user from Excel spreadsheet

Sub Test()
    MsgBox Get_LDAP_User_Properties("user", "cn", [a1].Value, "samAccountName")
End Sub
 
Function Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
 
' This is a custom function that connects to the Active Directory, and returns the specific
' Active Directory attribute value, of a specific Object.
' strObjectType: usually "User" or "Computer"
' strSearchField: the field by which to seach the AD by. This acts like an SQL Query's WHERE clause.
'                         It filters the results by the value of strObjectToGet
' strObjectToGet: the value by which the results are filtered by, according the strSearchField.
'                         For example, if you are searching based on the user account name, strSearchField
'                         would be "samAccountName", and strObjectToGet would be that speicific account name,
'                         such as "jsmith".  This equates to "WHERE 'samAccountName' = 'jsmith'"
' strCommaDelimProps: the field from the object to actually return.  For example, if you wanted
'                         the home folder path, as defined by the AD, for a specific user, this would be
'                         "homeDirectory".  If you want to return the ADsPath so that you can bind to that
'                         user and get your own parameters from them, then use "ADsPath" as a return string,
'                         then bind to the user: Set objUser = GetObject("LDAP://" & strReturnADsPath)
 
' Now we're checking if the user account passed may have a domain already specified,
' in which case we connect to that domain in AD, instead of the default one.
    If InStr(strObjectToGet, "\") > 0 Then
        arrGroupBits = Split(strObjectToGet, "\")
        strDC = arrGroupBits(0)
        strDNSDomain = strDC & "/" & "DC=" & Replace(Mid(strDC, InStr(strDC, ".") + 1), ".", ",DC=")
        strObjectToGet = arrGroupBits(1)
    Else
        ' Otherwise we just connect to the default domain
        Set objRootDSE = GetObject("LDAP://RootDSE")
        strDNSDomain = objRootDSE.Get("defaultNamingContext")
    End If
 
    strBase = "<LDAP://" & strDNSDomain & ">"
    ' Setup ADO objects.
    Set adoCommand = CreateObject("ADODB.Command")
    Set ADOConnection = CreateObject("ADODB.Connection")
    ADOConnection.Provider = "ADsDSOObject"
    ADOConnection.Open "Active Directory Provider"
    adoCommand.ActiveConnection = ADOConnection
 
 
    ' Filter on user objects.
    'strFilter = "(&(objectCategory=person)(objectClass=user))"
    strFilter = "(&(objectClass=" & strObjectType & ")(" & strSearchField & "=" & strObjectToGet & "))"
 
    ' Comma delimited list of attribute values to retrieve.
    strAttributes = strCommaDelimProps
    arrProperties = Split(strCommaDelimProps, ",")
 
    ' Construct the LDAP syntax query.
    strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
    adoCommand.CommandText = strQuery
    ' Define the maximum records to return
    adoCommand.Properties("Page Size") = 100
    adoCommand.Properties("Timeout") = 30
    adoCommand.Properties("Cache Results") = False
 
    ' Run the query.
    Set adoRecordset = adoCommand.Execute
    ' Enumerate the resulting recordset.
    strReturnVal = ""
    Do Until adoRecordset.EOF
        ' Retrieve values and display.
        For intCount = LBound(arrProperties) To UBound(arrProperties)
            If strReturnVal = "" Then
                strReturnVal = adoRecordset.Fields(intCount).Value
            Else
                strReturnVal = strReturnVal & vbCrLf & adoRecordset.Fields(intCount).Value
            End If
        Next
        ' Move to the next record in the recordset.
        adoRecordset.MoveNext
    Loop
 
    ' Clean up.
    adoRecordset.Close
    ADOConnection.Close
    Get_LDAP_User_Properties = strReturnVal
 
End Function

Select allOpen in new window

Hi,

This looks like the full distinguished name rather than the common name. I will test the code at work tomorrow  I'm in Oz :)

pls try 

MsgBox
 Get_LDAP_User_Properties
(
"user"
,
 
"distinguishedName

"
,
 
[
a1
].
Value
,
 
"samAccountName"
)

Cheers

Dave

Select allOpen in new window

Sub Test()
Dim RowNumber As Range, RowTarget As Range
 
For Each RowNumber In Range("B:B").SpecialCells(xlCellTypeConstants)
RowNumber.Offset(0, 4).Value = Get_LDAP_User_Properties("user", "distinguishedname", RowNumber.Value, "samAccountName")
 
Next
End Sub

Select allOpen in new window