Thanks Helmut, will have to do some research on LDAP as I'm not really too
familiar with it either.
"Helmut Meukel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> It's definitely not a problem with the array.
> I usually program in VB6 and was uncertain where the limit would
> be in Excel VBA, so I tested it on my system with Excel2000:
> ReDim arrNames(10000000) still worked,
> ReDim arrNames(100000000) caused an "Out of memory" error.
> I then filled an array of one million elements with strings, always
> redimming it like in your code. No problem at all.
>
> It's a problem with your LDAP query. I never used LDAP so
> I can't help you with this.
>
> BTW, assuming you get it fixed, change your other code to make
> it faster:
> - Dim arrNames() as String
> - initially ReDim arrNames(5000)
> - when done with the loop: ReDim Preserve arrNames(intSize-1)
> Continually redimming the array slows down your code dramatically.
> You use a slow sorting method, use Quicksort instead.
> For counters like intSize, j and i use Longs instead of Variants.
>
> HTH.
>
> Helmut.
>
>
> "Striker3070" <(E-Mail Removed)> schrieb im Newsbeitrag
> news:%(E-Mail Removed)...
>> the range SESCREEN is simply a named range on a spreadsheet, plenty or
>> room for 4000 names.
>>
>> The problem seems to be I never return more or lass than 1500 names. I
>> can add a watch to objGroup.Member and it has
>> 1499, from 0-1499 so 1500 members. so thie issue seems to be a limit on
>> either the array can't hold more than 1500, or the LDAP query can't
>> return more than 1500.
>>
>> "Helmut Meukel" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Where - and how - do you define the range "SESCREEN"
>>> I tested the Transpose function to see where its limitations are.
>>> It will happily fill a given range without complaining that the provided
>>> array contains more values than the range could hold.
>>>
>>> So I asked if you really checked how many names your code retrieved
>>> in the for each loop and stored in the array arrNames.
>>>
>>> Helmut.
>>>
>>>
>>> "Striker3070" <(E-Mail Removed)> schrieb im Newsbeitrag
>>> news:(E-Mail Removed)...
>>>> well as soon as the objuser.CN gets, it is 1499
>>>>
>>>> "Helmut Meukel" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> Just for clarity:
>>>>> Is intSize = 1499 at the end of the For-Each-Loop?
>>>>>
>>>>> Helmut.
>>>>>
>>>>>
>>>>>
>>>>> "Striker3070" <(E-Mail Removed)> schrieb im Newsbeitrag
>>>>> news:24CCBF4D-BE8C-4526-8A95-(E-Mail Removed)...
>>>>>> using the following to get Active Directory group membership, is it
>>>>>> possible that once I return a value say "Bob Smith" that is NOT
>>>>>> unique this will stop. I return 1499 members names no matter how many
>>>>>> people are in the group. I've added people to the group to be sure,
>>>>>> I still get 1499 as a result.
>>>>>>
>>>>>> I am NOT returning a domain ID which would be unique, so perhaps
>>>>>> that's an issue.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Sub ADGroupMembers()
>>>>>> Dim arrNames()
>>>>>> intSize = 0
>>>>>>
>>>>>> 'Let's clear the range first
>>>>>> Range("SESCREEN").ClearContents
>>>>>>
>>>>>> 'Get the AD Group Info for BATY-SESCREEN
>>>>>> Set objGroup = GetObject("LDAP://CN= BATY-SESCREEN,OU=Security
>>>>>> Groups,DC=baty,DC=com")
>>>>>>
>>>>>> 'Getting User Names and increase the array size
>>>>>> For Each strUser In objGroup.Member
>>>>>> Set objuser = GetObject("LDAP://" & strUser)
>>>>>> ReDim Preserve arrNames(intSize)
>>>>>> arrNames(intSize) = objuser.CN
>>>>>> intSize = intSize + 1
>>>>>> Next
>>>>>>
>>>>>> 'In order to sort, we have to convert to Uppercase.
>>>>>> For i = (UBound(arrNames) - 1) To 0 Step -1
>>>>>> For j = 0 To i
>>>>>> If UCase(arrNames(j)) > UCase(arrNames(j + 1)) Then
>>>>>> strHolder = arrNames(j + 1)
>>>>>> arrNames(j + 1) = arrNames(j)
>>>>>> arrNames(j) = strHolder
>>>>>> End If
>>>>>> Next
>>>>>> Next
>>>>>>
>>>>>> 'Fill the SESCREEN range with the array values
>>>>>> Range("SESCREEN").Value = WorksheetFunction.Transpose(arrNames)
>>>>>>
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
|
Similar Threads
