[VBA] Retrieve chart series source data in a range

[Christian_Ku]

Hello,

Currently I'm working in an excel file that contains a lot of charts. I want to write a macro that changes the source data of every serie in every chart. It only needs to add extra rows. But as every series has different columns where they get the data from, how can I change the rows without changing the columns?

This is what I've written so far:

Sub ammend_datarange_series_all_charts()

Dim start_cell As String, end_cell As String
Dim chart_tot As Integer, c As Integer, s As Integer

chart_tot = Charts.Count
start_cell = InputBox("Start Cell", "Only give Row number")
end_cell = InputBox("End Cell", " Only give Row number")



For c = 1 To chart_tot
    series_tot = Charts(c).SeriesCollection.Count
    For s = 1 To series_tot
        x_values = Charts(c).SeriesCollection(s).XValues
        y_values = Charts(c).SeriesCollection(s).Values
        'Charts(c).SeriesCollection(s).XValues = "='Calc'!$C$" & start_cell & ":$C$" & end_cell
        'Charts(c).SeriesCollection(s).Values = "='Calc'!$E$" & start_cell & ":$E$" & end_cell
    Next s
Next c

End Sub

As you can see I've tried two things, first I tried to change the string of the source data, however this meant that every series would have C and E as their source data. So I wanted to retrieve the x and y values in string format. This does not work yet, it returns an array with the values of all the entries in the series source data. From that array I can not retrieve the column where the data came from.

Help much appreciated

 

[Andrew Poulsom]

This should help you:

http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/

 

[Christian_Ku]

Hmmm, does this mean that I can not retrieve this info with Microsoft provided VBA code?

 

[Andrew Poulsom]

The link I posted only uses VBA code (to parse the SERIES formula). You can copy the Class module to your own project.

 

[adim1973]

Old thread, I know, but that class is pure gold. Thank you!

 

[CThomp2009]

Old thread, I know, but I'm hoping someone will look in... This code was simple in Excel 2003, I'm modifying to work in 2013, and am frustrated. I have an Excel file consisting of approx 20 100% stacked bar charts. I want to loop through and array the source data ranges of all the charts (which is then compared to ensure no two charts are pulling from the same range). The code should be pretty single, but returns an error noted below:

dim cht as chartobject
dim ChtData() as variant

redim ChtData(1 to activesheet.chartobjects.count, 1 to 2)

currentchart = 1

for each cht in activesheet.chartobjects
ChtData(currentchart,1) = cht.Name
ChtData(currentchart,2) = cht.seriescollection(1).formulaR1C1 <--- this throws an error
currentchart = currentchart + 1
next cht

If I manually click on the chat to the point where the blue border appears around the source data range and run "?cht.seriescollection(1).formulaR1C1" in the Immediate window - I get the formula as a string. However, when I run it as part of the code shown above, it doesn't. I don't need to parse the formula into pieces, I just need the whole formula string.

Seems this should be an easy thing to do/retrieve. What am I missing? Thanks very much.