Solved

Lookup AD user from Excel spreadsheet

Posted on 2009-07-10
15
1,012 Views
Last Modified: 2012-05-07
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
Comment
Question by:aideb
  • 9
  • 6
15 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24822243
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
 
LVL 2

Author Comment

by:aideb
ID: 24823354
Hi Dave,

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

This falls over at the following line.
Set adoRecordSet = AdoCommand.execute
0
 
LVL 2

Author Comment

by:aideb
ID: 24837844
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Author Comment

by:aideb
ID: 24838083
Is anyone else able to offer any help?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24838310
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
 
LVL 2

Author Comment

by:aideb
ID: 24839237
Still get the same error I'm afraid....
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 24845053
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
 
LVL 2

Author Comment

by:aideb
ID: 24847122
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
 
LVL 2

Author Comment

by:aideb
ID: 24848000
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24848062
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
 
LVL 2

Author Comment

by:aideb
ID: 24848407
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
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 500 total points
ID: 24848477
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
 
LVL 2

Author Comment

by:aideb
ID: 24849157
That did the trick.....Many thanks!
0
 
LVL 2

Author Closing Comment

by:aideb
ID: 31602028
Fantastic....all working. Your help is much appreciated.

Cheers

Aidan
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24849290
thx for the grade :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question