2

I'm trying to copy text and charts from Excel to Word. The problem is that the chart is always appearing on top of the Word document. How can I add the chart at the end of the Word document? Here is my code:

Sub Test()

Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table


Set WordApp = GetObject(class:="Word.Application")
WordApp.Visible = True
WordApp.Activate

'Create a New Document
Set myDoc = WordApp.Documents.Add

'Copy Excel Text in cell A1 to A3
Worksheets("Rapportage").Select
Range("A1:A3").Select
Selection.Copy

'Paste Excel Text into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
     
'Copy Excel Chart
Worksheets("Rapportage").Select
Range("A4").Select
Selection.Copy

'Paste Chart into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Michel Verberg
  • 21
  • 1
  • Instead of `myDoc.Paragraphs(1).Range.PasteExcelTable` I imagine the syntax should be something like `myDoc.Paragraphs(myDoc.Paragraphs.Count+1).Range.PasteExcelTable` – Marcucciboy2 Oct 22 '20 at 00:33

2 Answers2

1

Replace

'Paste Excel Text into MS Word
myDoc.Paragraphs(1).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

with

'Paste Excel Text into MS Word
'add a paragraph at the end of the document and paste into it
with myDoc.Content
    .InsertParagraphAfter
    .Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
End With
Timothy Rylatt
  • 7,221
  • 2
  • 10
  • 14
0

This happens because in both paste commands you are pasting in Paragraphs(1). Instead, you could just paste using Selection, so it will paste one thing after another in the order that you want (it pastes where the cursor is):

'Paste Excel Text into MS Word
     WordApp.Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

And, in the end:

'Paste Chart into MS Word
     WordApp.Selection.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
Ivan
  • 366
  • 3
  • 7
  • In case you really need to make the cursor go to the end of the document, you could use: WordApp.Selection.EndKey Unit:=6 Or, to go to the beggining of the document: WordApp.Selection.HomeKey Unit:=6 – Ivan Oct 22 '20 at 00:02