Macro that copies charts from a worksheet in excel to different slides in PowerPoint (MAC 2011)

I have a number of charts on a worksheet, i want to create a macro that copies each of the charts into a separate slide in a already existing powerpoint. If this macro is able to open the presentation and copy over these charts, that would be ideal. I am currently working with a MAC on Excel for Mac 2011.
AckeemKAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
Try this AckeemK:

Option Explicit

' ==============================================
' Macro to exprt charts from a worksheet to PowerPoint (runs from Excel)
' Dependencies : none
' Tested : PowerPoint 2011 (Mac) and 2013 (PC)
' Source : http://youpresent.biz/
' Author : Jamie Garroch
' Date : 13 May 2015
' ==============================================
Sub ExportChartsToPowerPoint()
    ' Declarations for Excel objects
    Dim oWB As Workbook
    Dim oWS As Worksheet
    Dim oCht As ChartObject
    ' Declarations for PowerPoint late-binding objects
    Dim oPPT As Object
    Dim oPres As Object
    Dim oSld As Object
    Dim oShp As Object
    ' Module variables
    Dim counter As Integer
    
    Set oWB = ActiveWorkbook
    ' The next line assumes we are just using sheet 1
    Set oWS = oWB.Worksheets(1)
    
    ' Create instance of PowerPoint application
    Set oPPT = CreateObject("PowerPoint.Application")
    Set oPres = oPPT.Presentations.Add(msoTrue)
    
    ' Loop through each chart in the sheet, copying and pasting it to a new slide in PowerPoint
    For counter = 1 To oWS.ChartObjects.Count
        ' Add a new slide at the end of the deck using the second layout (usually title only) - could loop through layouts to find the right one, independent of design
        Set oSld = oPres.Slides.AddSlide(oPres.Slides.Count + 1, oPres.SlideMaster.CustomLayouts(6))
        oWS.ChartObjects(counter).Copy
        ' You can use the .PasteSpecial method to paste using one of the picture formats instead of the chart object itself
        Set oShp = oSld.Shapes.Paste
        ' You can move/resize the oShp object now, centred and set to 80% of the slide area in this example but could also be set in a body placeholder:
        oShp.Width = oPres.PageSetup.SlideWidth * 0.8
        oShp.Height = oPres.PageSetup.SlideHeight * 0.8
        oShp.Left = (oPres.PageSetup.SlideWidth - oShp.Width) / 2
        oShp.Top = (oPres.PageSetup.SlideHeight - oShp.Height) / 2
    Next
    
    ' Clear Excel Objects
    Set oWB = Nothing: Set oWS = Nothing: Set oCht = Nothing
    ' Clear PowerPoint objects
    Set oPPT = Nothing: Set oSld = Nothing: Set oShp = Nothing
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AckeemKAuthor Commented:
Hi Jamie,

To be more specific, I am looking to update the charts in the existing powerpoint presentation which I attached. The charts within this presentation are the charts within the 'Charts of SR' tab in the Excel file. The user will adjust the charts based on the data and pivot tables and would click "Create Presentation" to open this presentation and have the charts in the PowerPoint match the ones in their carelog. The charts in the PowerPoint start from slide 14 through slide 23. I've tried copying and pasting special of the charts as "Microsoft Excel Chart Object" but not sure how to write the code for linking these. Essentially it should go "Open SRs by Month", and then "Closed SRs by Month" and so on within the PowerPoint basically following how it is shown as you scroll down the 'Charts of SRs' tab within the excel file.
DSE-Carelog-Report-V1.xlsm
DSE-Carelog-Slidedeck-Quarterly-Business
Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
That's a completely different question AckeemK. Could you accept the solution above to the original question which was "Macro that copies charts from a worksheet in excel to different slides in PowerPoint (MAC 2011)" and open a new question regarding linking and updating charts?
AckeemKAuthor Commented:
I apologize for that but I've asked this question multiple times and have not received no answer. It is currently open still and can be found here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28664971.html
Jamie GarrochSenior Technical Consultant at BrightCarbonCommented:
OK - thanks - I'll take a look tomorrow as it's a bit late in the UK now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.