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

Hi, Raphiel. This worked for me in Word / XL 2003:

Sub PasteXLasUnformatted()

Dim wdApp As Object
Dim wdDoc As Object
Dim fNameAndPath As String

fNameAndPath = "C:\Documents and Settings\User.Name\Desktop
\TestMe.doc"

ActiveSheet.Range("A121").Copy

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
Err.Clear
On Error GoTo 0

Set wdDoc = wdApp.Documents.Open(fNameAndPath)
wdApp.Visible = True

With wdDoc.Content
.Font.Name = "Arial"
.Font.Size = 11
.PasteSpecial DataType:=wdPasteText
End With

End Sub


Ed


On Sep 13, 3:22 am, raphiel2063
<raphiel2...@discussions.microsoft.com> wrote:
> 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

Ed

Thanks for the code but it is still not pasted unformatted text as I want it
to. It's still pasting the range as a picture into Word, which when I double
click it opens up a an excel type screen in word.






"Ed from AZ" wrote:

> Hi, Raphiel. This worked for me in Word / XL 2003:
>
> Sub PasteXLasUnformatted()
>
> Dim wdApp As Object
> Dim wdDoc As Object
> Dim fNameAndPath As String
>
> fNameAndPath = "C:\Documents and Settings\User.Name\Desktop
> \TestMe.doc"
>
> ActiveSheet.Range("A121").Copy
>
> On Error Resume Next
> Set wdApp = GetObject(, "Word.Application")
> If Err.Number <> 0 Then
> Set wdApp = CreateObject("Word.Application")
> End If
> Err.Clear
> On Error GoTo 0
>
> Set wdDoc = wdApp.Documents.Open(fNameAndPath)
> wdApp.Visible = True
>
> With wdDoc.Content
> .Font.Name = "Arial"
> .Font.Size = 11
> .PasteSpecial DataType:=wdPasteText
> End With
>
> End Sub
>
>
> Ed
>
>
> On Sep 13, 3:22 am, raphiel2063
> <raphiel2...@discussions.microsoft.com> wrote:
> > 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
>
>
>

Try replacing

..PasteSpecial DataType:=wdPasteText

with

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


Another alternative is simply to paste in the Excel range as a table,
then use Word's Canvert Table To Text to remove all the table
attributes. That's the long way around, but depending on what's
happening, it might be necessary.

What versions of Word and Excel are you using? Is this the whole
macro or is it a piece of a larger program?

Ed


On Sep 13, 8:18 am, raphiel2063
<raphiel2...@discussions.microsoft.com> wrote:
> Ed
>
> Thanks for the code but it is still not pasted unformatted text as I want it
> to. It's still pasting the range as a picture into Word, which when I double
> click it opens up a an excel type screen in word.
>
>
>
> "Ed from AZ" wrote:
> > Hi, Raphiel. This worked for me in Word / XL 2003:
>
> > Sub PasteXLasUnformatted()
>
> > Dim wdApp As Object
> > Dim wdDoc As Object
> > Dim fNameAndPath As String
>
> > fNameAndPath = "C:\Documents and Settings\User.Name\Desktop
> > \TestMe.doc"
>
> > ActiveSheet.Range("A121").Copy
>
> > On Error Resume Next
> > Set wdApp = GetObject(, "Word.Application")
> > If Err.Number <> 0 Then
> > Set wdApp = CreateObject("Word.Application")
> > End If
> > Err.Clear
> > On Error GoTo 0
>
> > Set wdDoc = wdApp.Documents.Open(fNameAndPath)
> > wdApp.Visible = True
>
> > With wdDoc.Content
> > .Font.Name = "Arial"
> > .Font.Size = 11
> > .PasteSpecial DataType:=wdPasteText
> > End With
>
> > End Sub
>
> > Ed
>
> > On Sep 13, 3:22 am, raphiel2063
> > <raphiel2...@discussions.microsoft.com> wrote:
> > > 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- Hide quoted text -
>
> - Show quoted text -