Vista - Object not a collection error

Hello all,
I am attempting to write and run a script that will enumerate all
DL's in our domain, and then return the number of members in each.
The script works fine, until I run into a DL with no members. At that
point, I receive an Object not a collection error on line 51. I know
that it must have something to do with the way that the If Then
statement cycles the struser variable, but I can't make it work. Any
assistance would be great. Thanks!

' On Error Resume Next

Dim strExcelPath, objExcel, objSheet, k, DLName

' Check for required arguments.
If (Wscript.Arguments.Count < 1) Then
Wscript.Echo "Arguments <FileName> required. For example:" &
vbCrLf _
& "cscript CreateUserList3.vbs c:\MyFolder\UserList3.xls"
Wscript.Quit(0)
End If

' Spreadsheet file to be created.
strExcelPath = Wscript.Arguments(0)

' Bind to Excel object.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "Distribution Lists"
objSheet.Cells(1, 1).Value = "Distribution List"
objSheet.Cells(1, 2).Value = "# of members"

' Query AD for Distribution Lists
Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

objCommand.CommandText = _
"SELECT DistinguishedName FROM 'LDAP://dc=firstdc,dc=seconddc,
dc=com' WHERE objectCategory='group'" & _
"AND groupType = 8 ORDER BY Name"

' Write DL's to Spreadsheet
k = 2
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Do Until objRecordSet.EOF
DLName = objRecordSet.Fields("DistinguishedName").Value
wscript.echo "LDAP://CN="& DLName &
",dc=firstdc,dc=seconddc,dc=com"
set objGroup = GetObject("LDAP://" & DLName)
i = 0
For Each strUser in objGroup.Member
i = i + 1
Next
objSheet.Cells(k, 1).Value = DLName
k = k + 1
If i = 0 Then
objSheet.cells(k,2).Value = "0"
set objgroup = nothing
Else
objSheet.Cells(k, 2).Value = i
set objgroup = nothing
End If
objRecordSet.MoveNext
Loop
objRecordset.Close

' Format the spreadsheet.
objSheet.Range("A1:A2").Font.Bold = False
objSheet.Select
objExcel.Columns(1).ColumnWidth = 61.5
objExcel.Columns(2).ColumnWidth = 20
objExcel.Columns(3).ColumnWidth = 20

' Save the spreadsheet.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

' Clean up.
objConnection.Close
set objConnection = Nothing
set objCommand = Nothing
set objRecordSet = Nothing
set objSheet = Nothing
set objExcel = Nothing

wscript.echo "Done"

This is a long shot 1:

Put an If statement in there to qualify things.

For Each strUser in objGroup.Member
If ((strUser.Class = "user") or (strUser.Class = "person")) Then
i = i +1
End If
Next

Other Classes that might be useful in an If statement are container
and organizationalUnit.

Long shot 2:

Confirm it's not Nothing before attempting to use it.

Comment:

When perusing your code from a news reader - it's hard to determine
what line 51 is.

<winberrym@xxxxxx> wrote in message
news:d647f697-2ee1-422b-ab17-6756e7390daf@xxxxxxThe statement:

For Each strUser in objGroup.Member

should raise an error of the "member" attribute has one or no DN's. I would
suggest:

i = 0
arrMembers = objGroup.member
If IsEmpty(arrMembers) Then
i = 0
ElseIf (TypeName(arrMembers) = "String") Then
i = 1
Else
For Each strMember In arrMembers
i = i + 1
Next
End If

The issue is explained in this link:

http://www.rlmueller.net/MemberOf.htm

Also, distribution groups can have values other than 8 for the groupType
attribute. I would use the LDAP syntax for the ADO query and use the filter:

"(&(objectCategory=group)" _

& "(!groupType:1.2.840.113556.1.4.803:=2147483648))"



Also, in your code the variable DLName has been assigned to be the full
Distinguished Name of the group. Perhaps you want the NT name of the group,
which is the value of the sAMAccountName attribute. Finally, it would be
better to ADO to retrieve the member attribute of the groups, so you don't
need to bind to each group object (which is less efficient). The query I
would suggest (LDAP syntax) is:

===========

strBase = "<LDAP://LDAP://dc=firstdc,dc=seconddc,dc=com>"

strFilter = "(&(objectCategory=group)" _

& "(!groupType:1.2.840.113556.1.4.803:=2147483648))"

strAttributes = "distinguishedName,sAMAccountName,member"



objCommand.CommandText = strBase & ";" & strFilter & ";" & strAttributes &
";subtree"

==========

Then when you enumerate the recordset the number of members in the group
will be one greater than the UBound of the member array (unless it is
empty). This saves enumerating the array. For example:

=========

Dim lngNumber, NTName, arrMembers



k = 2

Do Until objRecordset.EOF

DLName = objRecordSet.Fields("DistinguishedName").Value

NTName = objRecordset.Fields("sAMAccountName").Value

arrMembers = objRecordset.Fields("member").Value

If IsNull(arrMembers) Then

lngNumber = 0

Else

lngNumber = UBound(arrMembers) + 1

End If

objSheet.Cells(k, 1).Value = DLName

objSheet.Cells(k, 2).Value = NTName

objSheet.Cells(k, 3).Value = lngNumber
k = k + 1

objRecordset.MoveNext

Next

objRecordset.Close

==

Note I added a column for the NT name. You might just replace DLName with
NTName, unless the DN is actually what you want.


--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--