Copying a chart from Excel into a Word Template

[closet]

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]

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:

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]

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:

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.