Copy Paste (special-unformatted) from Excel to Word

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
Thu Sep 13 10:03:39 CDT 2007

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
Thu Sep 13 10:38:02 CDT 2007

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.

"Helmut Weber" wrote:

> Hi,
>
> try
>
> Selection.PasteAndFormat (wdFormatPlainText)
>
> --
> Greetings from Bavaria, Germany
>
> Helmut Weber, MVP WordVBA
>
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"
>

Ed
Thu Sep 13 11:39:14 CDT 2007

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


On Sep 13, 8:38 am, raphiel2063
<raphiel2...@discussions.microsoft.com> wrote:
> 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.
>
>
>
> "Helmut Weber" wrote:
> > Hi,
>
> > try
>
> > Selection.PasteAndFormat (wdFormatPlainText)
>
> > --
> > Greetings from Bavaria, Germany
>
> > Helmut Weber, MVP WordVBA
>
> > Win XP, Office 2003
> > "red.sys" & Chr$(64) & "t-online.de"- Hide quoted text -
>
> - Show quoted text -

raphiel2063
Wed Sep 19 03:26:01 CDT 2007

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


"Ed from AZ" wrote:

> 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
>
>
> On Sep 13, 8:38 am, raphiel2063
> <raphiel2...@discussions.microsoft.com> wrote:
> > 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.
> >
> >
> >
> > "Helmut Weber" wrote:
> > > Hi,
> >
> > > try
> >
> > > Selection.PasteAndFormat (wdFormatPlainText)
> >
> > > --
> > > Greetings from Bavaria, Germany
> >
> > > Helmut Weber, MVP WordVBA
> >
> > > Win XP, Office 2003
> > > "red.sys" & Chr$(64) & "t-online.de"- Hide quoted text -
> >
> > - Show quoted text -
>
>
>