Solved

Lookup AD user from Excel spreadsheet

Posted on 2009-07-10
15
1,000 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

17 Experts available now in Live!

Get 1:1 Help Now