问题描述
我知道我的问题可能听起来很简单,但是我找不到任何地方的解决方案。我很疲惫。
我在Word中编写一个宏来自动生成一个报表生成器。在某些阶段,我需要插入一些图表,它们以Excel格式显示,但没有办法。这是我的代码
Sub copy_pic_excel()
pre>
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
但它不断显示错误消息:运行时错误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 linechart.Copy
, it just copies chart sheet into new workbook. To fix it, usechart.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图表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!