Automating the Title of a chart in LibreOffice Calc

Question

I have found the below code (changed to my needs) here

I try to change the titles of many charts automatically, using macro in libre calc 7.3.0.

I Know which cells contain the titles and I want them to add them to charts. How can I make this VBA vcode to work?

Const SCells = "L8, T8, AA8"

' Set the title of the first Chart to the contents of C1
Sub SetTitle
    ' Get active sheet
    oSheet = ThisComponent.CurrentController.ActiveSheet
    
    aCells = Split(SCells,",")
    
    for i = uBound(aCells) to 0 step -1
        
    ' Get the cell containing the chart title, in this case C1
    oCell = oSheet.getCellRangeByName(aCells(i))
   
    oCharts = oSheet.getCharts() 
    ' Get the chart with index 0, which is the first chart created
    ' to get the second you would use 1, the third 2 and so on...
    oChart = oCharts.getByIndex(i)

    oChartDoc = oChart.getEmbeddedObject()

    'Change title
    oChartDoc.getTitle().String = oCell.getString() 

    next i
    
    
    
End Sub
        
        
    

Answer 1

What I was doing wrongly? I had spaces in Const SCells content: "L11, T11, E11" which was wrong.

I checked the content of aCells using xraytool and I found that the content was parsed as 1) "L11", 2) " T11" and 3) " E11", with spaces.

So you can change automatically the title of your charts in a librecalc sheet using that vba code:

' NOTE: NO SPACES OR OTHER CHARACTERS
Const SCells = "L11,T11,E11"


Sub SetTitle

    ' Get active sheet
    oSheet = ThisComponent.CurrentController.ActiveSheet
    
    ' Split SCells content by "," 
    ' see Print aCells for corrent content
    aCells = Split(SCells,",")
       
               
        'enumarate by "1" point increment
    for i  = uBound(aCells) to 0 step -1
    
      ' using aCells Content
      oCell  = oSheet.getCellRangeByName(aCells(i))

      oCharts = oSheet.getCharts() 
      ' Get the chart with index 0, which is the first chart created
      ' to get the second you would use 1, the third 2 and so on...
      oChart = oCharts.getByIndex(i)

      oChartDoc = oChart.getEmbeddedObject()

      'Change title
      oChartDoc.getTitle().String = oCell.getString() 

      ' xray oChart.Name

    next i
    
 End Sub