Active Directory Data Using Macro

[Vincent.Eymard]

Hi Everyone,
Got answers from my previous inquiries and Im back again to seek for more help ^^

This time around what i want to do is to be able to pull all the groups a LAN ID is a member of.
It's like, I will just put the LAN ID on one cell and when I run a Macro, it will return the list of Groups this ID is a member of.


I have gotten help from romperstomper on my previous post.
Hope to get help again.
(http://www.excelforum.com/excel-prog...ing-macro.html)


Thanks again

[Vincent.Eymard]

I'm sure this can be done.

[Vincent.Eymard]

Looking forward to your Inputs ^^
Thanks in advance.

[Vincent.Eymard]

Please please please.. hope someone out there could help..

[snb]

You'd better look at the attachment you posted.

[Vincent.Eymard]

You'd better look at the attachment you posted.

Hi snb. I viewed the attachment and looks okay.
I'm not sure what I'm missing..

[Vincent.Eymard]

I hope to get luck and find help today ^^

[Vincent.Eymard]

Still no luck with this one.. (

[romperstomper]

Try this:

Option Explicit
Sub GetGroupData()
    GetGroupMembership Sheet1.Range("D2").Value, Sheet1.Range("A8")
End Sub

Sub GetGroupMembership(strUser As String, rngOut As Range)

Dim objConnection, objCommand, objRecordSet, objUser, objRootDSE, objMember
Dim strLine, arrGroup

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

Set objRootDSE = GetObject("LDAP://RootDSE")
objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") & _
                            "' WHERE objectClass='user' And name='" & strUser & "'"
Set objRootDSE = Nothing

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Timeout") = 600
objCommand.Properties("Cache Results") = False

Set objRecordSet = objCommand.Execute
rngOut.CurrentRegion.Offset(1).ClearContents
While Not objRecordSet.EOF
    Set objUser = GetObject(objRecordSet.Fields("aDSPath"))
    For Each objMember In objUser.GetEx("memberOf")
        strLine = Mid(objMember, 4, 330)
        arrGroup = Split(strLine, ",")
        rngOut.Value = arrGroup(0)
        Set rngOut = rngOut.Offset(1)
    Next
    ' Additional section to find the primary group.
    If objUser.primaryGroupID = 513 Then
       rngOut.Value = "Domain Users"
    Else
        If objUser.primaryGroupID = 515 Then
           rngOut.Value = "Domain Computers"
        Else
            rngOut.Value = "Maybe a Domain Controller"
        End If
    End If

    Set objUser = Nothing
    objRecordSet.MoveNext
Wend

objConnection.Close
Set objRecordSet = Nothing
Set objCommand = Nothing
Set objConnection = Nothing

End Sub

[Vincent.Eymard]

Hi again romperstomper!
Thank you very much for your help again.
Tried the script but when I click the button nothing seems to happen.
Not sure what to do

[romperstomper]

Did you assign the getGroupData macro to the button? If not, then no it won't do anything.

Please don't quote entire posts when replying to them - it serves no purpose.

[Vincent.Eymard]

Hi again romperstomper,
Yup I assigned the button to the macro getGroupData, but when i click nothing seems to happen.
Unlike with the other script that you helped me with.

[romperstomper]

Then I guess the login id you entered is incorrect, because the script works fine for me.

[Vincent.Eymard]

I provided my own logon ID.
Hmnnn... Still trying to figure out what might be the problem..

[Vincent.Eymard]

It's even more weird this time, when I use a LAN ID such as myself I dont get any result, but when I used the generic ID "guest" there was a result (attached).
Hope someone can help.

Thanks again.

[Vincent.Eymard]

Still couldn't get this to work. Hope somebody could help me out.
Thanks in advance. ^^

[Vincent.Eymard]

Hope to get help.
I haven't solved it still, when I use a LAN ID such as myself I dont get any result, but when I used the generic ID "guest" there was a result (attached).
Hope someone can help.

Thanks again.

[Vincent.Eymard]

Still looking and seeking for help..

[Vincent.Eymard]

Anyone got the chance to look at this? ^^
Hope to find help.

Thanks

[jlynn001]

Hi Vincent,
Sorry I know it has been along time since this post. I did get the script working right however. I entered the ID in the same format as it was in the first extraction. For example the ID's came back as Doe, John. So if you enter it in the same format in the second extraction it does work. Your other option would be to change the line below.

objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") & _
"' WHERE objectClass='user' And name='" & strUser & "'"

to

objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & objRootDSE.Get("defaultNamingContext") & _
"' WHERE objectClass='user' And SAMAccountName='" & strUser & "'"

Then you can use jdoe rather then doe, John

Jlynn001