Thread: Edit Series text in Charts

Hi, I'm trying to edit all data fields in a Chart page to remove al references to linked workbooks, I've done it successfully for worksheets but the Charts is throwing an error (doesn't support this object) at the line with

Code:

For Each sc In ActiveChart

When I created a macro it returned this

Code:

    ActiveChart.SeriesCollection(1).XValues = "='1m (A-Z)'!R5C2:R21C2"

so I used that as my guide for coding.

My code for the full procedure is below, if anyone can offer some insight I'd very much appreciate it.

Code:

'   Check through each WORKSHEET and activate in turn for processing correction
    For Each WSheet In ActiveWorkbook.Worksheets
            WSheet.Activate
'           Go to sub-routine
            WipeSheet
'   Having processed sheet, look for the next until none are left
    Next WSheet

'   Check through each CHART PAGE and activate in turn for processing correction
    For Each ChartPage In ActiveWorkbook.Charts
            ChartPage.Activate
            WipeChart
    Next ChartPage
End Sub

Sub WipeSheet()
Dim c As Range

'   Check each cell in the range in turn
    For Each c In Range("A1:K" & LastCell(ActiveSheet).Row)
'       Go to function (ParsedTxt) and get amended formula
        c.Formula = ParsedTxt(c.Formula)
'   Go to next cell in series until all cells have been checked
    Next c
    
End Sub

Sub WipeChart()
Dim sc As SeriesCollection

    For Each sc In ActiveChart
'       Go to function (ParsedTxt) and get amended formula
        sc.XValues = ParsedTxt(sc.XValues)
        sc.Values = ParsedTxt(sc.Values)
        sc.Name = ParsedTxt(sc.Name)
    Next sc

'************************************
'    ActiveChart.SeriesCollection(1).XValues = "='1m [some other linked excel workbook](A-Z)'!R5C2:R21C2"
'*************************************
End Sub


Function ParsedTxt(txt As String) As String
Dim LB As Integer
Dim RB As Integer
Dim I As Integer

'   Start off by setting LB & RB to -1 so errors are more clear
    LB = -1
    RB = -1
    
'   Start at 1 and check through to the last character in designated string (Len(txt))
    For I = 1 To Len(txt)
'       When 1 character matches search criteria make LB or RB as appropriate = to I
        If Mid(txt, I, 1) = "[" Then LB = I
        If Mid(txt, I, 1) = "]" Then RB = I
'   Go to next letter in string (until all in string have been checked or bracket is found)
    Next I
    
'   If LB & RB are both >0 then each must have found a square bracket
'   Pass back the beginning and end of string concatenated (dumping middle text section including square brackets)
    If LB > 0 And RB > 0 Then
        ParsedTxt = Mid(txt, 1, (LB - 1)) & Mid(txt, (RB + 1), Len(txt))
    Else
'       Whenever a pair of square brackets aren't found just pass back original string
        ParsedTxt = txt
    End If
    
End Function