复制并粘贴一个带有VBA宏的excel图表 [英] Copy and paste an excel chart with a VBA macro in word

问题描述

我知道我的问题可能听起来很简单，但是我找不到任何地方的解决方案。我很疲惫。

我在Word中编写一个宏来自动生成一个报表生成器。在某些阶段，我需要插入一些图表，它们以Excel格式显示，但没有办法。这是我的代码

  Sub copy_pic_excel（）
 Dim xlsobj_2 As Object 
 Dim xlsfile_chart As Object 
 Dim chart As Object 
 
设置xlsobj_2 = CreateObject（Excel.Application）
 xlsobj_2.Application.Visible = False 
设置xlsfile_chart = xlsobj_2.Application.Workbooks.Open path_to_file.xlsx）
 
设置图表= xlsfile_chart.Charts（sigma_X_chart）
 chart.Select 
 chart.Copy 
带有选择
。 PasteSpecial Link：= False，DataType：= wdPasteEnhancedMetafile，_ 
放置：= wdInLine，DisplayAsIcon：= False 
结束
 End Sub 
  pre> 
 
 
但它不断显示错误消息：运行时错误5342'：指定的数据类型不可用。
 
 
 
我不知道为什么它不粘贴图表。我想通过'MSForms.DataObject'使用剪贴板，但我似乎只适用于文本（或字符串）。据我所知，我有一切都是必需的，但显然有一些缺失。 
 
 
 
任何想法？
解决方案
 code> xlsobj_2.Application.Visible = True ，您可以看到发生了什么：执行此行 chart.Copy 时，只需将图表复制到新的工作簿中。要修复它，请使用 chart.ChartArea.Copy 替代：
 子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 
设置xlsfile_chart = xlsobj_2.Application.Workbooks.Open（path_to_file.xlsx）
 
设置图= xlsfile_chart.Charts（sigma_X_chart）
 chart.Select 
 chart.ChartArea.Copy 
带有选择
 .PasteSpecial链接：= False，DataType：= wdPasteEnhancedMetafile，_ 
放置：= wdInLine，DisplayAsIcon：= False 
结束
 
'清理
设置xlsfile_chart =没有
 xlsobj_2.Quit 
设置xlsobj_2 =没有
结束Sub 
  
还要注意，我已经添加了清理部分代码退出excel应用程序和清理记忆。
 
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.

                        
这篇关于复制并粘贴一个带有VBA宏的excel图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！