Can't move charts after moving charts from excel to powerpoint!! D:

Question

I'm trying to move graphs that I've copied from excel into powerpoint using VBA. Code is below. Not sure why it's not working. How would you guys approach it? I've tried many different methods including inserting a ".select" after the ".paste" but it's giving me errors. Really not sure... =\ Any help is appreciated.

`

Sub Automating_PowerPoint_from_Excel_1()
'Automate using Early Binding: Add a reference to the PowerPoint Object Library in Excel (your host application) by clicking Tools-References in VBE, which will enable using PowerPoint's predefined constants. Once this reference is added, a new instance of PowerPoint application can be created by using the New keyword.

'Create a new PowerPoint ppt of 3 slides with sound effect, and run a slide show.
 
'variables declared as a specific object type ie. specific to the application which is being automated:
Dim applPP As PowerPoint.Application
Dim prsntPP As PowerPoint.Presentation
Dim slidePP As PowerPoint.Slide
Dim shapePP As PowerPoint.Shape
Dim lSlideCount As Long
Dim strPpPath As String, strPpName As String
Dim oSh As Shape

'Create a new instance of the PowerPoint application. Set the Application object as follows:
Set applPP = CreateObject("Powerpoint.Application")

'make the PowerPoint window visible:
applPP.Visible = True
'maximize PowerPoint window:
applPP.WindowState = ppWindowMaximized
applPP.Presentations.Open "C:\Users\....\Template A Powerpoint.pptx"

Set prsntPP = applPP.ActivePresentation

'-------------------------


ActiveWorkbook.Sheets("...").ChartObjects(4).Activate
    ActiveChart.ChartArea.Copy
prsntPP.Slides(3).Shapes.Paste

`

Answer 1

I've included two procedures here - one will create an instance of powerpoint, the other will copy charts, ranges and add some text to a text box.

NB: I haven't fully tested, just ripped it out of a project I was working on.

Public Sub UpdatePowerPoint()

    Dim oPPT As Object
    Dim oPresentation As Object
    Dim oSlide As Object
    Dim cht As Chart
    Dim lTop As Long

    On Error GoTo ERROR_HANDLER

    Set oPPT = CreatePPT

    ''''''''''''''''''''''''''''''''
    'Update path to your template. '
    ''''''''''''''''''''''''''''''''
    Set oPresentation = oPPT.Presentations.Open( _
        "S:\Bartrup-CookD\PowerPoint Template.pptx")
    oPPT.ActiveWindow.ViewType = 1 ' 1 = ppViewSlide

    '''''''''''''''''''''''''''''''''''''''''''
    'Add some text to a placehold on slide 1. '
    '''''''''''''''''''''''''''''''''''''''''''
    oPresentation.Windows(1).View.GoToSlide 1
    With oPresentation.Slides(1)
        .Shapes.PlaceHolders(1).Select msoTrue
        .Shapes.PlaceHolders(1).TextFrame.TextRange.Text = _
            "Add the date to this text box " & vbCr & _
            Format$(Date, "mmmm yyyy")
    End With

    ''''''''''''''''''''''''''''''''''''
    'Add a chart and range to slide 2. '
    ''''''''''''''''''''''''''''''''''''
    oPresentation.Windows(1).View.GoToSlide 2
    With oPresentation.Slides(2)

        '''''''''''''''''''''''''''
        'Copy and paste the chart '
        '''''''''''''''''''''''''''
        .Select
        Set cht = ThisWorkbook.Worksheets("Sheet1").ChartObjects("MyChart").Chart
        cht.CopyPicture Appearance:=xlScreen, Format:=xlPicture, Size:=xlScreen
        .Shapes.Paste.Select
        oPresentation.Windows(1).Selection.ShapeRange.Left = 40
        oPresentation.Windows(1).Selection.ShapeRange.Top = 90

        '''''''''''''''''''''''''''''''''
        'Copy and paste the data range. '
        '''''''''''''''''''''''''''''''''
        ThisWorkbook.Worksheets("Sheet1").Range("A2:F5").CopyPicture Appearance:=xlScreen, Format:=xlPicture
        .Shapes.Paste.Select
        oPresentation.Windows(1).Selection.ShapeRange.Left = 40
        oPresentation.Windows(1).Selection.ShapeRange.Top = 90

    End With

End Sub

Public Function CreatePPT(Optional bVisible As Boolean = True) As Object

    Dim oTmpPPT As Object

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Defer error trapping in case PowerPoint is not running. '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    Set oTmpPPT = GetObject(, "PowerPoint.Application")

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'If an error occurs then create an instance of PowerPoint. '
    'Reinstate error handling.                                 '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo ERROR_HANDLER
        Set oTmpPPT = CreateObject("PowerPoint.Application")
    End If

    oTmpPPT.Visible = bVisible
    Set CreatePPT = oTmpPPT

    On Error GoTo 0
    Exit Function

ERROR_HANDLER:
    Select Case Err.Number

        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure CreatePPT."
            Err.Clear
    End Select

End Function

If you're putting more than one chart on a sheet you may want to have the Top and Left values as variables.

If lTop is a long representing the top position - place this to calculate what the next top value should be in relation to the current selection.

lTop = lTop + oPresentation.Windows(1).Selection.ShapeRange.Height + 20

NB: This copy and pastes a picture of your range/chart and not an actual range/chart object.