Excel ListCount Maximum (moved from ms ofice forum)

[B827]

I have inherited a Excel workbook which has a listbox populated by a query on a database table. Recently the number of records returned has exceeded 60. The following code is used to red the selected items.

For i = 0 To LBSubjects.ListCount - 1
If LBSubjects.Selected(i) Then
keystr = keystr & LBSubjects.List(i, 0) & ","
End If
Next

Unfortunately this only goes as far as the 60th record and then exits the loop.

The ListCount appears to be a fixed at a maximum of 60 and is readonly property.

Does anyone have a method of reading through all the records?

Code for loading data to listbox

For Each ENode In XResp.documentElement.selectSingleNode("SUBJECT").childNodes
AddItemToList LB, Array(ENode.selectSingleNode("SUBJECTID").Text, ENode.selectSingleNode("SUBJECTNAME").Text)
Next
Public Function AddItemToList(InList As MSForms.ListBox, inArray As Variant)
Dim j As Integer
InList.AddItem (inArray(0))
For j = 1 To UBound(inArray)
InList.List(InList.ListCount - 1, j) = inArray(j)
Next
End Function

The listbox is an item in a sheet and not in a user form

Many thanks
Sandy


Sandy

 

[SkipVought]

Hi,

How is ENode defined and assigned?

Skip,
[sub]
[red][/red]
[/sub]

 

[B827]

Hi Skip
thanks for the continued help.

this is the code I think you need

Dim ENode As IXMLDOMElement
Dim XN As MSXML.IXMLDOMElement

The project references include
Microsoft XML, version 2.0

Sandy

 

[SkipVought]

And how about XResp? It seems that that is the collection that is driving the list.

How is XResp assigned?

Skip,
[sub]
[red][/red]
[/sub]

 

[B827]

Hi Skip
these are the references I can find

Private XResp As MSXML.DOMDocument
If XResp Is Nothing Then
Set XResp = New MSXML.DOMDocument
XResp.async = False
End If


For j = 1 To 6
XReq.documentElement.setAttribute WSheet.Cells(HeadingRow, j).Value, CellX.Offset(0, j - 1).Value
Next
'MsgBox X.XML
CellX.Offset(0, ResultCol - 1).Value = ""
'CellX.Offset(0, ResultCol - 1).Interior.Color = RGB(0, 0, 0)

dmy = C.Send(XResp)
If Not dmy Then
CellX.Offset(0, ResultCol - 1).Value = "FATAL: " & C.ErrorText & " (" & C.ErrorCode & ")"

Exit Do
Else
CellX.Offset(0, ResultCol - 1).Value = XResp.documentElement.Text
End If

Set CellX = CellX.Offset(1, 0)
CellX.Select
Loop


C.DisplayErrors = True
dmy = C.Send(XResp)
If Not dmy Then Exit Function
For Each N In XResp.documentElement.childNodes
LB.AddItem (N.selectSingleNode("USERID").Text)
LB.List(LB.ListCount - 1, 1) = N.selectSingleNode("USERNAME").Text
Next
Set LB = Nothing

Sandy

 

[SkipVought]

At this line of code...

[highlight yellow]
For Each ENode In XResp.documentElement.selectSingleNode("SUBJECT").childNodes[/highlight]
    AddItemToList LB, Array(ENode.selectSingleNode("SUBJECTID").Text, ENode.selectSingleNode("SUBJECTNAME").Text)
Next

insert a BREAK and examine this in the Watch Window...

XResp.documentElement.selectSingleNode("SUBJECT").childNodes[b].Count[/b]

What is that count?

Skip,
[sub]
[red][/red]
[/sub]

 

[B827]

Hi Skip,
thanks to your help I think that the Excel list box is working OK. There is something defined either in the workbook code or the ASP page generating the XML which doesn't like numbers in excess of 2000 (or similar) which I will now have to find.

Many thanks for your assistance.

Sandy

 

[SkipVought]

"...ListCount appears to be a fixed at a maximum of 60 ..."
"...numbers in excess of 2000 ..."

Is this something new, or the original question?

Exactly where are you on this?

Skip,
[sub]
[red][/red]
[/sub]

 

[B827]

Hi Skip,
just to let you know that I finally fixed the problem. There seems to have been a control character in a database field which caused the XML interface to crash.

Sandy

 

[SkipVought]

Wonderful!

Skip,
[sub]
[red][/red]
[/sub]