I have to create an Excel spreadsheet for a project that suggests new Active Directory groups based on the users' existing Active Directory groups. I require the active directory group's current description column for this (this is a mandatory field in our organization).
As a result, I already have a script that can confirm the presence of a Group by colouring it in. It's just one step in a larger problem. The description information is not retrieved in this way. Also, I haven't yet discovered a practical answer on the internet that I could also understand. The fact that the groups are in several Containers is a challenge that could make it more complicated. The distinguishing name prefix becomes inconsistent as a result.
Code so far. This reads ADgroups from column D (starting with D2) and searches it in active directory. Then it marks a cell with the colour green if it was found.
Dim Y As Integer
Dim GroupName As String
Dim ActSheet As String
Dim Descriptionname As String
ActSheet = ActiveSheet.Name
' Set up AD connection
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
Set objController = GetObject("GC:")
' Get record from AD
For Each objGCController In objController
strADPath = objGCController.ADspath
'strADDescription = objGCController.ADspath
Y = 0
GroupName = Sheets(ActSheet).Range("D2").Offset(Y, 0).Value
objCommand.CommandText = _
"<" & strADPath & ">;(&(objectClass=Group)" & _
"(cn=" & GroupName & "));distinguishedName;subtree"
objCommand.Properties("Page Size") = 50000
Set objRecordSet = objCommand.Execute
' What to do with results?
If objRecordSet.RecordCount = 0 Then
'change color of a cell to red
Sheets(ActSheet).Range("E2").Offset(Y, 0).Interior.Color = 255
' change color of a cell to green
Sheets(ActSheet).Range("E2").Offset(Y, 0).Interior.Color = 7138816
Y = Y + 1
Loop Until Sheets(ActSheet).Range("D2").Offset(Y, 0).Value = ""
' Close AD connection
I hope I'm on the right track, but a different approach might be a cleaner solution.