I’m encountering an issue when trying to modify the series values of a time series chart in an Excel report through a VB.NET script task in SSIS.

Here’s what I’m doing:

1. I open the Excel report and select a specific worksheet that
   contains the chart object.
2. I loop through the available series in the chart and extract the full version of the series formula.
3. I use a regular expression to identify and modify specific ranges in the formula (for example, changing the data source
   ranges).
4. After making the necessary changes to the formula string, I attempt to assign the modified formula back to the series.

However, the problem occurs after I assign the modified formula. When I check the resulting Excel report, the series formula doesn’t retain its proper structure. Specifically, the series names are collapsed or the data ranges are incorrect.

Important Note: The data sources in the chart series are in the following format:

 "'C:\Path\To\Your\File[ExternalFile.xlsx]Sheet2'!$B$2:$B$20"

Here’s the relevant portion of the code I’m using:

      Dim ws1 As Object = oWorkbook_Report.Worksheets("Rates")

            Dim stmt As String = "SELECT ROW_INDEX " +
                                        "FROM BULK_ROW_IND " +
                                        "WHERE REPORT_NM = 'GGBs_Spread' AND SHEET_NM = 'Rates' AND REF_DATE = '" + Format(refDate, "yyyy-MM-dd").ToString + "' "


            sqlComm = New System.Data.SqlClient.SqlCommand(stmt, sqlConn)

            Dim index As Integer = sqlComm.ExecuteScalar()


            Dim chartObj As Object = ws1.ChartObjects("Chart 3").Activate
            Dim chart As Object = chartObj.Chart
            Dim pattern, newRowNumber, ModifiedFormula As String



            For Each series As Object In chart.SeriesCollection

                Dim seriesFormula As String = series.Formula


                MsgBox("Series Formula before modification: " & seriesFormula)


                pattern = "$w{1,2}$d+:$w{1,2}$(d+)"

                newRowNumber = index.ToString

                ModifiedFormula = Regex.Replace(seriesFormula, pattern, Function(m) ReplaceEndRange(m, newRowNumber))

                MsgBox("Series Formula after modification: " & ModifiedFormula)

                'Commit changes   

                series.Formula = ModifiedFormula



            Next

Where ReplaceEndRange is used to modify the ending part of the range :

 Function ReplaceEndRange(ByVal match As Match, ByVal newNumber As String) As String

        Dim original As String = match.Value

        Dim modified As String = Regex.Replace(original, "d+$", newNumber)
        Return modified
    End Function

After running this code, the series in the chart do not display correctly. The series names collapse or show incorrect values, and the data ranges are not retained as expected. I’m unsure if there’s an issue with how I’m modifying the formula or assigning it back to the series.

What I’ve tried:

• I’ve confirmed that the regular expression is correctly identifying and replacing the desired parts of the formula.
• I’ve tried printing the modified formula before assigning it back to ensure it looks correct.

Despite this, assigning the modified formula to the series seems to break the series in the chart.

Any possible solutions?