Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Limit
I Am using the following that I found somewhere to get the members in an
Active Directory group and populate a spreadsheet with the names. Problem is it always returns 1500 names. even if I know there are 1600 people in this group. Is the array limited in size, or is the LDAP query limited in it's return? Thanks Sub ADGroupMembers() Dim arrNames() intSize = 0 'Let's clear the range first Range("SCREEN").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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Limit
Usually collection have a length property. Rather than keep on redim the array size in a loop get the length property and redim only onced befor you read the data. I'm not sure if this is going to work. What you should do is to add as a watch item : objGroup. Highlight the variable with the mouse and right click. select Watch. Then step through the code using F8. when you step pass the Getobject statement the wath item will contain data and you can look at the object : objGroup.Member. Look at the prperties and see if it contains the 1600 items you are expecting. Sub ADGroupMembers() Dim arrNames() intSize = 0 'Let's clear the range first Range("SCREEN").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 objlength = objGroup.Member.length ReDim Preserve arrNames(objlength) For Each strUser In objGroup.Member Set objuser = GetObject("LDAP://" & strUser) 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 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199906 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Limit
It contains 1499 members from the get go. However I still don't know if the
issue is the array, or the LDAP query. Somewhere there must be a size limit as I know there at least 1600 members of the AD group. I also added people to the group, and it still came up with 1499. "joel" wrote in message ... Usually collection have a length property. Rather than keep on redim the array size in a loop get the length property and redim only onced befor you read the data. I'm not sure if this is going to work. What you should do is to add as a watch item : objGroup. Highlight the variable with the mouse and right click. select Watch. Then step through the code using F8. when you step pass the Getobject statement the wath item will contain data and you can look at the object : objGroup.Member. Look at the prperties and see if it contains the 1600 items you are expecting. Sub ADGroupMembers() Dim arrNames() intSize = 0 'Let's clear the range first Range("SCREEN").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 objlength = objGroup.Member.length ReDim Preserve arrNames(objlength) For Each strUser In objGroup.Member Set objuser = GetObject("LDAP://" & strUser) 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 -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199906 http://www.thecodecage.com/forumz |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Limit
I think the problem is with the query or the database. Have you checked the database to see if there is any empty rows in the database? Maybe delete the 1500 entry which may be empty. Often excel will stop at an empty row. Excel will use the ID column of the database. sometimes the ID column is numerical and other times it can be an alphabetical string. In either case the ID column must be unique. A duplicate entry in the ID column could cause problems. If the ID column is numerical it should be in increasing order. I don't think it matters if a number is skipped because often an entry in a databae will be deleted. I don't think the problem is with the arry and I know I've seen queries where more than 1500 entries are returned. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199906 http://www.thecodecage.com/forumz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Limit
well I have exported with another utility, and there is nothing wrong with
any of the records. The names can be identical, there can be more than one Bill Smith in the AD group. However the username is unique. I'm not returning the user name however. I don't see why the array could not have two Bill Smith's in it. "joel" wrote in message ... I think the problem is with the query or the database. Have you checked the database to see if there is any empty rows in the database? Maybe delete the 1500 entry which may be empty. Often excel will stop at an empty row. Excel will use the ID column of the database. sometimes the ID column is numerical and other times it can be an alphabetical string. In either case the ID column must be unique. A duplicate entry in the ID column could cause problems. If the ID column is numerical it should be in increasing order. I don't think it matters if a number is skipped because often an entry in a databae will be deleted. I don't think the problem is with the arry and I know I've seen queries where more than 1500 entries are returned. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199906 http://www.thecodecage.com/forumz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit on array formula | Excel Worksheet Functions | |||
Array size limit | Excel Programming | |||
what is array size limit in VBA? | Excel Programming | |||
Determining Array Limit | Excel Programming | |||
limit to possible array size? | Excel Programming |