使用VBA将Excel图表粘贴到PowerPoint中

Question

我正在尝试创建一个Excel宏，将显示在Excel工作表上的图表复制，并将它们“粘贴”（特殊粘贴）到PowerPoint中。我遇到的问题是如何将每个图表粘贴到不同的幻灯片上？我完全不知道语法..

这是我目前为止所拥有的（它可以工作，但只能粘贴到第一张幻灯片）：

Sub graphics3()

Sheets("Chart1").Select
ActiveSheet.ChartObjects("Chart1").Activate
ActiveChart.ChartArea.Copy
Sheets("Graphs").Select
range("A1").Select
ActiveSheet.Paste
     With ActiveChart.Parent
     .Height = 425 ' resize
     .Width = 645  ' resize
     .Top = 1    ' reposition
     .Left = 1   ' reposition
 End With

Dim PPT As Object
Set PPT = CreateObject("PowerPoint.Application")
PPT.Visible = True
PPT.Presentations.Open Filename:="locationwherepptxis"

Set PPApp = GetObject("Powerpoint.Application")
Set PPPres = PPApp.activepresentation
Set PPSlide = PPPres.slides _
    (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)

' Copy chart as a picture
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
    Format:=xlPicture

' Paste chart
PPSlide.Shapes.Paste.Select

' Align pasted chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

- thedeepfield

Answer 1

因为我没有你的文件位置，所以我在下面附上了一份程序：

1. 创建了一个新的PowerPoint实例（使用晚绑定方式，因此需要定义ppViewSlide等常量）
2. 循环遍历名为Chart1的工作表中的每个图表（与你的示例相同）
3. 添加一个新幻灯片
4. 粘贴每个图表，然后重复这个过程

你是否需要在导出大小之前格式化每个图表图片？还是可以更改默认的图表大小呢？

Const ppLayoutBlank = 2
Const ppViewSlide = 1

Sub ExportChartstoPowerPoint()
    Dim PPApp As Object
    Dim chr
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Presentations.Add
    PPApp.ActiveWindow.ViewType = ppViewSlide
    For Each chr In Sheets("Chart1").ChartObjects
        PPApp.ActivePresentation.Slides.Add PPApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
        PPApp.ActiveWindow.View.GotoSlide PPApp.ActivePresentation.Slides.Count
        chr.Select
        ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
        PPApp.ActiveWindow.View.Paste
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    Next chr
    PPApp.Visible = True
End Sub

- brettdj

---

谢谢Jean-Francois。这是一个公正的问题-简短的答案是个人喜好。通常，如果可以自动化的对象有多个版本，我会晚期绑定，并且我发现Q&A论坛中的用户可能会在设置引用时遇到问题。而我在我的Duplicate Master插件中使用了早期绑定，因为它仅绑定到文件脚本库，这样可以节省20-30%的运行时间，并且作为插件的一部分，它会自动安装给用户。 - brettdj

Answer 2

使用函数从Excel绘制6个图表并将其导入到PPT中

Option Base 1
Public ppApp As PowerPoint.Application

Sub CopyChart()

Dim wb As Workbook, ws As Worksheet
Dim oPPTPres As PowerPoint.Presentation
Dim myPPT As String
myPPT = "C:\LearnPPT\MyPresentation2.pptx"

Set ppApp = CreateObject("PowerPoint.Application")
'Set oPPTPres = ppApp.Presentations("MyPresentation2.pptx")
Set oPPTPres = ppApp.Presentations.Open(Filename:=myPPT)
ppApp.Visible = True
Set wb = ThisWorkbook
Set ws = wb.Sheets(1)

i = 1

For Each shp In ws.Shapes

    strShapename = "C" & i
    ws.Shapes(shp.Name).Name = strShapename
    'shpArray.Add (shp)
    i = i + 1

Next shp

Call Plot6Chart(oPPTPres, 2, ws.Shapes(1), ws.Shapes(2), ws.Shapes(3), ws.Shapes(4), ws.Shapes(5), ws.Shapes(6))

End Sub
Function Plot6Chart(pPres As Presentation, SlideNo As Long, ParamArray cCharts())

Dim oSh As Shape
Dim pSlide As Slide
Dim lLeft As Long, lTop As Long

Application.CutCopyMode = False
Set pSlide = pPres.Slides(SlideNo)

For i = 0 To UBound(cCharts)

    cCharts(i).Copy
    ppApp.ActiveWindow.View.GotoSlide SlideNo
    pSlide.Shapes.Paste
    Application.CutCopyMode = False


    If i = 0 Then ' 1st Chart
        lTop = 0
        lLeft = 0
    ElseIf i = 1 Then ' 2ndChart
        lLeft = lLeft + 240
    ElseIf i = 2 Then ' 3rd Chart
        lLeft = lLeft + 240
    ElseIf i = 3 Then ' 4th Chart
        lTop = lTop + 270
        lLeft = 0
    ElseIf i = 4 Then ' 5th Chart
        lLeft = lLeft + 240
    ElseIf i = 5 Then ' 6th Chart
        lLeft = lLeft + 240
    End If

    pSlide.Shapes(cCharts(i).Name).Left = lLeft
    pSlide.Shapes(cCharts(i).Name).Top = lTop

Next i

Set oSh = Nothing
Set pSlide = Nothing
Set oPPTPres = Nothing
Set ppApp = Nothing
Set pPres = Nothing

End Function

- Vinayak K