Extract Members of an AD Group to Excel

All,

My first post on wiki. I have been writing VB for 2 years now and still learning. PowerShell seems lot easier than VB because of inbuilt commandlets. I am posting my favourite VB script that gets group membership of users in excel. This is required for those who are involved in SOX auditing etc. Please feel free to edit and modify as per your requirements. Kindly make sure that you are using VBSCRIPT compiler to make any changes. I am using VbsEdit at this time.

Save it as GroupMem.vbs and then launch CMD prompt. Use Cscript groupmem.vbs to execute the script.
Modify the input and outpath in the script to get the desired output at right locations.

' List All the Members of a Group
' Input path should be in the form of Distinguished name of group.
' Input text file is C:\YourPath\InputTextFile.txt
' Feel free to modify any change to the scripts .
' the scripts writes output to a excel file and also to a notepad.
' ..............' ..............' ..............
' Author : N . Anand Rao
' Version: 1.0

On Error Resume Next

Set Fso = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists("C:\YourPath\Groupmembership.xlsx") Then
fso.DeleteFile "C:\YourPath\Groupmembership.xlsx"
End If

Dim objFile1
strLog1="C:\YourPath\PassNevExp.txt"
Set objFSO=CreateObject("Scripting.FileSystemObject")
set objFile1=objFSO.CreateTextFile(strLog1,True)

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
'objExcel.Workbooks.Add
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
intRow = 2
col = 2

objExcel.Cells(1, 1).Value = "Group Name"
objExcel.Cells(1, 2).Value = "First Name"
objExcel.Cells(1, 3).Value = "Last Name"
objExcel.Cells(1, 4).Value = "Group Members"
objExcel.Cells(1, 5).Value = "LastLogonTimeStamp"
objExcel.Cells(1, 6).Value = "GroupMemberDescription"
objExcel.Cells(1, 7).Value��= "GroupDescription"
objExcel.Cells(1, 1).Interior.ColorIndex = 27
objExcel.Cells(1, 2).Interior.ColorIndex = 27
objExcel.Cells(1, 3).Interior.ColorIndex = 27
objExcel.Cells(1, 4).Interior.ColorIndex = 27
objExcel.Cells(1, 5).Interior.ColorIndex = 27
objExcel.Cells(1, 6).Interior.ColorIndex = 27
objExcel.Cells(1, 7).Interior.ColorIndex = 27
objExcel.Cells(1, 1).font.bold = True
objExcel.Cells(1, 7).font.bold = True
objExcel.Cells(1, 2).font.bold = True
objExcel.Cells(1, 3).font.bold = True
objExcel.Cells(1, 4).font.bold = True
objExcel.Cells(1, 5).font.bold = True
objExcel.Cells(1, 6).font.bold = True

Set oFS = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
InputFile = "C:\YourPath\InputTextFile.txt"
Set f = oFS.OpenTextFile(InputFile)

StrGroup = f.ReadAll
f.Close
arrGroups = Split(StrGroup,vbCrLf)

For Each agroup in arrGroups

''On Error Resume Next

Set objGroup = GetObject("LDAP://"& agroup)
objGroup.GetInfo

arrMemberOf = objGroup.GetEx("member")

WScript.Echo "Group Name :" & objGroup.get("name")
objExcel.Cells(intRow, 1).Value = objGroup.get("name")
objExcel.Cells(intRow, 6).Value = objGroup.get("description")
'' Gets ManagedBY attribute of a Group
Set ObjManager = GetObject("LDAP://"& objGroup.get("managedby"))
WScript.Echo "Manager of Group is : " & ObjManager.get("name")

''WScript.Echo "Manager is :" & objGroup.get("managedby")
''WScript.Echo "DL Email is :" & objGroup.get("mail")
WScript.echo "Members of the group are : "
For Each strMember in arrMemberOf

Set objMember = GetObject("LDAP://"& strMember)
objGroup.Get("name")

set objLogon = objMember.Get("lastLogontimestamp")
intLogonTime = objLogon.HighPart * (2^32) + objLogon.LowPart
intLogonTime = intLogonTime / (60 * 10000000)
intLogonTime = intLogonTime / 1440

TEST =intLogonTime + #1/1/1601#

WScript.echo objMember.Get("name") & " " & TEST
objExcel.Cells(intRow, 2).Value = objMember.Get("GivenName")
objExcel.Cells(intRow, 3).Value = objMember.Get("sn")
objExcel.Cells(col, 4).Value = objMember.Get("name")
objExcel.Cells(col, 5).Value = TEST
objExcel.Cells(col, 6).Value = objMember.Get("description")
Col = Col + 1
intRow = intRow +1

objFile1.writeline objMember.Get("name") & "|" & objMember.Get("GivenName") & "|" & objMember.Get("sn")
Next
Col = Col + 1
intRow = intRow +1
agroup = Null
objGroup = Null
TEST = null
Next

objExcel.Cells.EntireColumn.AutoFit
objexcel.Cells.EntireRow.AutoFit
objWorkbook.SaveAs "C:\YourPath\Groupmembership.xlsx"
objExcel.Quit

Hope this helps the larger estate of user community.
Cheers
Anand Rao