Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pulling a list of all users from Active Directory (Word 2003 VBA)

    Hi all,

    I have an application which currently pulls the logged in user's personal attributes (such as phone, email, title, department, etc) from Active Directory into appropriate controls in a user form, at which point the user can edit pertinent data and upon clicking OK it gets saved back to Active Directory. We then populate our letter, memo, fax templates with default author information by gathering the logged in user's attributes from Active Directory again. This works great, but now we want to expand the functionality a bit and I just spent a rather frustrating long weekend working trying to make it happen, with not much luck (I had a Labourious rather than Labour Day!).

    So, what I am trying to do now is call up all users from Active Directory, so that I can then populate a combobox with their names in our letter, memo and fax. That way, while I will default the author information to the logged in user (as we do now), the user can select anyone else from Active Directory as the author, thereby enabling them to select other Authors and not have to manually type their name and relevant data. Once I get that working I will expand that functionality to cc's, etc.

    So I am having difficulty pulling up a complete recordset from Active Directory, with all the attributes I require. I have had to filter before in order to get just the logged in user's attributes, and that worked fine, but the filter I am using now doesn't appear to be giving me a full recordset. I'm not sure if the filter is wrong, if I just can't see everything in the Immediate window, or if there is some limit to the number of records... At any rate, below is the code I am using...

    Public Function GetUserList() As String
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim oRoot As IADs
    Dim oDomain As IADs
    Dim sBase As String
    Dim sFilter As String
    Dim sDomain As String
    Dim sAttribs As String
    Dim sDepth As String
    Dim sQuery As String
    Dim user As IADsUser

    On Error GoTo ErrHandler:

    Set oRoot = GetObject("LDAP://rootDSE")
    'Work in the default domain
    sDomain = oRoot.Get("defaultNamingContext")
    Set oDomain = GetObject("LDAP://" & sDomain)
    sBase = "<" & oDomain.ADsPath & ">"
    sFilter = "(&(objectCategory=person)(objectClass=user)(displ ayName=*))"
    sAttribs = "cn,distinguishedName,displayName"
    sDepth = ADS_SCOPE_SUBTREE

    sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth

    conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"

    Set rs = conn.Execute(sQuery)

    rs.MoveFirst
    While Not rs.EOF
    For Each Field In rs.Fields
    Debug.Print Field
    Next

    Debug.Print ""
    rs.MoveNext
    Wend

    ErrHandler:

    On Error Resume Next
    If Not rs Is Nothing Then
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
    End If

    If Not conn Is Nothing Then
    If conn.State <> 0 Then conn.Close
    Set conn = Nothing
    End If

    Set oRoot = Nothing
    Set oDomain = Nothing

    End Function

    What I seem to get in the Immediate window is a list of three attributes: the common name, then on another line, the distinguished name, then on the last line for each field it says 'Null', which I presume would be the display name. And, it only seems to list a small number of them (for example names starting with N or M). I thought, with the filter I used that it would select all users with a displayname, but that isn't what I'm getting. Any ideas on what I'm doing wrong?

    Any help would be appreciated (especially since I'm supposed to demo this to my boss tmrw!!! Arrggghh!!!)

    Thanks!
    Karina

  2. #2
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling a list of all users from Active Directory (Word 2003 VBA)

    Just an update, I modified my filter line of code to include all the attributes I need to this:

    sAttribs= "cn,distinguishedName,displayName,givenName,sn,Mid dleName,Title,PersonalTitle,telephoneNumber,mail,I nitials,Department,Division,physicalDeliveryOffice Name,Description"

    In doing so, I noticed that the last attribute always displays as 'Null' in the Immediate window, it doesn't matter what the last attribute is (I tried all of them!), it just displays as 'Null'. So, what I did was include an attribute I don't need (Description) at the end of my filter, and that way I get all the attributes that I do need. Kinda silly, if I knew why it was returning 'Null' then I wouldn't have to pull the extra attribute.

    I still can't seem to get it to list all users either...

  3. #3
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling a list of all users from Active Directory (Word 2003 VBA)

    Here I am posting my stupidity and revelations as they occur for all to see! <img src=/S/blush.gif border=0 alt=blush width=15 height=15> So, I did a check to see that the entire recordset was in fact there (using the .RecordCount property) and found that it was correct. And, in the process I learned a lot about the Immediate window...which I feel very sheepish about! It seems that it does have a size limit, and I learned that all the stuff I send to it gets appended. I also figured out that I could clear it by selecting all and deleting...sheesh, don't I feel stupid. Well, no on to the next phase, which is to enumerate this list and add it to the combobox...

  4. #4
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling a list of all users from Active Directory (Word 2003 VBA)

    Here I am...stumped again. I have pulled out a recordset from AD, and now I am trying to populate a combobox with the displayName field. I can get it to print to the Immediate window just fine, but not add the item to the combobox...

    Private Sub UserForm_Initialize()
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim oRoot As IADs
    Dim oDomain As IADs
    Dim sBase As String
    Dim sFilter As String
    Dim sDomain As String
    Dim sAttribs As String
    Dim sDepth As String
    Dim sQuery As String
    Dim user As IADsUser

    On Error GoTo ErrHandler:

    Set oRoot = GetObject("LDAP://rootDSE")
    sDomain = oRoot.Get("defaultNamingContext")
    Set oDomain = GetObject("LDAP://" & sDomain)
    sBase = "<" & oDomain.ADsPath & ">"
    sFilter = "(&(objectCategory=person)(objectClass=user)(displ ayName=Thomas, K*))"
    sAttribs= "cn,distinguishedName,displayName,givenName,sn,Mid dleName,Title,PersonalTitle,telephoneNumber,mail,I nitials,Department,Division,physicalDeliveryOffice Name,Description"
    sDepth = ADS_SCOPE_SUBTREE
    sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth
    conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"
    Set rs = conn.Execute(sQuery)

    rs.MoveFirst
    While Not rs.EOF
    For i = 0 To rs.RecordCount
    frmUserForm1.cboAuthor.AddItem rs.Fields(2).Value
    Next 'i
    rs.MoveNext
    Wend

    rs.Close

    ErrHandler:

    On Error Resume Next
    If Not rs Is Nothing Then
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
    End If

    If Not conn Is Nothing Then
    If conn.State <> 0 Then conn.Close
    Set conn = Nothing
    End If

    Set oRoot = Nothing
    Set oDomain = Nothing

    End Sub

    Any ideas?

  5. #5
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling a list of all users from Active Directory (Word 2003 VBA)

    I managed to get it to work, but only if I place the code in the user form, not if I call it. Obviously something needs to be public, but I'm not sure what. I tried making the rs variable public, but that didn't do it. I'm shooting in the dark here. The code I have now in the user form is as follows:

    Private Sub GetUserList()
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim oRoot As IADs
    Dim oDomain As IADs
    Dim sBase As String
    Dim sFilter As String
    Dim sDomain As String
    Dim sAttribs As String
    Dim sDepth As String
    Dim sQuery As String
    Dim user As IADsUser

    On Error GoTo ErrHandler:

    Set oRoot = GetObject("LDAP://rootDSE")
    'Work in the default domain
    sDomain = oRoot.Get("defaultNamingContext")
    Set oDomain = GetObject("LDAP://" & sDomain)
    sBase = "<" & oDomain.ADsPath & ">"
    'Get record set
    sFilter = "(&(objectCategory=person)(objectClass=user)(exten sionattribute1=*))"
    sAttribs = "cn,distinguishedName,extensionattribute1,givenNam e,sn,MiddleName,Title,PersonalTitle,telephoneNumbe r,mail,Initials,Department,Division,physicalDelive ryOfficeName,Description"
    sDepth = ADS_SCOPE_SUBTREE

    sQuery = sBase & ";" & sFilter & ";" & sAttribs & ";" & sDepth

    conn.Open "Data Source=Active Directory Provider;Provider=ADsDSOObject"

    Set rs = conn.Execute(sQuery)

    rs.MoveFirst
    While Not rs.EOF
    cboAuthor.AddItem rs.Fields(2).Value
    rs.MoveNext
    Wend

    ErrHandler:

    On Error Resume Next
    If Not rs Is Nothing Then
    If rs.State <> 0 Then rs.Close
    Set rs = Nothing
    End If

    If Not conn Is Nothing Then
    If conn.State <> 0 Then conn.Close
    Set conn = Nothing
    End If

    Set oRoot = Nothing
    Set oDomain = Nothing

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •