How to alter Calc chart data range in a macro

[AustinGreen]

I need to talk to a human about this, having tried AI as well as web trawling; there are so many examples out there, none of which work on my system (Windows 11, LibreOffice version 26.2.0.3)

All I want is to be able to modify the end row of a data series of a chart, from within a macro. It’s not hard to get the current value:
ThisComponent.CurrentController.ActiveSheet.Charts(0).Ranges(0).EndRow
gives the value that I want to change, but assigning a new value to it has no effect.

I’d be most grateful for some help, preferably some sample working code.

[robleyd]

Did your web trawling include this page from the Wiki?

[AustinGreen]

I don’t think so, but I don’t see the relevance of it; it doesn’t mention charts at all. I don’t want to access the cells that the range refers to, I just want to change the end row number of the range definition within the chart. As you can do manually by putting the chart into edit mode, then selecting the Data Ranges option.

[cwolan]

@AustinGreen

All I want is to be able to modify the end row of a data series of a chart, from within a macro. It’s not hard to get the current value:
ThisComponent.CurrentController.ActiveSheet.Charts(0).Ranges(0).EndRow
gives the value that I want to change, but assigning a new value to it has no effect.

How about this?

dim ranges(0)

chart = ThisComponent.CurrentController.ActiveSheet.Charts(0)

range = chart.Ranges(0)
range.EndRow = new_value  ' assigning a new value

ranges(0) = range

chart.setRanges(ranges)

[Villeroy]

There are 2 common ways to adjust data ranges.

1. Use a range large enough to take all expectable values in future, say A:AA (entire columns A through AA). In this case, you must not put any data below the current chart data.
2. Use a defined range, say A1:F12 and insert new rows (or columns) for incoming new data. You may use the space below or right of A1:F12 for anything else. That content will be moved as you insert new cells. In order to make this really work, you have to fix a wrong default setting under Tools>Options>Calc>General>“Expand references when new rows/columns are inserted”.

The above applies to all kinds of spreadsheet references in charts, formulas, names, conditional formatting etc.

[AustinGreen]

Thanks cwolan, that’s an improvement - I always got ‘no such method’ when I tried to use chart.setRanges before, don’t know what I was doing differently, but anyway it now does at least set the ranges. However, the chart has two data series, and setting the ranges seems to remove the second one, so we’re not quite there yet.

[cwolan]

@AustinGreen

However, the chart has two data series, and setting the ranges seems to remove the second one, so we’re not quite there yet.

How is the data range defined in the Data Ranges dialog (the Data Range tab)? One range or more?

You shall inspect the rngs object…

chart = ThisComponent.CurrentController.ActiveSheet.Charts(0)
rngs = chart.Ranges

[Lupp]

@cwolan:
And how do you get the TableChart object for a chart embedded into a known shape ?
(if you don’t know the index in sheet.Charts() for sure)?

[AustinGreen]

@cwolan: In the dialog the Data Ranges tab is enmpty; the Data Series shows the ranges.

In the chart.Ranges object there is an array of 4 ranges; the first if those contains the EndRow value that I want to change. However, changing it has no effect on the chart, even if I call chart.setRanges().

[AustinGreen]

@Lupp: there is only one chart in the sheet.