VBA script to send charts from excel to specific slides in a powerpoint

Question

Looking for some VBA script that will take excel charts and place them into a PowerPoint presentation at specific slides and locations in that PowerPoint. I have an excel sheet with roughly 40 charts that I have placed all on the same sheet of the workbook. Is there a way to loop through all of these charts, copying them and then placing into the PowerPoint?

I am thinking that I can somehow label the charts in excel so VBA can pick them out, and then match those labels to specific locations in the PowerPoint so that they they will copy there.

To give some context, all of these charts are dynamic and will be changing as I input to new data (think of 40 categories of data in the same format) that needs to be visualized into charts and placed into PowerPoint slides - 40 categories * 40 charts = 1600 copying, pasting, formatting, etc. The slides are in a special format for my company so the formatting for them is already made and there is a shell framework with the graphs missing. So I would to find a way to automate this.

I have a good idea of how to do it in a simplified manner - creating a PPT and copying the charts as pictures to their own slides in the newly created powerpoint, but this misses the main issue of the already made formatting and the charts on same slides:

Sub Macro100()

'Step 1:  Declare your variables
    Dim PP As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim i As Integer


'Step 2:  Check for charts; exit if no charts exist
    Sheets("Slide Data").Select
    If ActiveSheet.ChartObjects.Count < 1 Then
    MsgBox "No charts existing the active sheet"
    Exit Sub
    End If


'Step 3:  Open PowerPoint and create new presentation
    Set PP = New PowerPoint.Application
    Set PPPres = PP.Presentations.Add
    PP.Visible = True


'Step 4:  Start the loop based on chart count
    For i = 1 To ActiveSheet.ChartObjects.Count


'Step 5:  Copy the chart as a picture
    ActiveSheet.ChartObjects(i).Chart.CopyPicture _
    Size:=xlScreen, Format:=xlPicture
    Application.Wait (Now + TimeValue("0:00:1"))


'Step 6:  Count slides and add new slide as next available slide number
    ppSlideCount = PPPres.Slides.Count
    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank)
    PPSlide.Select


'Step 7:  Paste the picture and adjust its position; Go to next chart
    PPSlide.Shapes.Paste.Select
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    Next i


'Step 8:  Memory Cleanup
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PP = Nothing

End Sub

I would like to differ the code to select an already made ppt and placing the charts on the same slides - there will typically be two charts per slide that are equally placed on each slide (left and right of the slide).

I know this is a bit complicated and long post, but any help to any part of problem is greatly appreciated. Please let me know if anything needs further clarification.

a-burge a-burge · Accepted Answer · 2019-10-06T12:29:18

You can name charts in the same way as you name ranges.

Then use this to refer to the chart:

Charts("Slide5Right").CopyPicture...

Added benefit is you don't have to loop through all charts. Might want to create a helper table to loop through instead though.

VBA script to send charts from excel to specific slides in a powerpoint

1 Answers