Copy and paste an excel chart with a VBA macro in word

Question

I'm aware that my question may sound/be trivial, but I couldn't find the solution anywhere...and I'm exhausted.

I'm writing a macro to automatize a report generation in Word. At some stage, I need to insert some chart, which is located as a chartsheet from excel...but no way. Here's my code

Sub copy_pic_excel()
Dim xlsobj_2 As Object
Dim xlsfile_chart As Object
Dim chart As Object

Set xlsobj_2 = CreateObject("Excel.Application")
xlsobj_2.Application.Visible = False
Set xlsfile_chart = xlsobj_2.Application.Workbooks.Open("path_to_file.xlsx")

Set chart = xlsfile_chart.Charts("sigma_X_chart")
chart.Select
chart.Copy
With Selection
.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
Placement:=wdInLine, DisplayAsIcon:=False
End With
End Sub

But it keeps showing the error message: "Run-time error '5342': The specified data type is unavailable."

I have no clue why it isn't pasting the chart. I thought to use the clipboard via 'MSForms.DataObject', but i seems that it only works with text (or strings). As far as I understand I have everything that is required, but obviously there's something missing.

Any idea?

Welcome to Stack Overflow! I upvoted your question, but next time try to avoid posting "thank you" at the end of your post- the guidelines here attempt to make the question as clear and concise as possible. I edited that part for you :) Again, welcome to SO! — Blue Ice
– Blue Ice, Commented Apr 4, 2014 at 19:49

Dmitry Pavliv · Accepted Answer · 2014-04-04 20:06:13Z

6

If you make excel application visible xlsobj_2.Application.Visible = True, you can see what really happened: when you execute this line chart.Copy, it just copies chart sheet into new workbook. To fix it, use chart.ChartArea.Copy instead:

Sub copy_pic_excel()
    Dim xlsobj_2 As Object
    Dim xlsfile_chart As Object
    Dim chart As Object

    Set xlsobj_2 = CreateObject("Excel.Application")
    xlsobj_2.Application.Visible = False
    Set xlsfile_chart = xlsobj_2.Application.Workbooks.Open("path_to_file.xlsx")

    Set chart = xlsfile_chart.Charts("sigma_X_chart")
    chart.Select
    chart.ChartArea.Copy
    With Selection
       .PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
            Placement:=wdInLine, DisplayAsIcon:=False
    End With

    'clean up
    Set xlsfile_chart = Nothing
    xlsobj_2.Quit
    Set xlsobj_2 = Nothing
End Sub

also note that I've added clean up part of code to exit from excel application and clean memory.

answered Apr 4, 2014 at 20:06

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

p929 Over a year ago

Great. It's working now. Wow, so stupid from my part. the 'chart.ChartArea.Copy' solve it! thx

Collectives™ on Stack Overflow

Copy and paste an excel chart with a VBA macro in word

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related