Solved

Lookup AD user from Excel spreadsheet

Posted on 2009-07-10
15
1,005 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now