Search code examples
excelexcel-2010powerpoint-2010vba

Data behind chart in Powerpoint 2010 is not updating through VBA

I'm having trouble with a Powerpoint 2010 presentation containing an OLEFormat.Object of an Excel chart.

I update the chart using data from Excel and save it at various stages - the idea is that I end up with three presentations:

  • The original that has been renamed with the word "(Previous)" appended to the file name.
  • A new version of the original file containing the new data - this is the template for the following month.
  • A new file containing the new data - this is the report version that is emailed out.

The problem I'm having is that the charts don't seem to retain the updated data. The charts will show the new data, but as soon as I go to edit the chart it flips back and only shows the original data - there's no updated data in the worksheet.

The image below shows what I mean - they're both the same chart, but once I edit the chart the last series changes from December back to June.

enter image description here

To recreate the problem:

  • Create a new folder and place a new blank presentation in there.
  • Delete the Click to add title and click to add subtitle objects from the first slide.
  • On the Insert ribbon select Object and Insert Excel Chart from the Insert Object dialog box.
    The chart is called Object 3 (as you deleted the first two objects) and contains six months of random data.
  • Ensure the presentation is saved as Presentation 1.pptx.
  • In the same folder create a new Excel 2010 workbook.

Add the following VBA code to a module within the workbook and execute the Produce_Report procedure: 

Option Explicit

Public Sub Produce_Report()

    Dim sTemplate As String             'Path to PPTX Template.
    Dim oPPT As Object                  'Reference to PPT application.
    Dim oPresentation As Object         'Reference to opened presentation.

    sTemplate = ThisWorkbook.Path & "\Presentation1.pptx"

    'Open the Powerpoint template and save a copy so we can roll back.
    Set oPPT = CreatePPT
    Set oPresentation = oPPT.Presentations.Open(sTemplate)

    'Save a copy of the template - allows a rollback.
    oPresentation.SaveCopyAs _
        Left(oPresentation.FullName, InStrRev(oPresentation.FullName, ".") - 1) & " (Previous)"

    'Update the chart.
    Audit_Volumes oPresentation.slides(1)

    'Save the presentation using the current name.
    oPresentation.Save

    'Save the presentation giving it a new report name.
    oPresentation.SaveAs ThisWorkbook.Path & "\New Presentation"

End Sub

Private Sub Audit_Volumes(oSlide As Object)
    Dim wrkSht As Worksheet
    Dim wrkCht As Chart
    With oSlide
        With .Shapes("Object 3")
            Set wrkSht = .OLEFormat.Object.Worksheets(1)
            Set wrkCht = .OLEFormat.Object.Charts(1)
        End With
        With wrkSht
            .Range("A3:D7").Copy Destination:=.Range("A2")
            .Range("A7:D7") = Array("December", 3, 4, 5)
        End With

        RefreshThumbnail .Parent

    End With
    Set wrkSht = Nothing
    Set wrkCht = Nothing
End Sub

Public Sub RefreshThumbnail(PPT As Object)
    With PPT
        .designs(1).slidemaster.Shapes(1).Left = .designs(1).slidemaster.Shapes(1).Left + 1
        .designs(1).slidemaster.Shapes(1).Left = .designs(1).slidemaster.Shapes(1).Left - 1
    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
        Set oTmpPPT = CreateObject("Powerpoint.Application")
    End If
    oTmpPPT.Visible = bVisible
    Set CreatePPT = oTmpPPT
    On Error GoTo 0
End Function

Surely the two versions of the presentation saved after the chart has been updated should show the data for the updated chart?

Solution

  • When updating charts in Powerpoint I've previously seen examples of changing the Powerpoint view to slidesorter, performing an action on the shape (DoVerb) and then switching the view back again.
    I've often had problems with the code throwing errors, probably because I generally update Powerpoint from either Excel or Access.

    I've had a play around and got it to work.
    An embedded chart object has two verbs available as far as I can tell - Edit and Open.
    So in my code where I have RefreshThumbnail .Parent, I have updated the code to RefreshChart .Parent, .slidenumber, .Shapes("Object 3").

    The new procedure is: 

    Public Sub RefreshChart(oPPT As Object, SlideNum As Long, sh As Object)
    oPPT.Windows(1).viewtype = 7 'ppViewSlideSorter
    oPPT.Windows(1).View.gotoslide SlideNum
    oPPT.Windows(1).viewtype = 9 'ppViewNormal
    sh.OLEFormat.DoVerb (1)
End Sub

    (previously I was using oPPT.ActiveWindow which I think was causing the problem).

    Now I'm just having problems with one chart resizing itself and the calculations behind another not recalculating - different problems for different questions I think.