List Groups a User is a Member of - Active Directory

**tomlancaster** · 04-12-2011, 08:13 AM

Hello, I found some code on the internet and tinkered with it to list all the members of a group. However, I want to reverse the code so it shows all the groups a list of members are in.

E.g.

User1 , Group1
User1 , Group 2
User2 , Group 2
User3 , Group3
Etc

The code I use to get Memeber of Groups is appended below. Please could someone help me "reverse" the result.

Many thanks
Tom

Sub OutputGroupMembership()
    Dim varGroups, varItem
    Dim wksOut As Worksheet
    Dim rngOut As Range
    Dim varMembers
    Dim lngIndex As Long
    Dim strMemberlist As String
        Worksheets("GROUPS").Activate
    ' this is the worksheet where the list will go
    ' adjust as required
    Set wksOut = ActiveSheet
    With wksOut
        ' first output cell
        Set rngOut = .Range("A14")
    End With
    ' this is the list of groups you want to get the members for
    
          varGroups = Range("MAIN!A20:A39").Value

      'Looping structure to look at array.
      For i = 1 To UBound(varGroups)
      Next
    
    For Each varItem In varGroups
        ' output group name
        With rngOut
            .Value = varItem
        End With
        ' move down a row
        Set rngOut = rngOut.Offset(0)
        ' get member list
        ' returned as a comma separated list
        strMemberlist = GetGroupUsers(varItem)
        ' split list into an array
        varMembers = Split(strMemberlist, ",")
        ' loop and output member IDs
        
        For lngIndex = LBound(varMembers) To UBound(varMembers)
        rngOut.Value = varItem
            rngOut.Offset(, 1).Value = varMembers(lngIndex)
            Set rngOut = rngOut.Offset(1)
        Next lngIndex

    Next varItem
    With wksOut
        Rw = .Cells.Rows.Count
        Range(Cells(Rw, "A").End(3)(2), Cells(Rw, "A")).EntireRow.Hidden = True
    End With
End Sub
Function GetGroupUsers(ByVal strGroupName As String) As String

    Dim objGroup, objDomain, objMember
    Dim strMemberlist As String, strDomain As String
    On Error Resume Next
    Set objDomain = GetObject("LDAP://rootDse")
    strDomain = objDomain.Get("dnsHostName")
    
    Set objGroup = GetObject("WinNT://" & strDomain & "/" & strGroupName & ",group")
    
    For Each objMember In objGroup.Members
        strMemberlist = strMemberlist & "," & objMember.Name
    Next objMember
    ' strip off the leading comma
    GetGroupUsers = Mid$(strMemberlist, 2)

End Function

**pike** · 04-13-2011, 04:02 AM

Hi tomlancaster
if the values are in coulmns a & b
then

Sub testv()
Dim a, b(), i As Long, n As Long, temp As String, e
With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 2)
    a = .Value
   End With
ReDim b(1 To UBound(a, 1), 1 To 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1)
        If Not .exists(a(i, 2)) Then
            n = n + 1
            b(n, 1) = a(i, 2)
            .Add a(i, 2), n
        End If
        b(.Item(a(i, 2)), 2) = b(.Item(a(i, 2)), 2) & _
                IIf(b(.Item(a(i, 2)), 2) <> "", ",", "") & a(i, 1)
    Next
    .RemoveAll
    For i = 1 To n
        For Each e In Split(b(i, 2), ",")
            If Not .exists(e) Then
                temp = temp & "," & e
                .Add e, Nothing
            End If
        Next
        b(i, 2) = Mid$(temp, 2)
        temp = ""
        .RemoveAll
    Next
End With
Range("e1").Resize(n, 2).Value = b

End Sub

**tomlancaster** · 04-13-2011, 04:10 PM

Hi, thanks for your reply. Sorry but I don't think I've explained myself correctly.

The script I've posted looks at a group in Active Directory and lists who is a member of the group.

What I would like to do is look at members and list what groups they are a member of.

Is this possible?

Thanks,
Tom