No sin, but you should have mentioned it and inserted a link to the question. @PYS did it for you.
I have not tested it for charts, but for a formula I need the below code to make changes visible.
dim XCOEO as variant
oXCOEO = oEmbeddedObject.ExtendedControlOverEmbeddedObject
oXCOEO.update()
Hello !
I guess that this way we are all wasting time. I tried to make simple and clear : just want to know if it is possible to change the range of data used by a diagram (mimetizing user interface).
I did not mention what my friend PYS (I thank so much for his help) wrote for 1 major reason + 2 regular reasons
0) 2 machine I tested does not have “dispose” method on datasequences array items !
- it does not make what I’ld like (modify range) … and there no symetric “insert” method
- I guess that using dispose of items of the array is not a safe way to do it (integrity of the diagram object)
Villeroy told us that it is possible (but after searching around a bunch I am less than hopfull!) … need to wait the idea he has. (API looks a bit weak and buggy! I search how to change column width of an array … not possible AFAIK!!)
Thank you all … just don’t waste your precious time to offer alternative solution !
Philippe
Formula ? in Writer table ? hum … are you really speaking about Writer tables ?
oEmbeddedObject.ExtendedControlOverEmbeddedObject
looks it does not exists in writer document …
Formula ? in Writer table ? hum … are you really speaking about Writer tables ?
No, I speak about a Math-Object. Math-Objects and Chart-Objects are managed the same way. They are located in the ‘Embedded Objects’ container of a text document. That is service ‘com.sun.star.text.TextEmeddedObjects’. The single element is SwXTextEmbeddedObject. And both Charts and Math-Objects have the same services, interfaces and methods. One of these methods is ‘getExtendedControlOverEmbeddedObject’, in Basic usable without ‘get’ by dot-access. That is in ‘com.sun.star.document.XEmbeddedObjectSupplier2’. LibreOffice: XEmbeddedObjectSupplier2 Interface Reference
The returned object is LibreOffice: XEmbeddedObject Interface Reference. That is the object in the state when you have selected it with a single click. And that has the method ‘update’ to update the representation of the object in the text document.
a community of people who share the same will to help others is a community of friends ! and you tried a lot ! thank you !
sorry, but as I wrote before : (property or method does not exist)
You might find ideas how to do the changes is the thread
https://forum.openoffice.org/en/forum/viewtopic.php?t=27290
and ideas, which objects are involved in the there linked method
http://hermione.s41.xrea.com/pukiwiki/index.php?OOoBasic%2FCalc%2Fchart2%2Fex3
A difference between Calc and Writer is the syntax of a cell range, so the part where the strings are manipulated needs to be adapted.
Hi Regina !
I gave a look at your suggestions. I’ll try to figure out if Hermione’s code can work for Writer, after adaptations ! (after first read, I recognized parts present in PSY code … but hum ! doubts! 
)
The link to oooForum does not work right now. I’ll try it tomorrow !
Thanks a lot for your attention.
Philippe
- Nobody seems to have experienced what i saw: The table - and consequently the chart - were rotten from the beginning. Only the integer numbers contained in the table were evaluated correctly because the
NumberFormatsettings were inconsistent: The language was (for me)Default (English), but the used decimal separator was mostly the comma, and only in one case the point. Only after the rectification the diagram showed something of meaning for the full range (A1:G5). - I have next to no experience with charts based on TextTable objects. The concepts of text tables are so inconsistent and error-prone that … (you’re right). Youi should not expect serious data presentation to work this way - and you should expect that charts with data from TT aren’t actually compatible with charts from spreadsheets. (Text tables even use a different column naming.) That’s all a mess, probably for “compatibility reasons”.
- If you finally want to move the chart creation to spreadsheets anyway, you should not expect that something having worked with a text table will also work in a spreadsheet.
- Develop with spreadsheets from the begiknning.
I attach what I got after making the NumberFormats of the table consistent.
plage1.odt (26.6 KB)
1 Like
Aha. Using the dispatcher to update the chart is a nice idea. I have tried method update() from EmbeddedObject. But in contrast to Math-objects it does not work for chart-objects.
Find attached an idea for Writer. Macro remove a series from chart.odt (20.3 KB)
In my test it did. See attached.
The code is raw, of course.
appendColumnsAsDataSeriesToChart_.ods (18.7 KB)
Test this, but add the colors to the aColors for more Columns.
diagram-ve-writeru-pokus1.odt (23.9 kB)
source: Apache OpenOffice Community Forum - Add multiple series in calc chart using macros - (View topic)
Perfect !!! Super !!! I am so happy ! … It does exactly the job !
Solution !!!
Thank you so much ! … we did not find it because we were looking for “sequences”, not for “series” !!! and for Writer solution !
And thanks to all of you for your attention and help !!! Finally KamilLanda got the solution !!
Philippe
Hello KamilLanda,
You really can do a lot with macros . For example you can find a bill system built on openOffice Calc.
What I got comes from PYS ! Thank to him !
Data is, as far as I understand, just a view of DataSequences. I did not find documentation on how to manage diagram structure with macro, but the code below shows that if you make a change in Data structure, DataSequences is updated.
' get first diagram
oDocument = ThisComponent.CurrentController.Frame
o = oDocument.controller.model.embeddedObjects.getByIndex(0)
oExt = o.ExtendedControlOverEmbeddedObject
oDiag = oExt.component
' get data structure (oDiag.Data)
oData = oDiag.getData
' get data array
d = oData.getData
' change any value
d(0)(1) = -1
' update data structure
oData.setData( d)
' get sequence structure
oSeq = oDiag.DataSequences(2)
' show value updated in DataSequences structure
msgBox oSeq(0).Values.Data(0)
Through “Data” we can manage DataSequences which seems to be where the diagram get what it needs to update its range configuration.
The big trouble left is : it does not allow to change columns number ! And that exactly what we are trying to find now !
Any way, thank you for your attention,
Philippe
Only small note, I suppose you will make the code optimalization :-).
For example the array p() starts from index 0 but it is null, because the loop starts from 1: for numColonne=1…
Found the solution for column A ! … I used my version of MiseAJourColonne to update DataSequence(0)
Complete code … this way the code preserve the diagram configuration … just change the data range.
Column A needs to be treated separatly, not considered as data !
Sub A1D3
ModifSourceDiagramme(array("B", "C", "D"), 3)
End Sub
Sub ModifSourceDiagramme(colonnes, derniereLigne as integer)
'COLORS FOR THE CHART
dim aColors() : aColors=array(RGB(0,69,134), RGB(255,66,14), RGB(255,211,32), RGB(87,157,28), RGB(126,0,33), RGB(131,202,255))
dim o, oExt, document, oDiag, oDataProvider, oDataSequences, oDiagram, oCooSys, oCoods, oChartTypes, oChartType, oDataSeriesList, oDataSeries
dim dimDataSequences as integer, numColonne as integer, col as string, nseq as integer
dim uColonnes as integer : uColonnes=ubound(colonnes)
' récupère le diagramme (unique dans le document)
document=ThisComponent.CurrentController.Frame
o=document.controller.model.embeddedObjects.getByIndex(0)
oExt=o.ExtendedControlOverEmbeddedObject
oDiag=oExt.component
oDataProvider=oDiag.getDataProvider()
oDataSequences=oDiag.DataSequences
oDiagram=oDiag.getFirstDiagram()
oCooSys=oDiagram.getCoordinateSystems()
oCoods=oCooSys(0) ' this chart has only a coordinate system
oChartTypes=oCoods.getChartTypes() ' chart type one by one
oChartType=oChartTypes(0)
MiseAJourColonneA( oDataProvider, oDataSequences, 0, _
"Donnees." & "A1:" & "A1", _
"Donnees." & "A2:" & col & cstr(derniereLigne) )
' DataSeries actuel
oDataSeries = oChartType.getDataSeries()
redim preserve oDataSeries(uColonnes)
for numColonne=0 to uColonnes
col=colonnes(numColonne)
MiseAJourColonne( oDataProvider, oDataSeries, numColonne, _
"Donnees." & col & "1:" & col & "1", _
"Donnees." & col & "2:" & col & cstr(derniereLigne) )
next numColonne
' mettre à jour DataSeries
oChartType.setDataSeries(oDataSeries)
' mettre à jour le diagramme
MiseAJourDiagrammes()
End Sub
Sub MiseAJourColonne(oDataProvider as object, oDataSeries, numColonne as integer, sLabel as string, sValeurs as string) as object
dim oValues as object, oData as object, oLabel as object, oSeq as object
oData=CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
oSeq=CreateDataSequence(oDataProvider, sValeurs, "values-y")
oData.setValues(oSeq)
oLabel=CreateDataSequence(oDataProvider, sLabel, "")
oData.setLabel(oLabel) ' label is used as name
oDataSeries(numColonne).setData(array(oData))
End sub
Sub **MiseAJourColonneA**(oDataProvider as object, oDataSequences as object, nseq as integer, sLabel as string, sValeurs as string)
dim oSeq as object, oValues as object
dim newrepr as string
dim newvalues as object
dim newseqValues as object
dim newseqLabel as object
dim oLabel as object
dim newlabel as object
oSeq = oDataSequences(nseq)
oValues = oSeq.Values
newrepr = sValeurs
newvalues = oDataProvider.createDataSequenceByRangeRepresentation(newrepr)
newvalues.Role = oValues.Role
newseqValues = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
newseqValues.Values = newvalues
oLabel = oSeq.Label
newrepr = sLabel
newLabel = oDataProvider.createDataSequenceByRangeRepresentation(newrepr)
' newLabel.Role = oLabel.Role --> pour Label, toujours = ""
newseqLabel = CreateUnoService("com.sun.star.chart2.data.LabeledDataSequence")
newseqLabel.Label = newLabel
with oSeq
.setLabel(newseqLabel.label)
.setValues(newseqValues.values)
end with
end Sub
and now … implemented your solution in my aplication … crashed L.O. !!! 
… that’s the next challenge !
Nice … I had already seen usedData … it contains o copy of DataSequences where we can use redim !!! … I do not imagine the purpose of those views of DataSequences.
I checked that redim is allowed on UsedData.DataSequences ! Then I ran MiseAJourDiagrammes (update Diagrams) and it had no effect at all !
I can’t see any method to update DataSequences from UsedData ! 
Please consider to post code as code using the tool Preformatted text (Ctrl+E)?
sorry!
reply mail … thought it was private !!!