Excel VBA how to find a description from an AD-group

0 votes

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.

Sub ValidateGroupName()

Dim objController
Dim objGCController
Dim objConnection
Dim objCommand
Dim strADPath
Dim objRecordSet
Dim objFields

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")
    objConnection.Open "Provider=ADsDSOObject;"

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
Next


Y = 0
Do

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
Else
' change color of a cell to green
Sheets(ActSheet).Range("E2").Offset(Y, 0).Interior.Color = 7138816
End If

Y = Y + 1

Loop Until Sheets(ActSheet).Range("D2").Offset(Y, 0).Value = ""

' Close AD connection
    objConnection.Close

End Sub

I hope I'm on the right track, but a different approach might be a cleaner solution.

Feb 16, 2023 in Others by narikkadan
• 63,420 points
490 views

1 answer to this question.

0 votes

First add the 'description' property to your query :

objCommand.CommandText = _
    "<" & strADPath & ">;(&(objectClass=Group)" & _
         "(cn=" & GroupName & "));distinguishedName;subtree;Description"

Then, get the property value if the group exists and write it beside the group cell for example :

If objRecordSet.RecordCount = 0 Then
'change color of a cell to red
Sheets(ActSheet).Range("E2").Offset(Y, 0).Interior.Color = 255
Else
' change color of a cell to green
Sheets(ActSheet).Range("E2").Offset(Y, 0).Interior.Color = 7138816
Sheets(ActSheet).Range("E2").Offset(Y, 1).text = objRecordset.Fields("Description")
End If
answered Feb 16, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
2,762 views
0 votes
1 answer

In Excel, how to find a average from selected cells

If one has the dynamic array formula ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,420 points
435 views
0 votes
1 answer
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,420 points
6,435 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
829 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,124 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
420 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
687 views
0 votes
1 answer
0 votes
1 answer

How to read a dataframe from an excel sheet containing multiple tables?

Here is one option with scikit-image (highly inspired by ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
1,650 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP