Macro change Categories data range

Is there any way to modify the categories data range of a chart using a macro?

In the object of the chart I can see the category data sequence in oChartDoc.DataSequences, but not in DataSeries.DataSequences, and I was not able to find a way to change oChartDoc.DataSequences.

I can change the chart.Ranges, StartRow and EndRow properties, but this then resets the selection of data ranges used by each series of the chart to a default order of the data for the chart.

I can also change oChartDoc.DataSourceLabelsInFirstColumn to false, this changes the categories to a data series, then I replace the data sequence of each series and set back to true. This also resets the selection of data ranges used by each series.

Trying to create a macro that will update all the charts in a spreadsheet document, after additional data has been added after scraping data from a website. I want the user to have freedom to create charts which will then be updated by the macro after new data is added. I want to avoid creating macro to recreate every chart type the user might want.

Attached example which includes my previous macro attempt. DataExtract.ods

edit retag close merge delete

Sort by » oldest newest most voted

Suggested answer from Ratslinger led me to a workaround that seems to be working as desired.

First make a copy of all data series of the chart and extend the data range in each data sequence of each series.

If the chart has a data category range, indicated by DataSourceLabelsInFirstColumn, then take Ranges(0) the category range, modify the StartRow and EndRow and put only the extended category range back into chart range.

Note the category range start row is modified if DataSourceLabelsInFirstRow is set.

Finally save the modified data series with all updated data sequences back to the chart.

Attached modified example with updated macro. DataExtract.ods

more

Resposting Ratslinger's solution because it really worked for me! (new user, so cannot upvote) Sub SetRanges ... oSheet=ThisComponent.CurrentController.ActiveSheet ... End Sub

more

I'll take a chance here. I believe you are trying to adjust the data range when new info is loaded - i.e. $A$3:$A10 changes to$A3:$A15 and$B$3:$B10 changes to $B3:$B15 and whatever other range for a chart. If so you need to enter this with an array:

Sub SetRanges
oSheet=ThisComponent.CurrentController.ActiveSheet
oChart = oSheet.getCharts().getByIndex(0)
oData = Array(oSheet.getCellRangeByName( "A1:A5" ).getRangeAddress(), _
oChart.setRanges(oData)
End Sub


Using first set as label and next three as data.

Found a clue to this in Pitonyak's OOME guide (Chapter 15 in my copy - Charts). First time using macros on charts found when adding new range need to set color. Haven't gone any further.

more

Thanks. Yes I'm trying to change the chart data ranges after adding more rows. I can change the chart series ranges but the methods I've tried so far to change the category sequence all set the wrong order of ranges. Example second sheet, column E sets categories, even though it was originally data and column J was categories. Suspect your suggestion will also do this but I'll test and post back with result.

( 2016-08-15 03:06:19 +0100 )edit

Yes, I see what you are talking about now. Will continue to look.

( 2016-08-15 03:35:35 +0100 )edit

I tried your suggestion, using only two ranges for a line chart with categories. "oData=Array(oExtractDataSheet.getCellRangeByName( "J6:J35" ).getRangeAddress(), _ oExtractDataSheet.getCellRangeByName( "E6:E35" ).getRangeAddress())". Which changes chart data range to "$'FHFA Housing Price Index'.$E$6:$E$35,$'FHFA Housing Price Index'.$J$6:$J$35", but this leaves both ranges of data assigned to categories.

( 2016-08-15 03:49:52 +0100 )edit

Actually this suggests a possible work around. I'll try first making a new set of data sequences for all the data series of the chart, then set the data range of the chart with only the category range and then apply the modified data sequences back to the chart.

( 2016-08-15 03:54:24 +0100 )edit