Stopping paste text from adding quotation marks?

[oliver]

Hi,
Hope you can help please.
I've inherited some code that uses the .copy method of the range
object in Excel (2003) to copy text to the windows (XP Pro)
clipboard. It then uses Word's PasteAndFormat method of a range
object with arguments Type:=wdFormatPlainText.

It works fine with just a single line of text but if the text includes
a hard return character created by pressing Alt+Enter in Excel, then
the pasted text is appear in quotation marks in the Word doc.

Does anyone know how to prevent this please.
thanks Oliver

 

[Doug Robbins - Word MVP]

Can you use the following instead:

With Selection.Range
.Paste
.Tables(1).ConvertToText
End With


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[oliver]

Thanks for the reply. Could you expand a little though please

The copy code in Excel is simply of the format Range.Copy

The original paste code is...
Set rgeToPaste = mobjDocNew.Bookmarks(strBmk).Range
rgeToPaste.PasteAndFormat Type:=wdFormatPlainText

To which I've added
rgeToPaste.Tables(1).ConvertToText

This doesn't work, I think Tables is not a member of the Range
collection maybe?

 

[Doug Robbins - Word MVP]

My suggestion was to use .Paste instead of .PasteAndFormat
Type:=wdFormatPlainText and then convert what will be pasted as a table to
text. What you have tried does not work because using PasteAndFormat
Type:=wdFormatPlainText does not paste the data from Excel in the form of a
table.

The following should work:

Set rgeToPaste = mobjDocNew.Bookmarks(strBmk).Range
With rgeToPaste
.Paste
.Tables(1).ConvertToText
End With

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Thanks for the reply. Could you expand a little though please

The copy code in Excel is simply of the format Range.Copy

The original paste code is...
Set rgeToPaste = mobjDocNew.Bookmarks(strBmk).Range
rgeToPaste.PasteAndFormat Type:=wdFormatPlainText

To which I've added
rgeToPaste.Tables(1).ConvertToText

This doesn't work, I think Tables is not a member of the Range
collection maybe?

 

[oliver]

Hi again,
That removes the quotation marks.... erm...but it also removes the
table formating the bookmark sits inside in the word doc
Can you think of any other method please?
Thanks Oliver

 

[Doug Robbins - Word MVP]

What was the formatting that was removed? It may be possible to use some
additional code to re-apply it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Hi again,
That removes the quotation marks.... erm...but it also removes the
table formating the bookmark sits inside in the word doc
Can you think of any other method please?
Thanks Oliver