Solved

Export usernames & domain groups from Active Directory to excel

Posted on 2008-10-28
1
859 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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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 steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now