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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With