furrelkt
08-10-2010, 07:33 AM
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
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