Pasting RTF Data into Excel Range

[Nick]

Hi there,

I'm trying to paste a load of RTF data into an Excel range but the
pastespecial method always fails,

My.Computer.Clipboard.SetData(System.Windows.Forms.DataFormats.Rtf,
iContent)
Dim pObjRange As Object = pObjWorksheet.Range("A1")
pObjRange.Select()
Call pObjWorksheet.PasteSpecial(System.Windows.Forms.DataFormats.Rtf)

iContent being the RTF data which has come straight from an rtf file and
stored in a string. I know the data is fine as I'm using the same data for
other purposes, only problem is, Excel doesn't seem to want it and gives me
no reason why the pastespecial method failed.

Any ideas what I'm doing wrong?

Nick.

 

[Nick]

I can't really believe how poorly documented this method is. Every example
I have seen passes a string as the format parameter, but I have just found
some samples that pass an integer, talk about lack of consistency.

So I have found within the Excel object library an xlClipboardFormatRTF
constant with a value of 7. Still, even with this, the method still fails.

I'm at a loss really, this worked fine with PowerPoint.

Nick.

 

[\Ji Zhou [MSFT]\]

Hello Nick,

The WorkSheet.PasteSpecial is not supposed to be used as the above codes.
See this document, http://msdn.microsoft.com/en-us/library/bb179201.aspx.
The first parameter expect an string which represent the format's name like
"Unicode Text", "Text", and "Microsoft Word Document Object" and so on.

Far from my test, I did not find any common way to paste the text with
format from other none Office applications into Excel. So, I think we have
to use Word as a bridge to achieve this objective. Thus, you can try the
following code which works fine on my side.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
My.Computer.Clipboard.SetData(System.Windows.Forms.DataFormats.Rtf,
Me.RichTextBox1.Rtf)

Dim word As Word.Application
word = New Word.Application
word.Visible = False
word.Documents.Add()
word.Selection.Paste()
word.ActiveDocument.Content.Select()
word.Selection.Copy()

Dim pObjWorksheet As Excel.Worksheet = app.ActiveSheet
Dim pObjRange As Object = pObjWorksheet.Range("A1")
pObjRange.Select()
pObjWorksheet.Paste()
End Sub

Hope it helps! Have a nice weekend!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Nick]

Hi there Ji,

That's excellent, a little more convoluted than I had originally hoped but
that works great now. Thanks a million for your help.

Nick.