将图表从excel复制到powerpoint，保持源格式和嵌入图表

I am currently trying to copy some charts from excel to powerpoint using vba and I am currently copying them as pictures to sever the link to the excel data but it would be really useful if I could copy them as embedded charts I know to use the:

我目前正在尝试使用vba将一些图表从excel复制到powerpoint,我目前正在将它们复制为图片以切断到excel数据的链接,但如果我可以将它们复制为嵌入式图表,我知道使用以下内容将非常有用:

CommandBars.ExecuteMso "PasteExcelChartSourceFormatting"

Method but I can't quite figure out how to go about integrating it into my code in order to use it

方法,但我无法弄清楚如何将其集成到我的代码中以便使用它

Set ppProgram = CreateObject("PowerPoint.Application")
Set PowerPointApp = GetObject(, "PowerPoint.Application")

Set myPresentation = PowerPointApp.Presentations("Filename.pptx")

'Copy Excel Range for Chart 1
WsGraph.ChartObjects("Chart 1").Chart.ChartArea.Copy
Set mySlide = myPresentation.Slides(5)
'Paste to PowerPoint and position
mySlide.Shapes.PasteSpecial DataType:=ppPasteEnhancedMetafile
Set myShapeRange = mySlide.Shapes(46)
myShapeRange.Left = 60
myShapeRange.Top = 143
myShapeRange.ZOrder msoSendToBack
myShapeRange.ZOrder msoBringForward

Can anyone help me with changing my paste special to pasting as an embedded chart?

任何人都可以帮我改变我的粘贴特殊粘贴作为嵌入式图表吗?

1 个解决方案

#1

With your Excel and Powerpoint objects the following should work:

使用Excel和Powerpoint对象时,以下内容应该有效:

'copy the chart from Excel
    xlSheet.ChartObjects(ChartName).Select
    xlSheet.ChartObjects(ChartName).Copy
'Select Slide
    Set mySlide = myPresentation.Slides(Charts(r).SlideName)
    mySlide.Select
'stall to make sure the slide is selected
    For k = 1 To 1000
        DoEvents
    Next k
'paste on selected slide
    PPApp.CommandBars.ExecuteMso ("PasteSourceFormatting")
    PPApp.CommandBars.ReleaseFocus
'sit and wait for changes to be made
    For k = 1 To 5000
        DoEvents
    Next k

The wait loops are in there because my Power Point was running slow and throwing errors.

等待循环在那里,因为我的Power Point运行缓慢且抛出错误。

The only problem with this is that it selects the chart and then the slide so it runs a little slower (and has a flashing screen if you use it in a loop for multiple charts) but when I tried to do it straight I kept getting pastes in the wrong slides and such.

唯一的问题是,它选择图表,然后选择幻灯片,使其运行速度稍慢(如果您在多个图表的循环中使用它,则会有一个闪烁的屏幕)但是当我尝试直接进行时,我不断获得粘贴在错误的幻灯片等。

Another issue with this approach is you have no reference to the chart after it is pasted so if you would like to resize/format it it needs to be done right after using PPApp.ActiveWindow.Selection.ShapeRange or something of the sort.

这种方法的另一个问题是你粘贴后没有参考图表,所以如果你想调整大小/格式化它需要在使用PPApp.ActiveWindow.Selection.ShapeRange或类似的东西之后立即完成。

#1