Change sheet name of the source data in a diagram using Excel VBA

Question

I have a diagram on a worksheet. The source data is on another sheet.

I would like to redirect the source data reference in the diagram to a worksheet identical to the first worksheet except for the data.

How can I replace the sheetname of the source data reference in the diagram with the name of the other worksheet?

https://learn.microsoft.com/en-us/office/vba/api/excel.chart.setsourcedata — Marcucciboy2, Sep 04 '18 at 18:34

score 0 · Answer 1 · answered Sep 07 '18 at 19:08

Sorry for the late response. I solved it like this with the help from you guys and some googleing. Not the cleanest code but it works. Thanks!!

Sub ReplaceSheetName(strNewSheetName As String)
    Dim srs As Series
    Dim strSourceDataSheetName As String
    Dim strTempFormula As String

    strNewSheetName = "'" & strNewSheetName & "'"
    For Each srs In ActiveSheet.ChartObjects(1).Chart.SeriesCollection
        If InStr(1, srs.Name, "Budget", vbTextCompare) > 0 Then
        Else
            strSourceDataSheetName = GetSheetNameFromChartSourceData(srs.Formula, "(", "!")
            strTempFormula = Replace(srs.Formula, strSourceDataSheetName, strNewSheetName, vbTextCompare)
            srs.Formula = WorksheetFunction.Substitute(srs.Formula, srs.Formula, strTempFormula)
        End If
    Next
    ActiveSheet.ChartObjects(1).Chart.Refresh
End Sub

Private Function GetSheetNameFromChartSourceData(strSourceDataFormula As String, strStartDelimiter As String, strEndDelimiter As String) As String
    Dim firstDelPos As Integer
    Dim secondDelPos As Integer
    Dim stringBwDels As String

    On Error GoTo ErrorHandler

    firstDelPos = InStr(strSourceDataFormula, strStartDelimiter) 'Position of start delimiter

    'If end delimiter is the same as the start delimiter they will be the same, hence no string is extracted.
    If strStartDelimiter = strEndDelimiter Then
        secondDelPos = InStrRev(strSourceDataFormula, strEndDelimiter) 'Position of end delimiter
    Else
        secondDelPos = InStr(strSourceDataFormula, strEndDelimiter) 'Position of end delimiter
    End If

    If firstDelPos = 0 Or secondDelPos = 0 Then
        stringBwDels = strSourceDataFormula
    Else
        stringBwDels = Mid(strSourceDataFormula, firstDelPos + 1, secondDelPos - firstDelPos - 1) 'Extract the string between two delimiters
    End If

    GetSheetNameFromChartSourceData = stringBwDels
    Exit Function
ErrorHandler:
    MsgBox "An error occurred while extracting substring between the given separators " & """" & strStartDelimiter & """" & " and " & """" & strEndDelimiter & """" & ". Error message: " & Err.Description, vbOKOnly, "Error"
    Resume Next
End Function

score 0 · Answer 2 · answered Nov 01 '19 at 16:47

This is a nice job. I also had the same problem but I solved it in a shorter way, leveraging on ready-made functions to achieve the same thing. Below is my code

Sub changeSheetRef()
'This code replaces the sheet name part of all data series in a plot _
 with the current sheet name

newSheetRef = ActiveSheet.Name

For i = 1 To ActiveSheet.ChartObjects.Count
    ActiveSheet.ChartObjects(i).Activate
        For j = 1 To ActiveChart.SeriesCollection().Count
            oldDataSource = ActiveChart.SeriesCollection(j).Formula
            oldSheetRef = Split(Split(oldDataSource, ",")(2), "'")(1)
            newDataSource = Replace(oldDataSource, oldSheetRef, newSheetRef)
            ActiveChart.SeriesCollection(j).Formula = newDataSource
        Next
    ActiveSheet.ChartObjects(i).Chart.Refresh
Next
MsgBox "All chart data series have been updated to match equivalent data on the current worksheet, courtesy of SaeedSoft Inc.", , "SaeedSoft Inc."
End Sub

Change sheet name of the source data in a diagram using Excel VBA

2 Answers2