Ask Your Question

Macro change Categories data range

asked 2016-08-14 19:27:11 +0100

mark_t gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-08-15 05:58:34 +0100

mark_t gravatar image

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

edit flag offensive delete link more

answered 2020-04-20 19:28:30 +0100

cloter gravatar image

updated 2020-04-20 19:29:17 +0100

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

edit flag offensive delete link more

answered 2016-08-15 02:12:08 +0100

Ratslinger gravatar image

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
  oChart = oSheet.getCharts().getByIndex(0)
  oData = Array(oSheet.getCellRangeByName( "A1:A5" ).getRangeAddress(), _
              oSheet.getCellRangeByName( "B1:B5" ).getRangeAddress(), _
             oSheet.getCellRangeByName( "C1:C5" ).getRangeAddress(), _
             oSheet.getCellRangeByName( "D1:D5" ).getRangeAddress())
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.

edit flag offensive delete link 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.

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

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

Ratslinger gravatar imageRatslinger ( 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.

mark_t gravatar imagemark_t ( 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.

mark_t gravatar imagemark_t ( 2016-08-15 03:54:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-08-14 19:27:11 +0100

Seen: 1,072 times

Last updated: Apr 20 '20