Macro change Categories data range

[mark_t]

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

[Ratslinger]

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(), _
              oSheet.getCellRangeByName( "B1:B5" ).getRangeAddress(), _
             oSheet.getCellRangeByName( "C1:C5" ).getRangeAddress(), _
             oSheet.getCellRangeByName( "D1:D5" ).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.

[mark_t]

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.

[Ratslinger]

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

[mark_t]

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]

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.

[GNK]

old post I know, but …

using the above macro (Ratslinger) for a single row of data(+label 1st row), I am trying to keep a link to a Fill Color row defined in the Data Ranges (GUI) box …
I cant find many clues as to how to access and set this, maybe through DataPointsProperties (Service DataPointProperties) or similar ?
Also found VaryColorsByPoint(boolen,true) but not how those values are referenced … maybe in UserDefinedAttributes(.container.XNameContianer) - but this apparently ‘void’ in MRI for chart with Fill Color range already set in Data Ranges GUI (in calc) …
Also ::com:star:drawing FillColor … but only seems to to be for a single value not for a range-refrence …

any clues or hints out there???

(much-love||much-libre)

[Ratslinger]

@GNK


Sorry but your comment if nothing short of confusing to me.


Probably better to post as a new question instead of burying it within another question.

[Ratslinger]

@GNK


Had another thought. Have worked with charts in Base. In this post → Charts in Base forms
there is some code for data series color. May be somewhat of what you want. Code like:

'Linestyle
'	oXDataSeries.LineStyle = com.sun.star.drawing.LineStyle.SOLID
'	oXDataSeries.LineStyle = com.sun.star.drawing.LineStyle.DASH
'	oXDataSeries.LineStyle = com.sun.star.drawing.LineStyle.NONE

'Line color to be used
'   oXDataSeries.Color = RGB(192, 192, 192)

Look in the first sample in the link (ChartInBase.odb) and in the code look under LineChart

[GNK]

Thank you — was just about to think of a new Q to post, but for now …

I already saw the ChartsInBase.ods (also cheers!) - really useful method, but hard to customize for publication-style diagrams - but that’s what Calc is for right?, - to clarify:

I actually was working with PieCharts, which can have a Fill Color range specified as a range in the Edit GUI - I modified the original macro code to work with a single data-ROW + label-ROW :

The broad workflow here is to generate batches of ‘similar data’ to dump in Calc, each of which produces subsets of related Charts. The charts are pre-made templates that (ideally) just need their data-ranges adjusted for each batch. Macros are intended to directly replace manually adjusting data and other ranges in the Edit GUI.

Blockquote

Sub SetRanges
  oSheet=ThisComponent.CurrentController.ActiveSheet
  oChart = oSheet.getCharts().getByIndex(0)
  oData = Array(oSheet.getCellRangeByName( "A1:E1" ).getRangeAddress(), _    '<< defaults to label in new chart
              oSheet.getCellRangeByName( "A2:E2" ).getRangeAddress(), _  '<< single data series in row
  '>>           oSheet.getCellRangeByName( "C1:C5" ).getRangeAddress(), _
  '>>           oSheet.getCellRangeByName( "D1:D5" ).getRangeAddress())
 oChart.setRanges(oData) 
End Sub

Blockquote

This works as a basis to update Pie chart data ranges (data from Base query), but I wanted to retain the custom color schemes (linked in Base to hexi-color codes) as well - subtle database-wide control of color is needed for final images in this project - currently, such ‘properties’ are reset to default when using the macro…

I thought it should be possible to define the ‘Fill Color’ range in the macro body somewhere, but the vocabulary, grammar and syntax for this evade me.

I see that ONLY Pie Charts have ‘Fill Color’ option in GUI, so no star::Diagram(etc…or similar…) related ‘FillColor’ properties for Line or other types, so maybe just point/line colors for Pies ?? ? …
I have also found Interface XColorScheme
but not sure what to make of that yet

Of course setting Pie Charts by hand is not such a huge task (sigh), but a generic solution could also be applicable to e.g. ChartsInBase method … which could very useful.

As always, your thoughts are welcome

PS: the other solution here (by mark_t) works very nicely to modify the vertical range of a chart template in Calc to the actual data dumped to the sheet (taller or shorter), but I can not really understand how it works at all > let alone adjust to work with row-series and PieCharts … so, if needs be, I will default to some mix of this and manual adjustment in Calc

PieCharts in base forms with customizable FillColors would remain a ‘nice-to-have-but…’

(and, for reference, just saw FillColor in this: Interface XGraphics)

[Ratslinger]

@GNK

I see that ONLY Pie Charts have ‘Fill Color’ option in GUI

Sorry, not what I see. Others such as Bar have it (and works) also.
Also found this related bug → tdf#80303


Using this pie chart (bar chart worked also):

you can see the Fill Color setting.


Using this (generated by MRI):

Sub Snippet
  Dim oSheets As Variant
  Dim oObj1 As Variant
  Dim oCharts As Variant
  Dim oObj2 As Variant
  Dim oEmbeddedObject As Variant
  Dim oFirstDiagram As Variant
  Dim oCoordinateSystems As Variant
  Dim oXCoordinateSystem As Variant
  Dim oChartTypes As Variant
  Dim oXChartType As Variant
  Dim oDataSeries As Variant
  Dim oXDataSeries As Variant
  Dim oDataSequences As Variant
  Dim oXLabeledDataSequence As Variant
  Dim oValues As Variant

  oSheets = ThisComponent.getSheets()
  oObj1 = oSheets.getByName("Sheet1")
  oCharts = oObj1.getCharts()
  
  oObj2 = oCharts.getByIndex(1)
  oEmbeddedObject = oObj2.getEmbeddedObject()
  oFirstDiagram = oEmbeddedObject.getFirstDiagram()
  
  oCoordinateSystems = oFirstDiagram.getCoordinateSystems()
  oXCoordinateSystem = oCoordinateSystems(0)
  oChartTypes = oXCoordinateSystem.getChartTypes()
  
  oXChartType = oChartTypes(0)
  oDataSeries = oXChartType.getDataSeries()
  oXDataSeries = oDataSeries(0)
  
  oDataSequences = oXDataSeries.getDataSequences()
  oXLabeledDataSequence = oDataSequences(1)
  oValues = oXLabeledDataSequence.getValues()
  
End Sub

got to what you wanted. As shown in MRI:

[Ratslinger]

@GNK

charts are pre-made templates that (ideally) just need their data-ranges adjusted for each batch

Have you seen this similar post?

Calc macro failing to update sheet data from Base query

System: Windows 10, LO 6.3.4.2 (x64), Firebird I have a Calc sheet set up with a data sheet receiving data extracted by a Base query, and a second sheet with a pie chart based upon those data. I want the user to open the spreadsheet and click a [Refresh] button to refresh the data from the query, thus updating the pie chart. My Calc macro successfully: Clears the data area upon which the chart is based Opens the Data Sources dialog ([Ctrl]+[Shift]+[F4]) Navigates to the required query (which…

[mark_t]

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

[GNK]

After some web-mining, I find that this solution still seems to be very effective for mass updating pre-made charts (on a single sheet with simple table and column data) in calc with new data ranges,
not that I can make much sense of the process in the macro,
but thank you! - this works )

PS:it is handy that reference to Fill Color Range and other formatting is retained

BTW - in the DataExtract.ods , the macro to run is TestUpdateDocChartRanges …

[cloter]

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