We help IT Professionals succeed at work.

VBA LDAP Query to retrieve all members of a group in active directory

hqdev
hqdev asked
on
Hi experts,

I need to retrieve all members of a group through VBA in Excel.
I can find all users with the code attached.
But I don't know how to retrieve only users from a particular group.
I've tried adding "memberOf='cn=SPC'" in the where clause (SPC is one of the existing group) but I get no records.
The AdsPath of the group is CN=SPC,OU=SPC,OU=Web ( Site ),OU=Applications,OU=Collaborations,DC=hq,DC=intra

Thanks
Const ADS_SCOPE_SUBTREE = 2

    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand = CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandText = "SELECT mail,sAMAccountName,displayName,showInAddressBook, ADsPath FROM 'LDAP://ou=Organisations,dc=HQ,dc=intra' WHERE objectCategory='user'"
    objCommand.Properties("Page Size") = 2000
    objCommand.Properties("Sort On") = "displayName"
    Set objRecordSet = objCommand.Execute

Open in new window

Comment
Watch Question

Author

Commented:
Finally I found how:

 Const ADS_SCOPE_SUBTREE = 2

    Set objConnection = CreateObject("ADODB.Connection")
    Set objCommand = CreateObject("ADODB.Command")
    objConnection.Provider = "ADsDSOObject"
    objConnection.Open "Active Directory Provider"
    Set objCommand.ActiveConnection = objConnection
    objCommand.CommandText = "SELECT mail,sAMAccountName,displayName,showInAddressBook, ADsPath, memberOf FROM 'LDAP://dc=HQ,dc=intra' WHERE objectCategory='group' AND objectClass='group' AND sAMAccountName='SPC'"
    objCommand.Properties("Page Size") = 2000
    objCommand.Properties("Sort On") = "displayName"
    Set objRecordset = objCommand.Execute
   
    Set Group = GetObject(objRecordset.fields("ADsPath"))
   
    For Each member In Group.Members
   
        Dim nn, nm  As String
         nn = member.displayname
         nm = member.mail
       
   
    Next

developedtester,  I had found the solution just before your post, but one of your link is showing a solution similar to mine so I'll give you the points.
That's very kind.  Thanks a bunch!