Hi All,

I have PPT that contain charts that linked data to other excel. I try to create a vba that can go through all the slides and chart, and change the data range. I use the series formula, but whenever I try to assign value into series formula, it always inform: "Method Formula to Series Failed"

Here is my code, could you advise on how to assign the formula to series? Many thanks in advance

Sub ChangeSeriesData()

    Dim oSlide As Slide
    Dim oShape As Shape
    Dim oChart As Chart
    Dim oChartData As ChartData
    Dim oWorkbook As Object ' Represents the embedded or linked Excel workbook
    Dim oWorksheet As Object ' Represents the specific worksheet within the workbook
    Dim sDataRange As String
    Dim sSheetName As String
 
    
Dim oldPath As String


Dim newPath As String


Dim strLink As String




    ' Set the slide and shape containing the linked chart
oldPath = "Data1"
newPath = "Data2"


For Each oSlide In ActivePresentation.Slides


For Each oShape In oSlide.Shapes
    ' Check if the shape is a chart and if it's linked
    If oShape.HasChart Then
        Set oChart = oShape.Chart
        If oChart.ChartData.IsLinked Then
            Set oChartData = oChart.ChartData
            oChartData.Activate ' Activates the linked Excel workbook


            Set oWorkbook = oChartData.Workbook
            ' Get the sheet name and data range from the first series in the chart
            ' This assumes all series in the chart use the same sheet and range,
            ' which is typical for linked charts.
            For i = 1 To oChart.SeriesCollection.Count
                Set oSeries = oChart.SeriesCollection(i)
             
                Dim sFormula As String
                sFormula = oSeries.Formula
            
                  'MsgBox "You entered : " & sFormula, vbInformation, "User Input"
                sFormula = Replace(sFormula, oldPath, newPath)
                'sFormula = Replace(sFormula, oldPath, newPath)
                  'MsgBox "You entered : " & sFormula, vbInformation, "User Input"
                 oSeries.Formula = sFormula ' <-- HERE IS WHERE I FACED ISSUE
                'oSeries.Formula = "=SERIES(Data1!$B$1,Data1!$A$2:$A$5,Data1!$B$2:$B$5,1)"
                  oWorkbook.Close ' Close the activated Excel workbook


        Next i
        
        End If
    End If
    
    Next oShape


Next oSlide


End Sub