Link to home
Start Free TrialLog in
Avatar of mpdillon
mpdillon

asked on

Cannot paste to Excel

I have a Visual Basic 2008 application which copies a RTF stream to the clipboard. I am trying to paste this into Excel programmatically. But it is not working.

When the code executes nothing is placed in Excel. The information is successfully copied into the Clipboard. I know this because I can paste the RTF document manually into Word with Ctrl-V. Oddly, the PASTE option is not available in Excel when the RTF document is programmatically copied into the clipboard.

However, if I copy the RTF document from Word with Ctrl_C and then go to Excel, Paste is available.

Summary                                Paste into Word                             Paste into Excel
Programmatic Copy                               Y                                                  N
Manual Copy from Word                        Y                                                  N


I have included the code (4 line) in the attached Word document. Also in the Word document is the Rtf document I am trying to copy and paste. Notice the Greek and subscript characters.

The variable sExportString represents the RTF document converted into a String. This is in the Word document also.

I am puzzled. Thank you for your assistance.

Pat
Avatar of FamousMortimer
FamousMortimer
Flag of United States of America image

Hi Pat,

Have you tried using Clipboard.GetText and setting the range value to it?

edit: Also, note that there is no file attached.
Avatar of mpdillon
mpdillon

ASKER

Here is the file. I will try the GetText in a minute.
CutAndPasteError.doc
ASKER CERTIFIED SOLUTION
Avatar of FamousMortimer
FamousMortimer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Progress! But not quite there. The Get text method returns nothing without an argument. If I place "TextDataFormat.Rtf" as the argument to GetText, the paste method place the text into the cell. However, the Paste method returns the text and not the RTF formatted text.

I looked at GetDataObject but it did not place any text into Excel either.

Any ideas?


thanks,
pat
Excel will not accept RTF data from the Clipboard. But you can copy foramatted data from another office application and paste it into Excel. So the work around is past RTF data into WORD. Copy from Word using default settings and paste into Excel.

Please see my code below.
(Word is referenced in the project because I wanted the intellisense help. Excel is late bound and not referenced to avoid version conflicts.)

Dim objWord As New Microsoft.Office.Interop.Word.Application
objWord.Documents.Add(Template:="Normal", NewTemplate:=False, DocumentType:=0)

On Error Resume Next
objExcel = GetObject(, "Excel.Application")

If Err.Number = 429 Then
    objExcel = CreateObject("Excel.application")
End If

On Error GoTo 0
'
objExcel.Workbooks.Add()
'Remove Worksheets 1 & 2
For i = 1 To objExcel.Worksheets.Count - 1
    objExcel.Application.DisplayAlerts = False
    objExcel.Worksheets(objExcel.Worksheets.Count).Delete()
Next i
objExcel.Application.DisplayAlerts = True
WorksheetNameString = "Sheet1"
objExcel.Worksheets(objExcel.Worksheets.Count).name = WorksheetNameString

My.Computer.Clipboard.SetText(sExcelString, TextDataFormat.Rtf)
                                    '

objWord.Selection.PasteAndFormat(0)
objWord.Selection.WholeStory()
objWord.Selection.Cut()
'

CellAddressString = objExcel.worksheets(WorksheetNameString).cells(r, 3).address(False, False)
objExcel.worksheets(WorksheetNameString).range(CellAddressString).value = My.Computer.Clipboard.GetData(0)
objExcel.worksheets(WorksheetNameString).range(CellAddressString).select()
objExcel.activesheet.paste()