Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1110
  • Last Modified:

Lookup AD user from Excel spreadsheet

I am looking to return the user login name from a (long) list of user information I have in a spreadsheet.

I have the following information contained in the spreadsheet;
Full LDAP String of users e.g. LDAP://Server.domain.com/CN=Smith, John,OU= etc etc
GUID of users e.g. {123213-4324243432 etc
CN of users e.g. CN=Smith, John,OU= etc

I would like to be able to be able to run a macro on the column to return the user login name.

Can anyone suggest how this can be done?
0
aideb
Asked:
aideb
  • 9
  • 6
2 Solutions
 
Dave BrettCommented:
Hi,

Can you pls test if this code returns a valid login name for a user's common name - I af offline from my work connection so I can't test this function which I have brorrowed fro Rob Sampson. You will need to change the
[a1].Value
to the location of a common name in your file

If the code returns the login name as desired that I will update the main part of the excel code so that loops through all your common names.

Cheers
Dave

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

Open in new window

0
 
aidebAuthor Commented:
Hi Dave,

I have tried this but get
System Error &H80040E37 (-2147217865)

This falls over at the following line.
Set adoRecordSet = AdoCommand.execute
0
 
aidebAuthor Commented:
Dave,

The value in the cell is
CN=Stewart\, Bernadette,OU=FDS-CorpDC-Users,OU=London,OU=Corp Users,OU=Corp,OU=Users,OU=User Accounts,DC=user,DC=ad,DC=company,DC=org,DC=uk

but I see that the strDNSDomain is being set to "CN=Stewart/DC=CN=Stewart" which doesn't look right?

Cheers

Aidan
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
aidebAuthor Commented:
Is anyone else able to offer any help?
0
 
Dave BrettCommented:
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

Open in new window

0
 
aidebAuthor Commented:
Still get the same error I'm afraid....
0
 
Dave BrettCommented:
Hi,
When I modiied the function to remove the "/" substitution this worked for me on the DistinguishedName
Pls try this with
CN=Stewart\, Bernadette,OU=FDS-CorpDC-Users,OU=London,OU=Corp Users,OU=Corp,OU=Users,OU=User Accounts,DC=user,DC=ad,DC=company,DC=org,DC=uk
in cell A1
Cheers
Dave

Sub Test()
    MsgBox Get_LDAP_User_Properties("user", "distinguishedname", [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.
 
' Otherwise we just connect to the default domain
    Set objRootDSE = GetObject("LDAP://RootDSE")
    strDNSDomain = objRootDSE.Get("defaultNamingContext")
    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

Open in new window

0
 
aidebAuthor Commented:
Now, that works!

Thanks for that. I will have a go at trying to get it to read in a column and outputting to another. I will let you know how I get on.

Cheers

Aidan
0
 
aidebAuthor Commented:
Modified the inital sub to traverse the column and output to another. Works a treat.

I am going to be running this on a number of other spreadsheets so I would like to be able to keep the workbook open which contains the macro and then run it on the workbook that has focus. I have seen that done before....any ideas how to do it?

Cheers

Aidan

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

Open in new window

0
 
Dave BrettCommented:
Hi Aidan
The code as is will run on the Activeworkbook and activesheet by default when it is run
I add all my frequentlty used codes to a workbook that I then save as an addin, so that they are automatically available in the background
Cheers
Dave
 
0
 
aidebAuthor Commented:
hmmm for some reason it wont run like that.

I have placed the code in a module, assigned the macro to a shortcut key  and saved it as an addin.

I have then opened another worksheet and added my new addin then executed the shortcut key. The code still reads from the previous data....any pointers?

Sorry, new to macros and addins.
0
 
Dave BrettCommented:
No probs, does it help if you explicitly reference the activeworkbook and worksheet as below?

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

Open in new window

0
 
aidebAuthor Commented:
That did the trick.....Many thanks!
0
 
aidebAuthor Commented:
Fantastic....all working. Your help is much appreciated.

Cheers

Aidan
0
 
Dave BrettCommented:
thx for the grade :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now