Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Lookup AD user from Excel spreadsheet

Posted on 2009-07-10
15
Medium Priority
?
1,066 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

721 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