+ Reply to Thread
Results 1 to 17 of 17

Pull Active Directory Group Information into Excel

  1. #1
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    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!!!
    Last edited by jam92102; 03-30-2010 at 01:27 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    re: Pull Active Directory Group Information into Excel

    Hello jam92102,

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

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Pull Active Directory Group Information into Excel

    Quote Originally Posted by Leith Ross View Post
    Hello jam92102,

    You are saying that "GG_OurSecurity" is a folder name and the members are the names of files in that folder?
    My wording maybe off. In this case we have a network share. In order to gain access to that networkshare your ID in active directory has to be a member of that group (in this case GG_Oursecurity). I'm wanting to pull a list of all the members in that GG_Oursecurity group.

    Does that make sense?

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pull Active Directory Group Information into Excel

    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?
    Remember what the dormouse said
    Feed your head

  5. #5
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Pull Active Directory Group Information into Excel

    Quote Originally Posted by romperstomper View Post
    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?
    They are generated from a system called NACOS? Basically the 2nd letter is always a Z and then its just a 6 digit group of numbers and letters. Such as WZZC3W or ZZN41J.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pull Active Directory Group Information into Excel

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

  7. #7
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Pull Active Directory Group Information into Excel

    Quote Originally Posted by romperstomper View Post
    So are those your user ids for logging into the network?
    Correct. Those are user login id's.

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pull Active Directory Group Information into Excel

    See if this function works for you:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Pull Active Directory Group Information into Excel

    Quote Originally Posted by romperstomper View Post
    See if this function works for you:
    Please Login or Register  to view this content.
    Ok, I thought I knew what I was doing but I don't. How do I get this code into the worksheet? I tried pasting it once I clicked on the visual basic link but could never get it to work. I appreciate all the help!!

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pull Active Directory Group Information into Excel

    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.

  11. #11
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Pull Active Directory Group Information into Excel

    Quote Originally Posted by romperstomper View Post
    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.
    Don't get me wrong, I really appreciate your help. I just don't know where to insert the name of my group and how I actually put it in to excel. For instance, do I record a macro and paste the code somewhere in there? Sorry for all the noob questions.

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pull Active Directory Group Information into Excel

    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:
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    06-10-2009
    Location
    Indiana, US
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Pull Active Directory Group Information into Excel

    Quote Originally Posted by romperstomper View Post
    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:
    Please Login or Register  to view this content.
    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!!
    Last edited by jam92102; 03-31-2010 at 08:01 AM.

  14. #14
    Registered User
    Join Date
    12-17-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Pull Active Directory Group Information into Excel

    Romperstomper - This is great - and perhaps a bit more logical than trying to pull data from the AD (powershell) and trying to then get the info, which is what I have been looking into the last two weeks.

    My aim was to have a tool (either a VB app or spread sheet) that would get this info "live", and not rely on pre-gathering the data.

    What I would find useful is if you can get it to look at a specific OU where the groups are, and lists them, that way you can then tick what you want to report on.

    In addition we would like to assigned the groups to "managers" that way we can pull off custom reports and report on who these owners are as well?

    Any thoughts in this direction appreciated!

  15. #15
    Registered User
    Join Date
    10-02-2013
    Location
    Ede
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Pull Active Directory Group Information into Excel

    Hi, is it also possible to create a matrix with left the users, right al groups and a color or "y" by all users who are in the specific group?

    group1 group2 group3
    user1 y
    user2 y
    user3 y y y

  16. #16
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Pull Active Directory Group Information into Excel

    @ 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.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  17. #17
    Registered User
    Join Date
    02-20-2014
    Location
    Denver, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Pull Active Directory Group Information into Excel

    Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1