Pull Active Directory Group Information into Excel

Hello all! I'm hoping someone can help me with this as I'm having a horrible time. What I am wanting to do is pull lists of members out of AD security groups we have and list them in an excel spreadsheet.


For instance say we have a security group called GG_OurSecurity in Active Directory.

I want to pull a list of all ID's (we use ID's not names in our Active Directory) and put them in an excel list. I would like to be able to extract each group we have all into one spreadsheet. For instance column A would have a title of GG_Oursecurity and underneath would be all members. Than the next column would be GG_Thenextsecurity group and list those members.

Is this possible? Am I making sense? Any help you could give would be much appreciated. I'm going crazy!!!

It is certainly possible (using ADSI or LDAP queries for example). What exactly are your IDs? Are they SAMAccountNames or just the SID of the user?

So are those your user ids for logging into the network?

See if this function works for you:

Function GetGroupUsers(strGroupName As String) As String

    Dim objGroup, objDomain, objMember
    Dim strMemberList As String, strDomain As String
    
    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 & vbCrLf
    Next objMember
    
    GetGroupUsers = strMemberList

End Function

That function just returns a list of members for a group name you specify. Since I have no idea how your worksheet is laid out or how you intend to supply the group name(s), I can't provide specific code for that.

You need to open the VBEditor (Alt+f11), select your workbook in the Project Explorer window (top left, looks a bit like a windows explorer window), right-click and choose Insert module. Then paste this code in - I have added comments so that you can hopefully see what you need to alter for your own groups:

Option Explicit
Sub OutputGroupMembership()
    Dim varGroups, varItem
    Dim wksOut As Worksheet
    Dim rngOut As Range
    Dim varMembers
    Dim lngIndex As Long
    Dim strMemberlist As String
    
    ' this is the worksheet where the list will go
    ' adjust as required
    Set wksOut = ActiveSheet
    With wksOut
        ' add titles
        .Range("A1").Value = "Group name"
        .Range("B1").Value = "Member ID"
        ' first output cell
        Set rngOut = .Range("A2")
    End With
    ' this is the list of groups you want to get the members for
    varGroups = Array("Group 1", "Group 2")
    
    For Each varItem In varGroups
        ' output group name
        With rngOut
            .Value = varItem
            .Font.Bold = True
        End With
        
        ' move down a row
        Set rngOut = rngOut.Offset(1)
        ' 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.Offset(, 1).Value = varMembers(lngIndex)
            Set rngOut = rngOut.Offset(1)
        Next lngIndex
    Next varItem
        
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

Originally Posted by romperstomper

You need to open the VBEditor (Alt+f11), select your workbook in the Project Explorer window (top left, looks a bit like a windows explorer window), right-click and choose Insert module. Then paste this code in - I have added comments so that you can hopefully see what you need to alter for your own groups:

Option Explicit
Sub OutputGroupMembership()
    Dim varGroups, varItem
    Dim wksOut As Worksheet
    Dim rngOut As Range
    Dim varMembers
    Dim lngIndex As Long
    Dim strMemberlist As String
    
    ' this is the worksheet where the list will go
    ' adjust as required
    Set wksOut = ActiveSheet
    With wksOut
        ' add titles
        .Range("A1").Value = "Group name"
        .Range("B1").Value = "Member ID"
        ' first output cell
        Set rngOut = .Range("A2")
    End With
    ' this is the list of groups you want to get the members for
    varGroups = Array("Group 1", "Group 2")
    
    For Each varItem In varGroups
        ' output group name
        With rngOut
            .Value = varItem
            .Font.Bold = True
        End With
        
        ' move down a row
        Set rngOut = rngOut.Offset(1)
        ' 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.Offset(, 1).Value = varMembers(lngIndex)
            Set rngOut = rngOut.Offset(1)
        Next lngIndex
    Next varItem
        
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

Thank you so much for all your help. I will play around with this and see what I can do. IT WORKS GREAT!!!! I am so excited!!! Thank you so much. You just made my life so much easier!!

@ pbos

Welcome to the forum.

Unfortunately you have to start your own thread according the forum rules.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Hello jam92102,

You are saying that "GG_OurSecurity" is a folder name and the members are the names of files in that folder?