Copying all charts from an Excel Sheet to a Powerpoint slide

Question

I have built a workbook to facilitate the creation of a monthly report presentation I am in charge of. The workbook has some data sheets, some processing sheets and then numbered sheets which contain the charts I need to paste to the corresponding slide. So far, I've built the VBA for opening the PowerPoint template and looping through each excel sheet, and discriminating which sheet names are numeric, and then activating the corresponding slide on the powerpoint template.

Unlike other solutions to similar problems I've found, I'd like to copy all charts from each numbered sheet to each slide at a time, as they are different in shape, quantities and disposition for each sheet/slide. I've mostly only found people copying one chart at a time and pastying as image, which will also not work for me (I need to fine tune data labels and position on the final slide). Any hints as to how could I achieve that?

Here's what my code looks like so far:

Sub CriarSlides()

Dim pptApp As Powerpoint.Application
Dim pptPres As Powerpoint.Presentation
Dim strFileToOpen As Variant
Dim strFileName As String, Hosp As String
Dim datawb As Workbook
Dim xlsCounter As Integer, xlsSlide As Integer


Set datawb = ThisWorkbook


strFileToOpen = Application.GetOpenFilename _
FileFilter:="Powerpoint Files *.pptx (*.pptx),")
If strFileToOpen = False Then
   Exit Sub
Else
   Set pptApp = New Powerpoint.Application
   pptApp.Visible = True
   pptApp.Presentations.Open Filename:=strFileToOpen, ReadOnly:=msoFalse, Untitled:=msoTrue
   Set pptPres = pptApp.Presentations(1)
End If

For xlsCounter = datawb.Worksheets.Count To 1 Step -1
    If IsNumeric(datawb.Worksheets(xlsCounter).Name) Then
       xlsSlide = datawb.Worksheets(xlsCounter).Name

' This is the problematic part

        Debug.Print xlsSlide
    End If
Next xlsCounter
End Sub

1) How can you make sure that the sheet's name corresponds to an existing slide 2) since you have many charts per sheet and you dont want them pasted in the same slide, how do you want to handle that? 3) Isn't it simpler to just add a slide per chart object? — A.S.H
I do have many charts per sheet (maximum 4), and they all have to be pasted on the same slide. Sorry if I was not clear on this. Here's an exemple: a Sheet is named "8" because all the charts on it should be pasted on slide 8. Also, I made sure that the sheet's name correspond to the existing slides because the slides template have been around for a long time and the charts have been pasted manually every month. — Gabriel Menezes

A.S.H A.S.H · Accepted Answer · 2017-03-16T21:52:23

With the following modified code you can paste the chart-objects of each sheet in the corresponding slide:

Sub CriarSlides()
    Dim pptApp As PowerPoint.Application, pptPres As PowerPoint.Presentation
    Dim strFileToOpen As Variant, sh As Worksheet, ch As ChartObject

    strFileToOpen = Application.GetOpenFilename(FileFilter:="Powerpoint Files *.pptx (*.pptx),")
    If strFileToOpen = False Then Exit Sub
    Set pptApp = New PowerPoint.Application
    pptApp.Visible = True
    Set pptPres = pptApp.Presentations.Open(fileName:=strFileToOpen, ReadOnly:=msoFalse)

    For Each sh In ThisWorkbook.Sheets
        If IsNumeric(sh.name) Then
            For Each ch In sh.ChartObjects
                ch.Copy
                With pptPres.Slides(CLng(sh.name)).Shapes.Paste
                    .Top = ch.Top
                    .Left = ch.Left
                    .Width = ch.Width
                    .Height = ch.Height
                End With
            Next
        End If
    Next
End Sub

Copying all charts from an Excel Sheet to a Powerpoint slide

1 Answers