Solved

Export usernames & domain groups from Active Directory to excel

Posted on 2008-10-28
1
887 Views
Last Modified: 2013-12-24
I borrowed the code below from a previous question/solution post that works great. I would, however, like to also list the username as well. Right now, it lists the user's name and the domaing group. Example:
User's Name             Domain Group
Hutson, Phoebe        All Users
Hutson, Phoebe       Test Group
Joy, Rob                   All Users
Joy, Rob                   Test Group

I'd like it give me the following:
User's Name             Username (network ID)      Domain Group
Hutson, Phoebe         PHutson                                All Users
Hutson, Phoebe        PHutson                                Test Group
Joy, Rob                   Rojo                                      All Users
Joy, Rob                   Rojo                                       Test Group

Thanks in advance for your help!

See code below:

On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2

Dim objGroup, objExcel, iRow, strUser
'Set objGroup = GetObject("LDAP://cn=nameofgroup,ou=Other Groups and Accounts - Public,dc=MyDomain,dc=com")
strGroup="src*"

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

objCommand.Properties("Page Size") = 64000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
    "SELECT Name, adSPath FROM 'LDAP://DC=BHCS,DC=pvt' WHERE objectCategory='group' " & _
        "AND Name='" & strGroup & "'"

Set objRecordSet = objCommand.Execute

Set objExcel = CreateObject("Excel.Application")
With objExcel
  .SheetsInNewWorkbook = 1
  .Workbooks.Add
  .Visible = True
 .Worksheets.Item(1).Name = objRecordSet.Fields("Name").Value
  irow=1

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
  Set objGroup = GetObject(objRecordSet.Fields("adsPath").Value)
  For Each strUser in objGroup.Member
     Set objUser =  GetObject("LDAP://" & strUser)
    .Cells(iRow,1) = objUser.CN
    .Cells(iRow, 2) = objRecordSet.Fields("Name").Value
    irow=irow + 1
  Next
  set objGroup=Nothing
  objRecordSet.MoveNext
Loop

 .Columns(1).entirecolumn.autofit
End With

Set objExcel = Nothing
Set objGroup = Nothing
0
Comment
Question by:snazzy129
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 23

Accepted Solution

by:
irudyk earned 500 total points
ID: 22830299
Try something like the following code
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2
 
Dim objGroup, objExcel, iRow, strUser
'Set objGroup = GetObject("LDAP://cn=nameofgroup,ou=Other Groups and Accounts - Public,dc=MyDomain,dc=com")
strGroup="src*"
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 64000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
objCommand.CommandText = _
    "SELECT Name, adSPath FROM 'LDAP://DC=BHCS,DC=pvt' WHERE objectCategory='group' " & _
        "AND Name='" & strGroup & "'"
 
Set objRecordSet = objCommand.Execute
 
Set objExcel = CreateObject("Excel.Application")
With objExcel
  .SheetsInNewWorkbook = 1
  .Workbooks.Add
  .Visible = True
 .Worksheets.Item(1).Name = objRecordSet.Fields("Name").Value
  irow=1
 
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
  Set objGroup = GetObject(objRecordSet.Fields("adsPath").Value)
  For Each strUser in objGroup.Member
     Set objUser =  GetObject("LDAP://" & strUser)
    .Cells(iRow,1) = objUser.CN
    .Cells(iRow,2) = objUser.sAMAccountName
    .Cells(iRow, 3) = objRecordSet.Fields("Name").Value
    irow=irow + 1
  Next
  set objGroup=Nothing
  objRecordSet.MoveNext
Loop
 
 .Columns(1).entirecolumn.autofit
End With
 
Set objExcel = Nothing
Set objGroup = Nothing

Open in new window

0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question