Error 438 when trying to set embedded chart source

[whitehawk81]

I'm trying to create a macro, which loops through the selected worksheets, opens a powerpoint template for each, replaces some text and also changes the data source of the embedded charts with the pivot table range of that specific chart on the selected worksheet. My problem was initially, that there are several charts on each worksheet, but each sheet has the same set of charts (e.g. performance, top 10 searches). So I tried to set the embedded chart source by validating the pivot table name. But now I get run-time error 438 - object doesn't support this property or method by defining the cht.Chart.SetSourceData Source. But if I change the source to Sheets(solutionName).Range("a1") the code runs without error, but the chart doesn't get updated with the new source. Obviously I defined something wrong, I just cannot figure out, what I'm missing.

Sub createPres(solutionName As String, ws As Worksheet)
Dim templatePPT As String
Dim PowerPointApp As PowerPoint.Application
Dim myPresentation As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim cht As Excel.ChartObject
Dim pt As PivotTable
Dim ptName As String
Dim saveAsName As String


Set cht = ws.ChartObjects(1)
ptName = solutionName & "-" & cht.Chart.ChartTitle.Text
Set pt = ws.PivotTables(ptName)


templatePPT = Environ("USERPROFILE") & "\AppData\Roaming\Microsoft\Templates\LDAP Kundenbericht.potx"
saveAsName = Environ("USERPROFILE") & "\My Documents\" & solutionName & " LDAP Kundenbericht-" & Sheet1.Range("B26").Value & ".pptx"


    'Look for existing instance
On Error Resume Next
    Set PowerPointApp = GetObject(, "PowerPoint.Application")
On Error GoTo 0


    'Let's create a new PowerPoint
If PowerPointApp Is Nothing Then
    Set PowerPointApp = New PowerPoint.Application
End If


Set myPresentation = PowerPointApp.Presentations.Open(templatePPT)


If myPresentation Is Nothing Then
    MsgBox "Template konnte nicht geöffnet werden.", vbCritical, "Error"
    Exit Sub
End If
    
ReplaceText "mndt", solutionName
ReplaceText "tt.mm.jjjj", Sheet1.Range("Date").Value
ReplaceText "monat jahr", Sheet1.Range("fullMJ").Value


    For Each sld In myPresentation.Slides


        For Each shp In sld.Shapes


            If shp.HasChart Then


                If pt.Name = ptName Then


[COLOR=#ff0000]                cht.Chart.SetSourceData Source:=Sheets(solutionName).pt.TableRange1[/COLOR]


                End If


            End If


        Next shp


    Next sld


MsgBox "Macro works"
'myPresentation.SaveAs saveAsName


End Sub

 

[whitehawk81]

Now I separated the code for changing the embedded chart source and changed it a bit. The code runs without error, but it still doesn't changes the chart source.

Sub changeSource(ws As Worksheet)
Dim pp As Object
Dim pptPres As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim pt As PivotTable
Dim ptName As String
Dim cht As Excel.ChartObject


Set pp = GetObject(, "PowerPoint.Application")
Set pptPres = pp.ActivePresentation
Set cht = ws.ChartObjects(1)
ptName = solutionName & "-" & cht.Chart.ChartTitle.Text
Set pt = ws.PivotTables(1)
    
For Each sld In pptPres.Slides


        For Each shp In sld.Shapes


            If shp.HasChart Then


                If pt.Name = ptName Then


                cht.Chart.SetSourceData Source:=pt.TableRange1


                End If


            End If


        Next shp


    Next sld


MsgBox "Macro works"


End Sub

 

[whitehawk81]

Actually the original error got resolved, when I changed the source range to:

[COLOR=#333333]cht.Chart.SetSourceData Source:=pt.TableRange1[/COLOR]

So I will post a new thread for the other issue.