Solved

Export usernames & domain groups from Active Directory to excel

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Suggested Solutions

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 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

18 Experts available now in Live!

Get 1:1 Help Now