Pasting from the clipboard into excel

[cruzmsl]

I am trying to paste Rich Text strings into Excel.

I can copy to the clipboard and paste into Word just fine.

I copy them to the clipboard with this

Public Sub Paste(sRTF As String)
'sRTF represents the rich text formatted string to paste into Word
RTB1.TextRTF = sRTF
'Copy the contents of the Rich Text to the clipboard
Dim lSuccess As Long
Dim lRTF As Long
Dim hGlobal As Long
Dim lpString As Long
lSuccess = OpenClipboard(Me.hwnd)
lRTF = RegisterClipboardFormat("Rich Text Format&quot
lSuccess = EmptyClipboard
hGlobal = GlobalAlloc(GMEM_MOVEABLE Or GMEM_DDESHARE, Len(sRTF))
lpString = GlobalLock(hGlobal)

CopyMemory lpString, ByVal sRTF, Len(sRTF)
GlobalUnlock hGlobal
SetClipboardData lRTF, hGlobal
CloseClipboard
GlobalFree hGlobal
End Sub

But when I try to paste into Excel it fails.

ActiveSheet.Paste Destination:=Worksheets("Sheet1&quot.Range("D22&quot

It appears Excel is not interpreting the data from the clipboard.
I have tried to view the data on the clipboard

MsgBox (Clipboard.GetText())

But I get a blank msgbox

After the app crashes I can open Word and paste and I get the string I copied there.

Any Ideas??

 

[RobBroekhuis]

The .paste method is set up to work on a copied RANGE. You can trick Excel into pasting into the active cell by using:

Application.CommandBars(1).Controls("Edit&quot.Controls("Paste&quot.Execute

which executes the paste command from the edit menu. This will paste formatted text just fine.
Rob

 

[cruzmsl]

Tried
Application.CommandBars(1).Controls("Edit&quot.Controls("Paste&quot.Execute

It still fails.
It seems to be related to the way the data is written to the clipboard.
After the app crashes I can open word and paste the formatted string.
I can open Excel and it will not paste anything. I can open up the office clipboard viewer and it shows nothing. But it still pastes fine from Word.

???

 

[RobBroekhuis]

Can you manually paste into Excel by hitting ^V after selecting a cell? Rob

 

[cruzmsl]

No, ^V does not work with Excel but does with Word

 

[RobBroekhuis]

If you paste into Word, then immediately copy (^C) in Word, can you paste into Excel?
Rob

 

[cruzmsl]

Yes Copying the formated string from Word(^C) to Excel(^V) manually works.
I changed the code. I am now poulating a Rich Text Box with the Rich Text string from my db. I then Copy the string from the RTB to the clipboard and paste it to Excel
When I Get/Put to the clipboard I am using Rich text format.
With this code I am getting the rich text string on the Excel spread sheet. So how can I get Excel to interpret this as Rich Text?

Clipboard.Clear
RTB1.SelStart = 0
RTB1.SelLength = Len(RTB1.Text)
Clipboard.SetText RTB1.SelRTF, &HBF01
Worksheets("Sheet1&quot.Cells(x, 3).Select
Worksheets("Sheet1&quot.Cells(x, 3).Value = Clipboard.GetText(&HBF01)

 

[RobBroekhuis]

What do you mean by how can I get Excel to interpret this as Rich Text? Do you need help pasting into Excel, or further manipulating once it's in Excel?
For the former, see my post above. For the latter, the formatting is now reflected in the activecell.characters object, which has all the bold/italic/font/size information.
Does that help?

Rob

 

[cruzmsl]

Interpret the RichText string as Formated Text
ie..
{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0\fnil\fcharset0 Times New Roman;}{\f1\fnil\fcharset2 Symbol;}}\uc1\pard\lang1033\f0\fs24 L-\f1 a\f0 -Glycerophosphorylcholine (cadmium chloride adduct)}

as

L-(greek alpha)-Glycerophosphorylcholine (cadmium chloride adduct)

 

[RobBroekhuis]

Ah. I see. You're getting the coded string, not the actual rich text. I know this is not what you want to hear, but it may be easier to just build the formatted text in Excel from whatever the native source was. If you can provide some more information on the background of what you are trying to accomplish, there may be an easier way than pasting...
Rob

 

[cruzmsl]

I have a set of chemical names I need to store in my SQL database.
The problem is a lot of them have a greek symbol in the name.
ie...L-(greek alpha)-Glycerophosphorylcholine (cadmium chloride adduct)
So I need to use one font for the greek symbol and one font for the rest of the name. I have been able to format them in a RichText Box and store the RichText code in the database.
These names are a real pain for me. We use them in a legacy paradox based system, on the web and in VB applications.
The Paradox app uses an old character set that Windows bases apps cannot interpret the greek characters correctly.
On the web I have to use a different font for the greek characters to display correctly. So for the windows based apps I have built this RichText database.

I am now trying to output these into an Excel spread sheet to use in a printed list.

 

[RobBroekhuis]

If you can somehow designate the Greek characters in the source string (e.g., by preceding them with a ~) then you can easily process the string in Excel, e.g.

sub PutGreek(s As String)
Dim i As Integer, s1 As String, s2 As String
For i = 1 To Len(s)
If Mid(s, i, 1) = "~" Then
s2 = s2 + "*"
i = i + 1
s1 = s1 + Mid(s, i, 1)
Else
s1 = s1 + Mid(s, i, 1)
s2 = s2 + " "
End If
Next i
ActiveCell = s1
For i = 1 To Len(s1)
If Mid(s2, i, 1) = "*" Then
ActiveCell.Characters(i, 1).Font.Name = "Symbol"
End If
Next i
End Sub
Rob

 

[cruzmsl]

I thought of that, but the greek symbols are not the only problem. Some parts need to be itallics, some bold.
It gets to be a mess.
It seems simpler to use the richtext.
Plus there is a list of ~4000 of these to reformat would take days.
I have an app that uses these strings to build Word documents. It works fine.
Who knows, I may have to reformat????

 

[cruzmsl]

Well since I have an app that paste these strings into word successfully I tried pasting it into word and then copying it and pasting into Excel.
It works...it's ugly but it works.
Still would like to know why it will not paste directly into Excel?????