RTF string of EXCEL cell using VBA

[chemimpex]

I am trying to get the RTF String of data stored in a cell in EXCEL 2000.
I need the rtf string because the data is formatted and it contains symbol fonts, subscripts, superscripts etc

Is there a way to get the rich text string directly from the cell.
I also tried to send this cell to clipboard ( using selection.copy) and try to get the RTF from clipboard using
clipboard.gettext(vbcfrtf) but it doesn't work in VBA

I also tried to get to paste this data in word using
WD.Selection.PasteSpecial DataType:=wdPasteRTF
but the symbol font's get converted to ??? when it pastes into word. That is really funny, wonder why?

What is the best way to do this? Pls advise asap.

If anything can go wrong, it WILL.

 

[SkipVought]

What method are you using that is not working?

Please post your code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[chemimpex]

Skip, this is the code i have

Sub Get_Cell_Data()

Dim Temp1

Selection.Copy

Temp1 = clipboard.GetText(vbcfrtf)
MsgBox Temp1

End sub

It says undefined constant to get the contents of the clipboard. How do I assign the rtf string to variable temp1 in my code.

If anything can go wrong, it WILL.

 

[SkipVought]

How about this...

    Dim doc As Word.Document, wd As Word.Application
    
    Set wd = CreateObject("Word.Application")
    wd.Visible = True
    
    Set doc = wd.Documents.Add
    
    [a1].Copy
    
    doc.Range.PasteSpecial DataType:=wdPasteRTF
    
    Set doc = Nothing
    Set wd = Nothing

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[chemimpex]

Skip, I still don't get the rtf string that i need. Pasting this in word does not give me the rtf string. One way is saving this document as rtf and loading it to a rich text box and taking the textrtf property of the box but i am planning to manipulate 100,000 records and this route is too slow and error prone.
Is there a way to get the rtf string directly from excel cell or at the most from the clipboard if not the cell.
Thanks.

If anything can go wrong, it WILL.