Problems using VBA to create a PPT from an excel

Question

I´m fairly new to VBA and I´m trying to make a powepoint presentation from a workbook. I have a template, and the idea is to fill it with graphs and charts.

This is my code:

Sub ChartToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

' 6 - Convocatoria - Presentismo
Set PPSlide = PPPres.Slides(6)
ThisWorkbook.Worksheets("FyV").ChartObjects(15).Select
'Hoja8.ChartObjects(15).Select
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,        Format:=xlPicture
PPSlide.Shapes.Paste
PPApp.ActiveWindow.Selection.ShapeRange.Left = 10
PPApp.ActiveWindow.Selection.ShapeRange.Top = 20
'PPSlide.ShapeRange.Width = 80
'PPSlide.ShapeRange.Height = 80

End Sub

I have one block per graph, sometimes more than 1 graph per slide. But I´m having several problems.

1. When I ask for

    ThisWorkbook.Worksheets("FyV").ChartObjects(15).Select
   

I get chart 24 from that worksheet. When I ask for charts 3, 12 and 13 I get chart 5.

2. When I uncomment

   'PPSlide.ShapeRange.Width = 80
   'PPSlide.ShapeRange.Height = 80
   

I get the following error:

Compile error: Method or data member not found

3. Sometimes the line:

   ThisWorkbook.Worksheets("FyV").ChartObjects(XX).Select
   

Gets the following error:

Run-time error '1004': Application-defined or object-defined error

But XX exists, and it is on "FyV"

I have tried

 ThisWorkbook.Worksheets("FyV").ChartObjects(15).Select

And

'Hoja8.ChartObjects(15).Select

To solve 1 and 3, but it changed nothing.

Thanks in advance, Bauti.

Answer 1

I´ve found a solution (guided by the answers, Thanks!) It isn´t that elegant, but it works.

  Sub ChartToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide

Worksheets("FyV").Select

' 6 - Convocatoria - Presentismo
Set PPSlide = PPPres.Slides(6)
ThisWorkbook.Worksheets("FyV").ChartObjects("Chart 15").Select
'Hoja8.ChartObjects(15).Select
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
PPSlide.Shapes.Paste
PPApp.ActiveWindow.Selection.ShapeRange.Left = 40
PPApp.ActiveWindow.Selection.ShapeRange.Top = 200
PPApp.ActiveWindow.Selection.ShapeRange.Width = 160
PPApp.ActiveWindow.Selection.ShapeRange.Height = 160

End Sub

Since there were few worksheet changes it wasn´t that hard to add the worksheet line every time there was a change.

Furthermore, asking in mr excel forum I got this answer, that seems to work:

Sub ChartToPresentation()
    ' 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 oShape As PowerPoint.Shape
    ' Reference existing instance of PowerPoint
    Set PPApp = GetObject(, "Powerpoint.Application")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' 6 - Convocatoria - Presentismo
    Set PPSlide = PPPres.Slides(6)
     ThisWorkbook.Worksheets("FyV").ChartObjects("Chart 1").CopyPicture Appearance:=xlScreen, Format:=xlPicture
     PPSlide.Shapes.Paste
     With PPSlide
        Set oShape = .Shapes(.Shapes.Count)
     End With
     'oShape.LockAspectRatio = msoFalse
     oShape.Left = 10
     oShape.Top = 20
     oShape.Width = 80
     oShape.Height = 80
End Sub

Thanks for the replies, Bauti.

Answer 2

When I uncomment

'PPSlide.ShapeRange.Width = 80 'PPSlide.ShapeRange.Height = 80 I get the following error:

Compile error: Method or data member not found

---

Yes, because you can't set the width and height for a shaperange.
If there's only one shape in the range, as would be the case for a chart pasted into PPT from Excel, you can use PPSlide.ShapeRange(1).Height etc.

If you need to set the size of more than one shape in a range, you'd have to iterate through the ShapeRange collection:

For x = 1 to PPSlide.ShapeRange.Count
   With PPSlide.ShapeRange(x)
       ' Do stuff here
   End With
Next

By the way, you generally want to avoid selecting anything, in either PPT or Excel. Get an object reference to the chart rather than selecting it. In fact, if the sheet the chart is on isn't currently in view, attempting to .Select it may be one reason for the errors you're seeing.

Answer 3

ChartObjects(15) represents the "fifteenth" chart on the sheet - 15 doesn't necessarily correspond to the chart's name, or to its position on the sheet, but is related to the order in which the charts were created.