Question

Locked

Load Excel with users from specific AD OU???

By John.Key1 ·
I have this script downloaded from the web. I know the script gets all CN, LastName, FirstName, and DisplayName of users within the Domain. Can anyone tell me how to set it specifically to my OU - I do not want everyone else information in the domain - just mine.


Option Explicit
Const ADS_SCOPE_SUBTREE = 2
Sub LoadUserInfo()
Dim x, objConnection, objCommand, objRecordSet, oUser
Dim sht As Worksheet

' get domain
Dim oRoot
Set oRoot = GetObject("LDAP://rootDSE")
Dim sDomain
sDomain = oRoot.Get("defaultNamingContext")
Dim strLDAP
strLDAP = "LDAP://" & sDomain

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") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = "SELECT adsPath FROM '" & strLDAP & "' WHERE objectCategory='person'AND objectClass='user'"
Set objRecordSet = objCommand.Execute

x = 2
Set sht = ThisWorkbook.Worksheets("Sheet1")
With sht
' Clear and set Header info
.Cells.Clear
.Cells(1, 1).Value = "CN"
.Cells(1, 2).Value = "Last Name"
.Cells(1, 3).Value = "First Name"
.Cells(1, 4).Value = "Display Name"
Do Until objRecordSet.EOF
Set oUser = GetObject(objRecordSet.Fields("aDSPath"))
.Cells(x, 1).Value = Replace(oUser.Name, "CN=", "")
.Cells(x, 2).Value = oUser.SN
.Cells(x, 3).Value = oUser.givenName
.Cells(x, 4).Value = oUser.displayName
x = x + 1
objRecordSet.MoveNext
Loop

End With

End Sub

Private Sub Workbook_Open()
LoadUserInfo
End Sub

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Share your knowledge
Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums