Update Vertical Ranges for ALL calc charts, from new row data, EXCEPT for 'chart.title = 'X'

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 :slight_smile: (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 newchart.RangeIsUpdateable’ property …hmmm … maybe not :slight_smile:

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;

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	
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," ")
'	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()
				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 ), _
			If NOT IsNull(oSequenceY) Then
				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
	Next i
	If bCategories Then
		'	Change the range of the category data
		oRange = oChart.Ranges(0)
		If bFirstRowLabel Then
			oRange.StartRow = 4
			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
	CreateDataSequence = oDataSequence
End Function

REM original by mark_T >>>>>>>>>>>>>>>>>>>>>