Consulting

Results 1 to 8 of 8

Thread: Populate a Sheet with Active Directory Computers

  1. #1
    VBAX Regular
    Joined
    Nov 2009
    Posts
    11
    Location

    Populate a Sheet with Active Directory Computers

    Hi
    I'm looking to add Computers from AD to a sheet, any ideas on how this is possible? I've seen code for AD but I can't find any relating to the computer objects.
    Is it also possible to find out where each computer is in the AD tree?
    Thanks!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    What have you tried?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Nov 2009
    Posts
    11
    Location
    Quote Originally Posted by xld
    What have you tried?
    Well not a lot so far, all google returns in the search is user related AD queries. I wouldn't know where to start with this.....

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    I don't use AD, but I found this code, but not sure how relevant it is

    [vba]

    Sub TestHarness
    Set objTrans = CreateObject("NameTranslate")

    objTrans.Set 3, "<domainname>\<servername>$"
    strComputerDN = objTrans.Get(1)

    Set objComputer = GetObject("LDAP://" & strComputerDN)
    colGroups = objComputer.MemberOf

    For i = 0 To UBound(colGroups)
    ActiveCell.Value = GetGroup(colGroups(i))
    ActiveCell.Offset(1, 0).Select
    Next
    End Sub

    Function GetGroup(strGroup) As String
    z = Split(strGroup, ",")
    If Left(z(0), 3) = "CN=" Then
    GetGroup = Right(z(0), Len(z(0)) - 3)
    Else
    GetGroup = ""
    End If
    End Function
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Nov 2009
    Posts
    11
    Location
    Thanks for the reply.
    I've amended that so that the location of the computer entered is added to a listbox, however I'd like to be able for it to run through Active Directory and add the Computer names too, not just the one entered.
    Here is my code so far:

    [VBA]Private Sub CommandButton19_Click()
    Set objTrans = CreateObject("NameTranslate")

    objTrans.Set 3, "domain\computer$"
    strComputerDN = objTrans.Get(1)

    Set objComputer = GetObject("LDAP://" & strComputerDN)


    ListBox4.AddItem objComputer.Location



    End Sub[/VBA]

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Don't know enough about AD to help I am afraid.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    This will populate a worksheet with the names:
    Private Sub GetComputerList()
        Const ADS_SCOPE_SUBTREE = 2
        Set objRoot = GetObject("LDAP://rootDSE")
       'Work in the default domain
       strDomain = objRoot.Get("defaultNamingContext")
    
        Set objConnection = CreateObject("ADODB.Connection")
        Set objCommand = CreateObject("ADODB.Command")
        objConnection.Provider = "ADsDSOObject"
        objConnection.Open "Active Directory Provider"
        
        Set objCommand.ActiveConnection = objConnection
        objCommand.CommandText = _
            "SELECT Name FROM 'LDAP://" & strDomain & "' WHERE objectClass='computer'"
        objCommand.Properties("Page Size") = 1000
        objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
        Set objRecordset = objCommand.Execute
        ActiveSheet.Range("A2").CopyFromRecordset objRecordset
        objRecordset.Close
        ObjConnection.Close
    End Sub
    You would need to alter it to use GetRows and then transpose the array, or loop and use AddItem in order to populate your listbox.

  8. #8
    VBAX Regular
    Joined
    Nov 2009
    Posts
    11
    Location
    Thats done it, thanks! Just gotta sort it out now

Posting Permissions

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