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
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
ASKER
Here is the file. I will try the GetText in a minute.
CutAndPasteError.doc
CutAndPasteError.doc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I looked at GetDataObject but it did not place any text into Excel either.
Any ideas?
thanks,
pat
ASKER
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.W ord.Applic ation
objWord.Documents.Add(Temp late:="Nor mal", NewTemplate:=False, DocumentType:=0)
On Error Resume Next
objExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then
objExcel = CreateObject("Excel.applic ation")
End If
On Error GoTo 0
'
objExcel.Workbooks.Add()
'Remove Worksheets 1 & 2
For i = 1 To objExcel.Worksheets.Count - 1
objExcel.Application.Displ ayAlerts = False
objExcel.Worksheets(objExc el.Workshe ets.Count) .Delete()
Next i
objExcel.Application.Displ ayAlerts = True
WorksheetNameString = "Sheet1"
objExcel.Worksheets(objExc el.Workshe ets.Count) .name = WorksheetNameString
My.Computer.Clipboard.SetT ext(sExcel String, TextDataFormat.Rtf)
'
objWord.Selection.PasteAnd Format(0)
objWord.Selection.WholeSto ry()
objWord.Selection.Cut()
'
CellAddressString = objExcel.worksheets(Worksh eetNameStr ing).cells (r, 3).address(False, False)
objExcel.worksheets(Worksh eetNameStr ing).range (CellAddre ssString). value = My.Computer.Clipboard.GetD ata(0)
objExcel.worksheets(Worksh eetNameStr ing).range (CellAddre ssString). select()
objExcel.activesheet.paste ()
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.W
objWord.Documents.Add(Temp
On Error Resume Next
objExcel = GetObject(, "Excel.Application")
If Err.Number = 429 Then
objExcel = CreateObject("Excel.applic
End If
On Error GoTo 0
'
objExcel.Workbooks.Add()
'Remove Worksheets 1 & 2
For i = 1 To objExcel.Worksheets.Count - 1
objExcel.Application.Displ
objExcel.Worksheets(objExc
Next i
objExcel.Application.Displ
WorksheetNameString = "Sheet1"
objExcel.Worksheets(objExc
My.Computer.Clipboard.SetT
'
objWord.Selection.PasteAnd
objWord.Selection.WholeSto
objWord.Selection.Cut()
'
CellAddressString = objExcel.worksheets(Worksh
objExcel.worksheets(Worksh
objExcel.worksheets(Worksh
objExcel.activesheet.paste
Have you tried using Clipboard.GetText and setting the range value to it?
edit: Also, note that there is no file attached.