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:[vba]buf = Split(o.Object.Values, ";")[/vba]use:
[vba]buf = Split(o.Object.DisplayValues, ";")[/vba]

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:[vba]buf = Split(o.Object.Values, ";")[/vba]use:
[vba]buf = Split(o.Object.DisplayValues, ";")[/vba]

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:
[vba]buf = Split(Replace(o.Object.DisplayValues, """", ""), ";")[/vba]