How to read the contents of an OLEObject (dropdown list) in excel

[furrelkt]

I need to print out the list contents from the list in excel sheet. i used copy/paste from a web site and the paste went into the excel sheet as a list.

here is the code i have


Sub Test()
Dim o As Object, buf, c As Range, i As Long
On Error Resume Next
Set c = Application.InputBox("Select a cell for output", Type:=8)
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
For Each o In ActiveSheet.OLEObjects
If TypeName(o.Object) = "HTMLSelect" Then
buf = Split(o.Object.Values, ";")
c.Offset(, i).Resize(UBound(buf)).Value = Application.Transpose(buf)
i = i + 1
End If
Next
End Sub



This produces:

1st__bSource__bCorporation__b__u__b__PFSCIN__p
AA__bInterfinance__bB__dV__d__b__b__PAANL__p



In the list that i see in the excel sheet is actually...


1st Souce Corporation - (FSCIN)
AA Interfianace B.V. (AANL)


So, i am guessing that it's reading the ID or unique code or something instead of the actual "name" that's in the list...

Can someone help me please? I really need it to return what's showing in the list and not "ID".


Thanks for any help.

Edit: added the workbook.

Keri

[p45cal]

instead of:buf = Split(o.Object.Values, ";")use:
buf = Split(o.Object.DisplayValues, ";")

Note that your resize isn't big enough to accommodate the last entry since split returns a zero based array, not a 1 based array, so you'll miss the last entry..

[furrelkt]

instead of:buf = Split(o.Object.Values, ";")use:
buf = Split(o.Object.DisplayValues, ";")

Note that your resize isn't big enough to accommodate the last entry since split returns a zero based array, not a 1 based array, so you'll miss the last entry..

Thank you so much for this. :-)

It seems as it is getting the last value. :-)

Thank you so much for your help.

Also, everytime i go to scroll...it quits excel and restarts... as well as i notice that some of the entries or names are coming over like


"AmTrust Bank - (OSBOH)"
Arvest Bank Operations, Inc. - (ABOAR)
"Asiatrust Development Bank (ASTPH)"

with the quotes i mean...

Edit: I can clean this up, without the qoutes. i so appreciate your help.

Keri

[p45cal]

This will clear the quote marks:
buf = Split(Replace(o.Object.DisplayValues, """", ""), ";")