Copy-pasting 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

 

[Ed from AZ]

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

 

[raphiel2063]

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]

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