?
Solved

Export usernames & domain groups from Active Directory to excel

Posted on 2008-10-28
1
Medium Priority
?
911 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

616 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