Copy and paste Excel spreadsheet with VBA macro in word

Question

I know that my question may sound / be trivial, but I could not find a solution anywhere ... and I'm exhausted.

I am writing a macro to automate report generation in Word. At some point, I need to insert some kind of chart, which is in the form of a worksheet from Excel ... but not at all. Here is 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 continues to display the error message: "Runtime error" 5342 ": The specified data type is not available."

I have no clue why it is not inserting a chart. I thought to use the clipboard through "MSForms.DataObject", but it seems to me that it only works with text (or lines). As far as I understand, I have everything that is required, but, obviously, something is missing.

Any idea?

Answer 1

excel xlsobj_2.Application.Visible = True, , : chart.Copy, . , chart.ChartArea.Copy :

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

, excel .