Copying a chart from Excel into a Word Template

closet

New Member
Joined
Jun 20, 2016
Messages
2
Hi there,

I have created a chart in Excel and want this to appear at the Bookmark 'CIPChart' in a premade Word template. This is my code. The Word template opens fine but I can't get the chart to copy and paste into it. Where am I going wrong?

Sub macro()

Dim word As Object, templateFile As Object

On Error Resume Next

Set word = GetObject(, "word.application") 'gives error 429 if Word is not open
If Err = 429 Then
Set word = CreateObject("word.application") 'creates a Word application
Err.Clear
End If

Set templateFile = word.documents.Add(Template:="G:\SavInv\2014 Investment Prop\CIP Healthcheck\Dummy 4.docx")

Sheets("Performance Results").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = True
word.ActiveDocument.Bookmarks("CIPChart").Range.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _
, DisplayAsIcon:=False
End Sub
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,312
You code doesn't work because, to begin with, you've confused it by naming a variable with the same name as the application you want to use. Furthermore, there is no such Word PasteSpecial argument as 'Format:="Picture (Enhanced Metafile)"'. Your code also unnecessarily activates the worksheet and selects the chart for copying.

Try:
Code:
Sub Demo()
Dim wdApp As Object, wdDoc As Object
Const wdPasteEnhancedMetafile As Long = 9
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add(Template:="G:\SavInv\2014 Investment Prop\CIP Healthcheck\Dummy 4.docx")
Sheets("Performance Results").ChartObjects("Chart 1").Copy
wdDoc.Bookmarks("CIPChart").Range.PasteSpecial DataType:=wdPasteEnhancedMetafile, Link:=False, DisplayAsIcon:=False
wdApp.Visible = True
End Sub
PS: A docx is NOT a template.

PPS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
 

closet

New Member
Joined
Jun 20, 2016
Messages
2
Thank you very much for your help Paul. Your code works perfectly and you have given me some great tips to use in future. I'm just learning - can you tell. :)

Kind regards,


You code doesn't work because, to begin with, you've confused it by naming a variable with the same name as the application you want to use. Furthermore, there is no such Word PasteSpecial argument as 'Format:="Picture (Enhanced Metafile)"'. Your code also unnecessarily activates the worksheet and selects the chart for copying.

Try:
Code:
Sub Demo()
Dim wdApp As Object, wdDoc As Object
Const wdPasteEnhancedMetafile As Long = 9
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add(Template:="G:\SavInv\2014 Investment Prop\CIP Healthcheck\Dummy 4.docx")
Sheets("Performance Results").ChartObjects("Chart 1").Copy
wdDoc.Bookmarks("CIPChart").Range.PasteSpecial DataType:=wdPasteEnhancedMetafile, Link:=False, DisplayAsIcon:=False
wdApp.Visible = True
End Sub
PS: A docx is NOT a template.

PPS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
 

Forum statistics

Threads
1,082,630
Messages
5,366,656
Members
400,909
Latest member
ola97316

Some videos you may like

This Week's Hot Topics

Top