Active Directory Users with Group Membership Export to Excel

Hi, can someone help me here. I'm not that great at scripting.......
Here's what I am looking to do....
I need to enumerate ALL active directory users THAT ARE NOT DISABLED,
into a spreadsheet with a list of their groups. For example, column A
and B on the spreadsheet is the first and last name, column C is the
groups that the user is a member of. I need to also make sure NOT to
list any accounts that are disabled.
The excel order is like:
John Doe
Domain User
Accounting
Any other Group
Jane Doe
Domain Users
Accounting
Randy Top
Domain Users


I have sort of been working on a script that I found that pulls the
first and last name into a spreadsheet, but I need help getting the
rest...........
ANY HELP IS GREATLY APPRECIATED!!!!!!!!!!!! THANKS!!!!!!!!!!!!!!!
Here is the scirpt I've been working with...

_____________________________________________________________________________
Dim ObjWb
Dim ObjExcel
Dim x, zz
Const ADS_UF_ACCOUNTDISABLE = 2
Set objRoot = GetObject("LDAP://RootDSE")
strDNC = objRoot.Get("DefaultNamingContext")
Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the
Domain using LDAP using ROotDSE
Call ExcelSetup("Sheet1") ' Sub to make Excel Document
x = 1
Call enummembers(objDomain)
Sub enumMembers(objDomain)
On Error Resume Next
Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's

For Each objMember In objDomain ' go through the collection

If ObjMember.Class = "user" Then ' if not User object, move on.
x = x + 1 ' counter used to increment the cells in Excel




FirstName = objMember.GivenName
LastName = objMember.sn
Manager = ObjMember.Manager
AdsPath = Objmember.Adspath

zz = 1 ' Counter for array of 2ndary email addresses
For each email in ObjMember.proxyAddresses
If Left (email,5) = "SMTP:" Then
Primary = Mid (email,6) ' if SMTP is all caps, then it's the Primary
ElseIf Left (email,5) = "smtp:" Then
Secondary(zz) = Mid (email,6) ' load the list of 2ndary SMTP
emails into Array.
zz = zz + 1
End If
Next
' Write the values to Excel, using the X counter to increment the rows.


objwb.Cells(x, 1).Value = FirstName
objwb.Cells(x, 2).Value = LastName
objwb.Cells(x, 3).Value = Manager
objwb.Cells(x, 4).Value = AdsPath

' Write out the Array for the 2ndary email addresses.
For ll = 1 To 20
objwb.Cells(x,26+ll).Value = Secondary(ll)
Next
' Blank out Variables in case the next object doesn't have a value for
the property

FirstName = "-"
LastName = "-"
Manager = "-"
For ll = 1 To 20
Secondary(ll) = ""
Next
End If

' If the AD enumeration runs into an OU object, call the Sub again to
itinerate

If objMember.Class = "organizationalUnit" or OBjMember.Class =
"container" Then
enumMembers (objMember)
End If
Next
End Sub
Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds
Column heads to the 1st row
Set objExcel = CreateObject("Excel.Application")
Set objwb = objExcel.Workbooks.Add
Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName)
Objwb.Name = "Active Directory Users" ' name the sheet
objwb.Activate
objExcel.Visible = True
objwb.Cells(1, 1).Value = "FirstName"
objwb.Cells(1, 2).Value = "LastName"
objwb.Cells(1, 3).Value = "Manager"
objwb.Cells(1, 4).Value = "Adspath"
End Sub
MsgBox "Done" ' show that script is complete
_____________________________________________________________________________

Hi,

You've made a good start. Do you miss outputing the primary email address on
purpose?

A problem I see is that you already have a multi-valued attribute you are
documenting in the spreadsheet, proxyAddresses. Ordinarily I place
multi-valued attributes at the right end of the spreadsheet, as you have
done, so they can take up as many columns as necessary. There is no good way
to accomodate two multi-valued attributes, unless you cut off the number of
values documented. Normally, I would suggest using a dynamic array for
proxyAddresses, but in this case hard coding a dimension of 20 probably
helps.

In any case, group memberships can be handled the same way. The memberOf
attribute is multi-valued. It is a collection of the Distinguished Names of
the groups the user is a direct member of (except the user's "Primary"
group, which should be "Domain Users"). Perhaps add statements similar to:

Dim Groups(9) ' array for up to 10 group memberships.

If objMember.Class = "user" Then
...
jj = 0
For Each strGroup In objMember.memberOf
Groups(jj) = strGroup
jj = jj + 1
Next

...

For jj = 0 To 9
objwb.Cells(x, jj + 47).Value = Groups(jj)
Next

...

For jj = 0 To 9
Groups(jj) = "-"
Next

I ordinarily recommend removing "On Error Resume Next". I assume this was
added because the multi-valued attributes will raise errors if they have no
values or if there is just one value. Also the script will raise an error if
more than the maximum anticipated values in the multi-valued attributes are
encountered. Since the script is flawed (a user with one group, not counting
their "primary" group, will not have their membership documented - same for
proxyAddresses), I suggest removing "On Error Resume Next" and modifying to
fix this. Perhaps use:

jj = 0
arrGroups = objMemberMemberOf
If IsEmpty(arrGroups) Then
' do nothing, no group memberships.
ElseIf (TypeName(arrGroups) = "String") Then
' One group membership.
Groups(0) = arrGroups
Else
' More than one group membership
For Each strGroup In arrGroups
Groups(jj) = strGroup
jj = jj + 1
If (jj = 10) Then
' maximum documented is 10 (jj=0 to 9).
Exit For
End If
Next
End If

Something similar should be done for proxyAddresses. To eliminate disabled
users, test the AccountDisabled property method. For example:

For Each objMember In objDomain
If objMember.Class = "user" Then
If (objMember.AccountDisabled = False) Then
x = x + 1
...

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

<tim.cole@ftnfinancial.com> wrote in message
news:1147875317.395082.166740@38g2000cwa.googlegroups.com...
> Hi, can someone help me here. I'm not that great at scripting.......
> Here's what I am looking to do....
> I need to enumerate ALL active directory users THAT ARE NOT DISABLED,
> into a spreadsheet with a list of their groups. For example, column A
> and B on the spreadsheet is the first and last name, column C is the
> groups that the user is a member of. I need to also make sure NOT to
> list any accounts that are disabled.
> The excel order is like:
> John Doe
> Domain User
> Accounting
> Any other Group
> Jane Doe
> Domain Users
> Accounting
> Randy Top
> Domain Users
>
>
> I have sort of been working on a script that I found that pulls the
> first and last name into a spreadsheet, but I need help getting the
> rest...........
> ANY HELP IS GREATLY APPRECIATED!!!!!!!!!!!! THANKS!!!!!!!!!!!!!!!
> Here is the scirpt I've been working with...
>
> _____________________________________________________________________________
> Dim ObjWb
> Dim ObjExcel
> Dim x, zz
> Const ADS_UF_ACCOUNTDISABLE = 2
> Set objRoot = GetObject("LDAP://RootDSE")
> strDNC = objRoot.Get("DefaultNamingContext")
> Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the
> Domain using LDAP using ROotDSE
> Call ExcelSetup("Sheet1") ' Sub to make Excel Document
> x = 1
> Call enummembers(objDomain)
> Sub enumMembers(objDomain)
> On Error Resume Next
> Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's
>
> For Each objMember In objDomain ' go through the collection
>
> If ObjMember.Class = "user" Then ' if not User object, move on.
> x = x + 1 ' counter used to increment the cells in Excel
>
>
>
>
> FirstName = objMember.GivenName
> LastName = objMember.sn
> Manager = ObjMember.Manager
> AdsPath = Objmember.Adspath
>
> zz = 1 ' Counter for array of 2ndary email addresses
> For each email in ObjMember.proxyAddresses
> If Left (email,5) = "SMTP:" Then
> Primary = Mid (email,6) ' if SMTP is all caps, then it's the Primary
> ElseIf Left (email,5) = "smtp:" Then
> Secondary(zz) = Mid (email,6) ' load the list of 2ndary SMTP
> emails into Array.
> zz = zz + 1
> End If
> Next
> ' Write the values to Excel, using the X counter to increment the rows.
>
>
> objwb.Cells(x, 1).Value = FirstName
> objwb.Cells(x, 2).Value = LastName
> objwb.Cells(x, 3).Value = Manager
> objwb.Cells(x, 4).Value = AdsPath
>
> ' Write out the Array for the 2ndary email addresses.
> For ll = 1 To 20
> objwb.Cells(x,26+ll).Value = Secondary(ll)
> Next
> ' Blank out Variables in case the next object doesn't have a value for
> the property
>
> FirstName = "-"
> LastName = "-"
> Manager = "-"
> For ll = 1 To 20
> Secondary(ll) = ""
> Next
> End If
>
> ' If the AD enumeration runs into an OU object, call the Sub again to
> itinerate
>
> If objMember.Class = "organizationalUnit" or OBjMember.Class =
> "container" Then
> enumMembers (objMember)
> End If
> Next
> End Sub
> Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds
> Column heads to the 1st row
> Set objExcel = CreateObject("Excel.Application")
> Set objwb = objExcel.Workbooks.Add
> Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName)
> Objwb.Name = "Active Directory Users" ' name the sheet
> objwb.Activate
> objExcel.Visible = True
> objwb.Cells(1, 1).Value = "FirstName"
> objwb.Cells(1, 2).Value = "LastName"
> objwb.Cells(1, 3).Value = "Manager"
> objwb.Cells(1, 4).Value = "Adspath"
> End Sub
> MsgBox "Done" ' show that script is complete
> _____________________________________________________________________________
>

Thanks so much.... I appreciate it....but I'm not sure I know WHERE to
make these additions...