• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

Ad groups from excel

I have list of ad groups in excel and I want to get the list of users (first name, last name) separated by semicolon in the same sheet but separate column. The function will take the group name and get the list of users. I have a script from Rob (Thanks Rob) but don't know how to call the AD with in excel function (macro). The script that enumrates is shown below.
Ex. Groupname in A1 will pass into the function and the list of users in group A1 will be listed in E1.
0
Hadush
Asked:
Hadush
  • 4
  • 3
  • 2
  • +1
1 Solution
 
patrickabCommented:
Hadush,

Please upload your file with the macro. If needs be, make the data anonymous - only a small sample is needed.

Patrick
0
 
cy_hungCommented:
Maybe you want to read about this example :
http://en.allexperts.com/q/Excel-1059/Concatenating-lookup-cells.htm

or this one :
http://www.ozgrid.com/forum/showthread.php?t=90243

Of course, you need some skill in VBA to complete the task.  Please upload the data, so that we can take a look ....
0
 
HadushAuthor Commented:
Thaks Patrickab and cy_hung,
You can refer to the related question if you need to see the script. The only issue I have with that is the formatting of the users is not under the same column because of the depth varies.
As  you can see on the attached excel some users could be level1, others could be level2, or level6 which is the max level i have right now. The macro would start checking if level6 isnull and if it is it will pick level5, if level5 is null it will pick level 4 , continues until it gets some value and pass that group name and get the members of that group and list them under the corrosponding users column.

EESampleData.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
patrickabCommented:
Hadush,

Please explain each of the following:

>"I have list of ad groups in excel ..."

What is an 'ad group'. I've never heard of the term

>"...call the AD with in excel function (macro)."

What is an "AD" in this context? Again I don't know what you mean.

I have your file, but I need your explaination in this question what you want to achieve. Please do not refer me back to a previous question. Instead please just explain what it is that you want done.

Thanks

Patrick
0
 
RobSampsonCommented:
Hi Hadush, I have reposted the code from your previous question, changed to suit this requirement, but instead of running a second macro over the workbook, I've incorporated the ability to place all users in the last used column instead.  The code below is *NOT* a macro, it is a stand-alone VBS file.

@patrickab
AD in this context means Active Directory OUs and Security groups. You can use VBA to pull Active Directory details, but in this case, I've used a straight VBS file.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2 
 
strOU = "OU=Users,OU=Croydon,OU=Sites," 
If strOU <> "" Then 
        If Right(strOU, 1) <> "," Then strOU = strOU & "," 
End If 
 
Set objRootDSE = GetObject("LDAP://RootDSE") 
strDomain = objRootDSE.get("defaultNamingContext") 
 
Set objOU = GetObject("LDAP://" & strOU & strDomain) 
objOU.Filter = Array("Group") 
 
Set objExcel = CreateObject("Excel.Application")
Const xlToLeft = -4159 
Const xlUp = -4162
objExcel.visible = True 
Set objWB = objExcel.Workbooks.Add 
Set objSheet = objWB.Sheets(1) 
 
intRow = 1 
intCol = 2 
For Each objMember In objOU 
        GetGroups objMember, intRow, intCol 
        intRow = intRow + 1 
Next

MoveUsersToLastColumn

MsgBox "Done"
 
Sub GetGroups(objGroup, intGroupRow, intGroupCol) 
        objSheet.Cells(intGroupRow, 1).Value = Mid(Split(strOU, ",")(0), 4) 
        objSheet.Cells(intGroupRow, intGroupCol).Value = Mid(objGroup.Name, 4) 
        GetMembers objGroup, intGroupRow, intGroupCol 
        intNumOfGroups = 0 
        For Each objObject In objGroup.Members 
                If LCase(objObject.Class) = "group" Then 
                        intNumOfGroups = intNumOfGroups + 1 
                        'intGroupCol = intGroupCol + 1 
                        intGroupRow = intGroupRow + 1 
                        objSheet.Cells(intGroupRow, 1).Value = Mid(Split(strOU, ",")(0), 4) 
                        objSheet.Cells(intGroupRow, intGroupCol).Value = Mid(objGroup.Name, 4) 
                        GetGroups objObject, intGroupRow, intGroupCol + 1 
                End If 
        Next 
End Sub 
 
Sub GetMembers(objGroupUsers, intUserRow, intUserCol) 
        For Each objObject In objGroupUsers.Members 
                If LCase(objObject.Class) = "user" Then 
                        objSheet.Cells(intUserRow, intUserCol + 1).Font.Bold = True 
                        If objSheet.Cells(intUserRow, intUserCol + 1).Value = "" Then 
                                objSheet.Cells(intUserRow, intUserCol + 1).Value = objObject.CN 
                        Else 
                                objSheet.Cells(intUserRow, intUserCol + 1).Value = objSheet.Cells(intUserRow, intUserCol + 1).Value & ";" & objObject.CN 
                        End If 
                End If 
        Next 
End Sub

Sub MoveUsersToLastColumn
	intLastColumn = 0
	intLastRow = objSheet.Cells(65536, 1).End(xlUp).Row
	For intRow = 1 To intLastRow
		intLastColInRow = objSheet.Cells(intRow, 256).End(xlToLeft).Column
		If intLastColInRow > intLastColumn Then intLastColumn = intLastColInRow
	Next
	For intRow = 1 To intLastRow
		intLastColInRow = objSheet.Cells(intRow, 256).End(xlToLeft).Column
		If intLastColInRow < intLastColumn Then
			objSheet.Cells(intRow, intLastColumn).Value = objSheet.Cells(intRow, intLastColInRow).Value
			objSheet.Cells(intRow, intLastColumn).Font.Bold = True
			objSheet.Cells(intRow, intLastColInRow).Value = ""
			objSheet.Cells(intRow, intLastColInRow).Font.Bold = False
		End If
	Next
	objSheet.Rows("1:1").Insert
	For intCol = 1 To intLastColumn - 1
		objSheet.Cells(1, intCol).Value = "Level " & intCol
	Next
	objSheet.Cells(1, intLastColumn).Value = "Users"
	objSheet.Rows("1:1").Font.Bold = True
	objSheet.Columns.AutoFit
End Sub

Open in new window

0
 
HadushAuthor Commented:
Thanks Rob,
It perfectly move the users to the last column except for groups which don't have users yet. If the group doesn't have users it is moving to the users list. Is there a way to tell if it is group name not to move to the users column?
0
 
RobSampsonCommented:
Yeah, maybe we can check if the cell is bold or not....try this.

Regards,

Rob.
Const ADS_SCOPE_SUBTREE = 2 
 
strOU = "OU=Users,OU=Croydon,OU=Sites," 
If strOU <> "" Then 
        If Right(strOU, 1) <> "," Then strOU = strOU & "," 
End If 
 
Set objRootDSE = GetObject("LDAP://RootDSE") 
strDomain = objRootDSE.get("defaultNamingContext") 
 
Set objOU = GetObject("LDAP://" & strOU & strDomain) 
objOU.Filter = Array("Group") 
 
Set objExcel = CreateObject("Excel.Application")
Const xlToLeft = -4159 
Const xlUp = -4162
objExcel.visible = True 
Set objWB = objExcel.Workbooks.Add 
Set objSheet = objWB.Sheets(1) 
 
intRow = 1 
intCol = 2 
For Each objMember In objOU 
        GetGroups objMember, intRow, intCol 
        intRow = intRow + 1 
Next

MoveUsersToLastColumn

MsgBox "Done"
 
Sub GetGroups(objGroup, intGroupRow, intGroupCol) 
        objSheet.Cells(intGroupRow, 1).Value = Mid(Split(strOU, ",")(0), 4) 
        objSheet.Cells(intGroupRow, intGroupCol).Value = Mid(objGroup.Name, 4) 
        GetMembers objGroup, intGroupRow, intGroupCol 
        intNumOfGroups = 0 
        For Each objObject In objGroup.Members 
                If LCase(objObject.Class) = "group" Then 
                        intNumOfGroups = intNumOfGroups + 1 
                        'intGroupCol = intGroupCol + 1 
                        intGroupRow = intGroupRow + 1 
                        objSheet.Cells(intGroupRow, 1).Value = Mid(Split(strOU, ",")(0), 4) 
                        objSheet.Cells(intGroupRow, intGroupCol).Value = Mid(objGroup.Name, 4) 
                        GetGroups objObject, intGroupRow, intGroupCol + 1 
                End If 
        Next 
End Sub 
 
Sub GetMembers(objGroupUsers, intUserRow, intUserCol) 
        For Each objObject In objGroupUsers.Members 
                If LCase(objObject.Class) = "user" Then 
                        objSheet.Cells(intUserRow, intUserCol + 1).Font.Bold = True 
                        If objSheet.Cells(intUserRow, intUserCol + 1).Value = "" Then 
                                objSheet.Cells(intUserRow, intUserCol + 1).Value = objObject.CN 
                        Else 
                                objSheet.Cells(intUserRow, intUserCol + 1).Value = objSheet.Cells(intUserRow, intUserCol + 1).Value & ";" & objObject.CN 
                        End If 
                End If 
        Next 
End Sub

Sub MoveUsersToLastColumn
	intLastColumn = 0
	intLastRow = objSheet.Cells(65536, 1).End(xlUp).Row
	For intRow = 1 To intLastRow
		intLastColInRow = objSheet.Cells(intRow, 256).End(xlToLeft).Column
		If intLastColInRow > intLastColumn Then intLastColumn = intLastColInRow
	Next
	For intRow = 1 To intLastRow
		intLastColInRow = objSheet.Cells(intRow, 256).End(xlToLeft).Column
		If intLastColInRow < intLastColumn Then
			If objSheet.Cells(intRow, intLastColInRow).Font.Bold = True Then
				objSheet.Cells(intRow, intLastColumn).Value = objSheet.Cells(intRow, intLastColInRow).Value
				objSheet.Cells(intRow, intLastColumn).Font.Bold = True
				objSheet.Cells(intRow, intLastColInRow).Value = ""
				objSheet.Cells(intRow, intLastColInRow).Font.Bold = False
			End If
		End If
	Next
	objSheet.Rows("1:1").Insert
	For intCol = 1 To intLastColumn - 1
		objSheet.Cells(1, intCol).Value = "Level " & intCol
	Next
	objSheet.Cells(1, intLastColumn).Value = "Users"
	objSheet.Rows("1:1").Font.Bold = True
	objSheet.Columns.AutoFit
End Sub

Open in new window

0
 
HadushAuthor Commented:
Thanks ROB! AGAIN!
 
0
 
HadushAuthor Commented:
Thanks
0
 
RobSampsonCommented:
No problem. Thanks for the grade.

Regards,

Rob.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now