Copy Paste (special-unformatted) from Excel to Word

[raphiel2063]

Hi

I'm trying to copy-paste (unformatted), a series of cells from excel into
Word. I've got the below macro which does some of this but it keeps pasting
the whole table either as a picture or a normal table (if you use the
disabled line instead of the enabled paste line).

However, if I manually copy-paste special and select unformatted text I get
a load of text and no tables or borders which is what I want.

Any ideas how to achieve this with the below macro?

Sub OpenAWordFile()
Dim wordApp As Object
Dim fNameAndPath As String
ActiveSheet.Range("I5:I51").Copy
fNameAndPath = "C:\Documents and Settings\tom.jordan\My
Documents\Projects\FFEC Headed Paper.doc"
Set wordApp = CreateObject("Word.Application")
With wordApp
.Documents.Open (fNameAndPath)
.Visible = True
.Selection.PasteSpecial DataType:=wdPasteText
'.Selection.PasteAndFormat (wdPasteDefault)
.Selection.WholeStory
.Selection.Font.Name = "Arial"
.Selection.Font.Size = 11
End With
Set wordApp = Nothing
Application.CutCopyMode = False
End Sub

 

[Helmut Weber]

Hi,

try

Selection.PasteAndFormat (wdFormatPlainText)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[raphiel2063]

I tried using that instead. I still just get the selection of cells pasted
into word with the tables, borders etc as usual. If I manually copy and then
paste-special (unformatted text) I get what I want, a page of normal text
without the tables/borders, but I want to automate it for people to use.

 

[Ed from AZ]

Hey, Raphiel:

Next time please include BOTH newsgroups in the posting, rather than
posting the same question in two separate postings. If you had done
that, Helmut and I would not have duplicated our answers to you.

Ed

 

[raphiel2063]

Ed

Sorry for the two posts. I tried to insert the bit of code and added
..selection. before it as it throwing up an error message. It now reads the
below.

.Selection.PasteSpecial Link:=False, DataType:=wdPasteText,
Placement:= _
wdInLine, DisplayAsIcon:=False

Unfortuntately, it now throws up another error saying "Run-time error
'5941': Application-defined or object-defined error" but doesn't highlight
anything in the macro. The full macro is as below.

I tried doing it the other way (convert table to text) but can't figure out
how to select the pasted table and perform the conversion as
..selection.wholestory doesn't let me perform the conversion on it. Any ideas
on either of these?

Dim wordApp As Object
Dim fNameAndPath As String

ActiveSheet.Range("I5:I51").Copy
fNameAndPath = "C:\Documents and Settings\tom.jordan\My
Documents\Projects\FFEC Headed Paper.doc"
Set wordApp = CreateObject("Word.Application")
With wordApp
.Documents.Open (fNameAndPath)
.Visible = True
.Selection.PasteSpecial Link:=False, DataType:=wdPasteText,
Placement:= _
wdInLine, DisplayAsIcon:=False

.Selection.WholeStory
.Selection.Font.Name = "Arial"
.Selection.Font.Size = 11
.Selection.Rows.ConvertToText Separator:=wdSeparateByParagraphs, _
NestedTables:=True
End With
Set wordApp = Nothing
Application.CutCopyMode = False
End Sub