CODEWITHSUNDEEP
vbaexcelpowerpoint
Oskar_U
Oskar_U

Reputation: 482

Excel VBA - bulk chart export to ppt

I've very similar problem as described previously, but in my case I'd like to export all charts at once as chartobjects into specific placeholder of my ppt template slides. For total export I use the following solution scraped from the web. How to paste those charts directly? Somehow abovementioned solution does not work fine for me. I'd be grateful for any advice

Option Explicit

Sub TotalExport_chart_to_ppt()

' Set a VBE reference to Microsoft PowerPoint Object Library

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim SlideCount As Long
Dim ws As Worksheet
Dim oChrtObj As ChartObject
Dim nPlcHolder As Long

' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")

' Reference active presentation
Set PPPres = PPApp.ActivePresentation

PPApp.ActiveWindow.ViewType = ppViewSlide

' loop through each worksheet within the active workbook
For Each ws In Worksheets
    ' loop through each chart object on current worksheet
    For Each oChrtObj In ws.ChartObjects
        ' copy chart as chartobject
        oChrtObj.Copy
        ' Add a new slide and paste in the chart
        SlideCount = PPPres.Slides.Count
        Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutChart)
        With PPSlide.Shapes.Paste
            .Align msoAlignCenters, True
            .Align msoAlignMiddles, True
        End With
    Next oChrtObj
Next ws

' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
Set ws = Nothing
Set oChrtObj = Nothing

End Sub

Upvotes: 0

Views: 183

Answers (1)

mooseman
mooseman

Reputation: 2017

It may help to pastespecial and then set formats

Set PPShape = PPSlide.Shapes.PasteSpecial(DataType:=ppPasteEnhancedMetafile)

There are other types of pastespecials, so you will have to decide what you want the chart to be.

Upvotes: 0

Related Questions