Help with ADSI spreadsheet

[aaltieri]

Greetings!

I am building an excel spreadsheet to log some data from our AD network and have run into a bit of a snag.

I'm looking at all the properties that an object (a user in this example) has in AD. I'm using a loop that looks like:

For Each prop In schobj.mandatoryproperties
targetrng.Offset(i, 0) = prop 'enters the data into the spreadsheet
i = i + 1 'increments once to move to the next row for the next property
Next

This lists all the properties (well, the mandatory ones in this case) for me in an excel spreadsheet. Now, what I need it to do is to also print out the value of those properties. I'm trying to do this by referring to the specific field via a variable. Something like:

targetrng.Offset(i, 0) = userObj.$prop

Using the variable name Prop, in lieu of having to hard code every single property like:

userObj.cn
userObj.objectSID

etc...


I would seriously appreciate any help as we have over 200 properties for the user objects...and if at all possible, I'd like to use the same bit of code for container objects and computers.

Peace

anthony.

[Bob Phillips]

Is this a 3rd party object, with its own type library?

[aaltieri]

In the immediate example, I'm dealing with ADSI (Active Directory Systems/Service Interface) objects. This is a built-in library in VBA/VBscript.

However, they are kind of third...forth...and fifth party objects as they have the standard ADSI stuff, plus the exchange fields, some fields for our Cisco networks, some audit stuff tossed in there. Quite a few, really.

But I suspect that the question could be rephrased to refer to the properties of a cell object just as easily.

Hope this clears things up a mite for you!

Thanks!


--anthony

[Bob Phillips]

See if this function gets you anywhere. You would use it like so

[vba]

Dim i As Long
Dim ary

ary = IterateMembers(cls) ' I guess here you would put your instance of the ADS
For i = LBound(ary, 2) To UBound(ary, 2)
Debug.Print ary(1, i) & " - " & ary(2, i) & " - " & _
ary(3, i) & " - " & ary(4, i)
Next i
[/vba]

Here is the function that gets the info

[vba]

Function IterateMembers(obj As Object) As Variant
Dim TLI As New TLIApplication
Dim ret As Variant
Dim interface As InterfaceInfo
Dim member As MemberInfo
Dim rtn As Variant
Dim i As Long
Dim iArray As Long
Dim aryProps
Dim fOK As Boolean

On Error Resume Next
Set interface = TLI.InterfaceInfoFromObject(obj)

ReDim aryProps(1 To 4, 1 To 1)
iArray = 1
For Each member In interface.Members
ReDim Preserve aryProps(1 To 4, 1 To iArray)
aryProps(1, iArray) = member.Name
rtn = ""
rtn = TLI.InvokeHook(obj, member.MemberId, INVOKE_PROPERTYGET)
Select Case member.InvokeKind
Case INVOKE_FUNC:
aryProps(2, iArray) = "Function"
aryProps(3, iArray) = ""
aryProps(4, iArray) = member.Value
Case INVOKE_PROPERTYGET:
aryProps(2, iArray) = "Property Get"
aryProps(3, iArray) = TypeName(rtn)
aryProps(4, iArray) = rtn
Case INVOKE_PROPERTYPUT:
aryProps(2, iArray) = "Property Put"
aryProps(3, iArray) = TypeName(rtn)
aryProps(4, iArray) = rtn
Case INVOKE_CONST:
aryProps(2, iArray) = "Constant"
aryProps(3, iArray) = TypeName(rtn)
aryProps(4, iArray) = rtn
End Select
iArray = iArray + 1
Next member

Set TLI = Nothing
IterateMembers = aryProps

End Function
[/vba]

[Bob Phillips]

Oh, BTW, you will have to set a reference to the 'Typelib Information' librray, (tlbinf32.dll)

[aaltieri]

Oh, BTW, you will have to set a reference to the 'Typelib Information' librray, (tlbinf32.dll)

Forgive me, but I'm not entirely certain what that means.

Thanks for all the help though! This is potentially saving me a day or two of manual searching!

Peace

--anthony