vba 在word中复制并粘贴带有VBA宏的excel图表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22871774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy and paste an excel chart with a VBA macro in word
提问by p929
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
我正在编写一个宏来自动化 Word 中的报告生成。在某个阶段，我需要插入一些图表，该图表位于 excel 的图表表中……但没办法。这是我的代码
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."
但它一直显示错误消息：“运行时错误'5342'：指定的数据类型不可用。”
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.
我不知道为什么它不粘贴图表。我想通过“MSForms.DataObject”使用剪贴板，但我似乎只适用于文本（或字符串）。据我所知，我拥有所需的一切，但显然缺少一些东西。
Any idea?
任何的想法？
采纳答案by Dmitry Pavliv
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 sheetinto new workbook. To fix it, use chart.ChartArea.Copyinstead:
如果您使 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
also note that I've added clean uppart of code to exit from excel application and clean memory.
另请注意，我添加了清理部分代码以退出 excel 应用程序并清理内存。
