copy from excel to word

[Robin]

Hi. I am trying to copy multiple cells from excel to word. I can do this
once with the following code, but on the second iteration, I get a 'command
error' at the pastespecial stage. I think its because I am not activating
Excel properly at the end of the first iteration, but I can't crack this.
Any help would be appreciated!

Sub ExcelDataToWorddoc()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document

For i = 1 To 5
Set WordApp = GetObject(, "Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.ActiveDocument
ThisWorkbook.Sheets("from_Forms").Range("Strengths_Start").Offset(i - 1,
0).Copy
WordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText,
Placement:=wdInLine, DisplayAsIcon:=False
Application.CutCopyMode = False
Set WordDoc = Nothing
Set WordApp = Nothing
Sheets("from_Forms").Activate
Next i
End Sub

 

[OssieMac]

Hi Robin,

The line you need is
AppActivate "Microsoft Word".

Also you can keep the setting of the word object variables outside of the
loop so they are only set once so try the following.

Sub ExcelDataToWorddoc()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim i As Long

Set WordApp = GetObject(, "Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.ActiveDocument

For i = 1 To 5
ThisWorkbook.Sheets("from_Forms").Range("Strengths_Start").Offset(i - 1, _
0).Copy

AppActivate "Microsoft Word"

WordApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
Placement:=wdInLine, DisplayAsIcon:=False
Application.CutCopyMode = False
Next i

Set WordDoc = Nothing
Set WordApp = Nothing
Sheets("from_Forms").Activate
End Sub

 

[OssieMac]

Hi Again Robin,

You also need the following line so that you finish up with the correct
window when you activate your worksheet.

AppActivate "Microsoft Excel" 'Insert before the following line
Sheets("from_Forms").Activate

 

[Robin Davies]

Hi Again Robin,

You also need the following line so that you finish up with the correct
window when you activate your worksheet.

AppActivate "Microsoft Excel"  'Insert before the following line
Sheets("from_Forms").Activate

Thanks Ossie!

I know its close, but its still bombs out on the second pass at the
pastespecial step due to a "Command failed" error

I also had to change "Microsoft Word" to "Word"

Sub ExcelDataToWorddoc()

Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim i As Long

Set WordApp = GetObject(, "Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.ActiveDocument

For i = 1 To 5
ThisWorkbook.Sheets("from_Forms").Range("Strengths_Start").Offset(i -
1, _
0).Copy

AppActivate "Word"

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

AppActivate "Microsoft Excel"
Sheets("from_Forms").Activate

Next i

Set WordDoc = Nothing
Set WordApp = Nothing

End Sub

 

[OssieMac]

Hi again Robin,

Perhaps a little more info so that I can attempt to emulate exactly what you
have.

What version of Office (Word and Excel) are you using?

What is the actual range of of the defined name "Strengths_Start"?

I tested it in Office XP (2002) and it works fine with or without
AppActivate. On reviewing my code, AppActivate is really only required once
(if at all) because Excel is not being activated again during the code until
after the Copy/Paste.

I wonder if you need a line feed in Word after each paste. After the paste,
the selection is actually at the end of the last line pasted so if you want
each paste to be under the previous line then a line feed is required
otherwise the paste commences at the end of the last line pasted.

Sub ExcelDataToWorddoc()
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim i As Long

Set WordApp = GetObject(, "Word.Application")
WordApp.Visible = True
Set WordDoc = WordApp.ActiveDocument

AppActivate ("Microsoft Word")

For i = 1 To 5

ThisWorkbook.Sheets("from_Forms") _
.Range("Strengths_Start") _
.Offset(i - 1, 0).Copy

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

Application.CutCopyMode = False

WordApp.Selection.TypeParagraph 'Line feed in word

Next i

Set WordDoc = Nothing
Set WordApp = Nothing

AppActivate "Microsoft Excel"
Sheets("from_Forms").Activate

End Sub

Both of the following work in Word 2002
AppActivate ("Microsoft Word")
AppActivate ("Word")

but only the following for Excel
AppActivate ("Microsoft Excel")