This is a follow up post from:
(and a sort of answer to a question not asked)
I found the the solution by mark_t (see DataExtract.ods) to be very useful, except that I could not exclude charts in ‘composite figures’ from updates (e.g. pie charts across single rows of data, with other charts using many rows of data from the same sheet).
Other LovelyOffice solutions found hereabouts require various input of ranges or chart names or other cell variables,or database data-preparation, etc ,
mark_ts solution automates this nicely for cases with ‘simple data’ in multiple/lots/too-many-to-do-manually charts 
(for vertical range expansion/contraction at least, + deals with gaps from empty rows between data blocks).
I have made some trivial modifications to the original code, so that the macro will update ONLY charts with title ‘Is Null’ or with title<>‘X’ (i.e. charts with title ‘X’ are excluded from update)
*…also posting because I wasted time trying to get the calc-GUI user-renamed ‘Chart.Names’ as a variable, but could only get the original calc default name instead (e.g. ‘Object 1’) as the returned string value in BASIC macro … sigh:( …hence use of the chart.diagram.title rather than chart.name … nevertheless .title might be overall best for flexibility, vs ‘chart.type’ or vs ‘chart.index’ etc *
maybe something like this could work as an in-built function for LO ? - Perhaps with a new ‘chart.RangeIsUpdateable’ property …hmmm … maybe not
A version of the original macro which expands columns as well/instead of rows would probably also be very useful, but is more than my tinkering ability (and current need)…so here, to trip up upon, I will leave it;
Sub UPDATE_ALL_CALC_CHART_VERTICAL_RANGES_EXCEPT_FOR_THOSE_WITH_TITLES_LIKE_X( )
REM ... use e.g. 6 point white font for a dummy 'X' title on non-updating charts (row-based pies for example)
REM ...could maybe use some input variables here to branch for differnt chart-title-keys...
call UpdateDocChartRanges_SELECTIVE_BY_TITLE( thisComponent, "X" )
End Sub
Sub UpdateDocChartRanges_SELECTIVE_BY_TITLE( oExtractDataDoc As Object, EXCLUDE As string )
Dim iLastRow As Long
Dim oExtractDataSheet As Object
Dim oExtractDataChart As Object
Dim TIT As String
Dim oChartDoc As Object
Dim oTitle As Variant
ii = 0
iii=0
For Each oExtractDataSheet In oExtractDataDoc.Sheets
' oExtractDataSheet = oExtractDataDoc.Sheets.getByIndex(0)
'oChartS = oExtractDataSheet.getCharts()
'oChartNAME = oChartS.Chart.Name() REM the DEFAULT chart name, not any user defined names :/ !!!
' oSPLIT = split(oChartNAME," ")
' SPLIT = oSPLIT(0)
' If SPLIT = "CHART" then ......
'SPLIT = "DEPTH " & ? .....
' If oChartS.hasByName(SPLIT) Then ......
REM user defined chart.name has problems
REM see: https://bugs.documentfoundation.org/show_bug.cgi?id=152130
iLastRow = ubound( oExtractDataSheet.Data )
For Each oExtractDataChart In oExtractDataSheet.Charts
oSheet = ThisComponent.sheets.GetByIndex(iii) REM Your Chart sheet
oCharts = oSheet.getCharts()
oExtractDataChart = oCharts.getbyIndex(ii)
SH = oExtractDataChart.getName()
'TY = oExtractDataChart.getChartType()
oChartDoc = oExtractDataChart.getEmbeddedObject()
'oChartDoc.attachData(oAddress)
oTitle = oChartDoc.getTitle()
TIT = oTitle.string & "XX" REM ...to deal with null title.string
XXX = EXCLUDE & "XX"
if TIT <> XXX Then REM ... ie exclude title "X" from update
' msgbox (SH & ii & TIT )
' If oChartS.hasByName("DEPTH 3") Then UpdateChartRanges( oChartS, 6, iLastRow + 1 ) >>> :?
UpdateChartRanges( oExtractDataChart, 6, iLastRow + 1 ) ' Replace chart datasequences of chart series
End If
ii = ii +1
Next oExtractDataChart
iii = iii + 1
Next oExtractDataSheet
End Sub
REM original by mark_T >> https://ask.libreoffice.org/t/macro-change-categories-data-range/20359 >>>>>>>>>>>>>>>>>>>
Sub UpdateChartRanges( oChart As Object, iFirstRow As Long, iLastRow As Long )
Dim oChartDoc As Object
Dim oDataProvider As Object
Dim oCooSys As Object
Dim oCoods As Object
Dim oChartTypes As Object
Dim oChartType As Object
Dim oDataSeriesList As Variant
Dim oDataSequence As Object
Dim oValues As Object
Dim oNewDataSeries() As Object
Dim oSequenceY As Object
Dim oSequenceLabel As Object
Dim i As Integer
Dim n As Integer
Dim bCategories As Boolean
Dim il As Integer
Dim iu As Integer
Dim vSequences() As Object
Dim bFirstRowLabel As Boolean
Dim oRange As Object
oChartDoc = oChart.EmbeddedObject
oDataProvider = oChartDoc.getDataProvider()
oCooSys = oChartDoc.getFirstDiagram().getCoordinateSystems()
oCoods = oCooSys(0) ' this chart has only a coordinate system
oChartTypes = oCoods.getChartTypes() ' chart type one by one
oChartType = oChartTypes(0)
' all data series belongs the chart type
oDataSeriesList = oChartType.getDataSeries()
' Save and then disable categories so we can treat them as series and restore later
bCategories = oChartDoc.DataSourceLabelsInFirstColumn
' Record if first row is label
bFirstRowLabel = oChartDoc.DataSourceLabelsInFirstRow
ReDim oNewDataSeries(lbound(oDataSeriesList) To ubound(oDataSeriesList)) As Object
' For each series in the chart
For i = lbound(oDataSeriesList) To ubound(oDataSeriesList)
' create new DataSeries
oNewDataSeries(i) = CreateUnoService("com.sun.star.chart2.DataSeries")
' Copy the existing formatting of this series
oNewDataSeries(i) = oDataSeriesList(i)
il = lbound(oNewDataSeries(i).DataSequences)
iu = ubound(oNewDataSeries(i).DataSequences)
ReDim vSequences(il To iu) As Object
' Update each data sequence in each series
For n = il To iu
oDataSequence = oNewDataSeries(i).DataSequences(n)
vSequences(n) = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
oSequenceY = CreateDataSequence(oDataProvider, _
UpdateSeriesRange( oDataSequence.Values.SourceRangeRepresentation, iFirstRow, iLastRow ), _
oDataSequence.Values.Role)
If NOT IsNull(oSequenceY) Then
vSequences(n).setValues(oSequenceY)
If NOT IsNull(oDataSequence.Label) Then
oSequenceLabel = CreateDataSequence(oDataProvider, oDataSequence.Label.SourceRangeRepresentation, "")
vSequences(n).setLabel(oSequenceLabel) ' label is used as name
End If
End If
Next n
oNewDataSeries(i).setData(vSequences)
Next i
If bCategories Then
' Change the range of the category data
oRange = oChart.Ranges(0)
If bFirstRowLabel Then
oRange.StartRow = 4
Else
oRange.StartRow = 5
End If
oRange.EndRow = iLastRow - 1
' Set the new category data range in the chart data range
oChart.Ranges = Array(oRange)
' Put the data series back in the chart
oChartType.DataSeries = oNewDataSeries
End If
End Sub
Function UpdateSeriesRange( ByRef sRange As String, iFirstRow As Long, iLastRow As Long ) As String
Dim sUpdateSeriesRange As String
Dim iColon As Integer
Dim iDot As Integer
Dim iFirst As Integer
Dim iLast As Integer
iColon = InStr( sRange, ":" )
iDot = InStr( sRange, "." )
iFirst = InStr( Mid( sRange, iDot + 2 ), "$" ) +iDot + 1
iLast = InStr( Mid( sRange, iColon + 2 ), "$" ) + iColon + 1
sUpdateSeriesRange = Left( sRange, iFirst ) & Format( iFirstRow, "0" ) & Mid( sRange, iColon, iLast - iColon + 1 ) & Format (iLastRow, "0" )
UpdateSeriesRange = sUpdateSeriesRange
End Function
Function CreateDataSequence( _
oDataProvider As Object, _
sRangeRepresentation As String, sRole As String ) As Object
' Based on post by Hanya
' https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=8991#p46467
' creat new DataSequence from range representaion
' that provides real data and its role in the series
' oDataProvider: com.sun.star.chart2.data.XDataProvider
' sRangeRepresentation: range address e.g. Sheet1.A1:B2
' sRole: role is defined in com.sun.star.chart2.data.DataSequenceRole
Dim oDataSequence As Object
On Error GoTo Handler
' create .chart2.data.DataSequence from range representation
oDataSequence = oDataProvider.createDataSequenceByRangeRepresentation(sRangeRepresentation)
If NOT IsNull(oDataSequence) Then
oDataSequence.Role = sRole
End If
Handler:
CreateDataSequence = oDataSequence
End Function
REM original by mark_T >>>>>>>>>>>>>>>>>>>>>
Love||you||Libre