Excel Charts and Tables to PowerPoint

[afziscool]

I have created a spreadsheet that uses functions to create a few tables and charts and will update them whenever I update the data. I now would like to have these charts and tables that are always the same (other than for different numbers in the cells) to be arranged into various slides of a powerpoint presentation in same locations and with the same size every time.

Can anyone give me any advice in how to have this process of putting the tables and charts into powerpoint be automated?

Thanks,
-Arthur

 

[Derek Brown]

If you have not already done so, you will find that there are very few sources of PowerPoint VBA compared to Excel, Access and Word. One very good source however, is Office VBA Macros You can Use Today (you will find this at the MrExcel.com store).
I created a simple VBA module as a prototype for a larger project, so here is a copy of it - you may find it helpful.

Public Sub CopyChartsToPowerPoint()
'
Dim ws As Worksheet
Dim objChartObject As ChartObject
Dim objChart As Chart
Dim objCht As Chart
Dim lngKount As Long
'
Dim pptApp As Object 'PowerPoint.Application
Dim pptPres As Object 'PowerPoint.Presentation
Dim pptSld As Object 'PowerPoint.Slide
'
On Error GoTo Error_Para
'
    'Create a new Powerpoint session
    Set pptApp = CreateObject("PowerPoint.Application")
    'Create a new presentation
    Set pptPres = pptApp.Presentations.Add
'
    lngKount = 0
    For Each ws In ActiveWorkbook.Worksheets
      'Verify if there is a chart object to transfer
      If ws.ChartObjects.Count > 0 Then
        For Each objChartObject In ws.ChartObjects
          Set objChart = objChartObject.Chart
          Set pptSld = pptPres.Slides.Add(pptPres.Slides.Count + 1, 12)
          With objChart
           'Copy chart object as picture
            objChart.CopyPicture xlScreen, xlBitmap, xlScreen
            'Paste copied chart picture into new slide
            pptSld.Shapes.Paste
          End With
          lngKount = lngKount + 1
        Next objChartObject
      End If
    Next ws
    ' Now check CHART sheets:
    For Each objCht In ActiveWorkbook.Charts
        Set pptSld = pptPres.Slides.Add(pptPres.Slides.Count + 1, 12)
        With objCht
            'Copy chart object as picture
             .CopyPicture xlScreen, xlBitmap, xlScreen
             'Paste copied chart picture into new slide
             pptSld.Shapes.Paste
        End With
        lngKount = lngKount + 1
    Next objCht
    '
    If lngKount > 0 Then
        If lngKount = 1 Then
            MsgBox "1 chart was copied to PowerPoint", vbOKOnly + vbInformation, "Information"
        Else
            MsgBox lngKount & " charts were copied to PowerPoint", vbOKOnly + vbInformation, "Information"
        End If
    End If
    'Activate PowerPoint application
    pptApp.Visible = True
    pptApp.Activate
    GoTo Exit_Para
Error_Para:
    MsgBox "Error " & Err.Number & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "copying charts to PowerPoint", vbOKOnly + vbCritical, "Error"
Exit_Para:
    On Error Resume Next
    Set ws = Nothing
    Set objChart = Nothing
    Set objChartObject = Nothing
    Set pptSld = Nothing
    Set pptPres = Nothing
    Set pptApp = Nothing
End Sub

You will see that there is a different approach for Chart Sheets and Embedded Charts. The code opens PowerPoint but does not start the slideshow.

 

[afziscool]

Thats great. Thank you very much for your help. Do you know if it is possible to paste the charts in specific locations of the slide (like bottom right, 2/3 of the way up and on the left side) and to have the chart be a certain size?

 

[Derek Brown]

It is possible to do as you want but I do not have any code examples as I have not needed to do that.
The charts I believe need to be treated as a 'Shape' object and you can use the Shape.Height, Shape.Width, Shape.Top and Shape.Left etc properties.
For my main project, I choose to do things more simply - I created an Excel page, formatted exactly as I wanted, with an embedded chart and copied the Excel page to PowerPoint instead of the chart.

Worksheets("Month Charts").Range("A1:O33").CopyPicture xlScreen, xlBitmap
Set pptSld = pptPres.Slides.Add(pptPres.Slides.Count + 1, ppLayoutBlank)
pptSld.Shapes.Paste (1)

Hope this helps.

 

[Datsmart]

Lots of good tips here:
http://peltiertech.com/Excel/XL_PPT.html#VBAcontents