Active Directory Queries in Excel/VBA

Here’s the problem. You need to do some reconciliation of group members in an active directory group – or you want to confirm a user should have access to some particular parts of your spreadsheet (perhaps you’ve customised a dashboard for different types of users).

This is a problem that I’ve come up against a number of times in my career. In a previous role I needed to reconcile members of an AD group. I needed the report of everyone in the AD group. To get this I relied on a technology partner (which goes by a three letter initialisation, and used to make machines for international business…) who I spent 4-6 hours in teleconferences with, over a period of two months, and who in the end finally managed to provide me with this data.

Over the same period however, I needed to actually do the reporting. So, I pull out my trusty excel toolkit to do the job. It took a couple of hours, and I had a fully customisable system for getting me the information I needed. (more time doing, less time meeting, can be really productive sometimes!)

First requirment. You need to be on the same network / have access to the AD domain controllers.

Second requirement. Excel / VBA.

The code:

Function GetAdsProp(ByVal searchString As String, returnFields As String) As Collection
    'This function originally taken from here: https://www.remkoweijnen.nl/blog/2007/11/01/query-active-directory-from-excel/
    'the function returns a collection of comma-delmited strings containing the requested return fields.
    ' Get the domain string ("dc=domain, dc=local")
    Dim strDomain As String
    Dim valueSet As Collection
    Set valueSet = New Collection
    strDomain = GetObject("LDAP://rootDSE").Get("defaultNamingContext")

    ' ADODB Connection to AD
    Dim objConnection As ADODB.Connection
    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open "Provider=ADsDSOObject;"

    ' Connection
    Dim objCommand As ADODB.Command
    Set objCommand = CreateObject("ADODB.Command")
    objCommand.ActiveConnection = objConnection

    ' Search the AD recursively, starting at root of the domain
    'intent here is to be passed the entire LDAP search string, to enable different types of queries using the one function
    'return fields should be a comma delimited list of valid LDAP fields
    'Search string should be in the LDAP query format: http://www.selfadsi.org/ldap-filter.htm
    objCommand.CommandText = ";" & searchString & ";" & returnFields & ";subtree"

    ' RecordSet
    Dim objRecordSet As ADODB.Recordset
    Set objRecordSet = objCommand.Execute

    If objRecordSet.RecordCount = 0 Then
        valueSet.Add "Group Members or Group not found"  ' no records returned
    Else
       objRecordSet.MoveFirst
       Do While objRecordSet.EOF = False 'for every record
            valueSet.Add DelimitItems(objRecordSet) 'take the record, pass it to delimit items, which pulls each ofthe fields into a single comma delimited string
            objRecordSet.MoveNext
       Loop

    End If

    ' Close connection
    objConnection.Close

    ' Cleanup
    Set objRecordSet = Nothing
    Set objCommand = Nothing
    Set objConnection = Nothing
    Set GetAdsProp = valueSet
    Set valueSet = Nothing

End Function
Function DelimitItems(record As ADODB.Recordset) As String
'this function takes a ADODB record (which is similar to a collection) and returns a comma delimited
'string containing all the fields for that record
Dim x As Integer
Dim myString As String
Dim item As Variant

x = 1
For Each item In record.Fields
    If (x = 1) And item  "" Then
        myString = item
        x = x + 1
    Else
        If item  "" Then
            myString = myString & ", " & item
        End If
    End If
Next
DelimitItems = myString
End Function

Things to know:

1. You call this function with a full LDAP search string. Eg. (&(ObjectCategory=user)(MemberOf=CN=MYGROUPX, OU=resources, OU=group))
2. You need to tell it what you want back using returnFields eg. “CN, mail”
3. It returns a collection which will need to be iterated over using another function. Even if there are no results. you could just return the first item as text using something like GetAdsProp(“queryString”,”returnFields”)(1)

It’s worth reading through the code, and checking out the references I used most when putting this solution together (again):

• http://www.selfadsi.org/ldap-filter.htm
• https://www.remkoweijnen.nl/blog/2007/11/01/query-active-directory-from-excel/
• https://stackoverflow.com/questions/9890049/ldap-query-to-list-all-users-of-a-certain-group