OpenOffice Calc macro to add pie chart

Question

I am trying to insert a piechart in open-office using macro. But the code shows error:

Line:

Dim oDiagram As New com.sun.star.chart.PieDiagram

Error:

"Object not accessible. Invalid reference."

I am unable to figure out why. Kindly help. Here is my complete macro code:

Sub Macro1

    Dim oRange as Object
    Dim oRangeAddress(1) As New com.sun.star.table.CellRangeAddress
    Dim oDiagram As New com.sun.star.chart.PieDiagram
    Dim oRect As New com.sun.star.awt.Rectangle
    Dim cTitle as String

    oRange = thisComponent.getCurrentSelection.getRangeAddress
    oSheets = ThisComponent.getSheets()
    oSheet = oSheets.getByIndex(0)
    oCharts = oSheet.Charts

    oRect.Width = 10000
    oRect.Height = 10000
    oRect.X = 8000
    oRect.Y = 1000

    oRangeAddress(0).Sheet = oRange.Sheet
    oRangeAddress(0).StartColumn = 0
    oRangeAddress(0).StartRow = 0
    oRangeAddress(0).EndColumn = 1
    oRangeAddress(0).EndRow = 2

    cTitle = "Test Results"
    oCharts.addNewByName(cTitle,oRect,oRangeAddress(),TRUE, TRUE)
    oChart = oCharts.getByName(cTitle).embeddedObject
    oChart.Diagram = oDiagram
    oChart.HasMainTitle = True
    oChart.Title.String = cTitle

End Sub

Here is the input sheet data:

Input sheet data.

AFAIK you can't instantiate a new diagram using `New com.sun.star.chart.PieDiagram`. Instead, you could just use `Dim oChart as Object`. But now, `oCharts.getByName(cTitle).embeddedObject` throws a `NoSuchElementException` - i'm not sure why, same happens with [sample code from OOo Wiki](https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Charts_in_Spreadsheets)... :-( — tohuwawohu, Jun 24 '14 at 19:28
I've added an answer with an example working with OOo.Calc (but not with LIbreOffice). — tohuwawohu, Jun 26 '14 at 08:18
In version 4.3.0, the issue [seems to be solved](https://bugs.freedesktop.org/show_bug.cgi?id=80494#c6) - at least regarding the saple code from the OOo Wiki. — tohuwawohu, Jul 30 '14 at 10:17

tohuwawohu · Accepted Answer · 2014-06-26T08:23:45.310

You can't instantiate a com.sun.star.chart.PieDiagram directly, independent of an already-existing chart. Instead, you'll have to create the chart first, and then create a PieDiagram. Thus, to make the macro work, do the following:

remove the line Dim oDiagram As New com.sun.star.chart.PieDiagram
change the line oChart.Diagram = oDiagram to oChart.Diagram = oChart.createInstance("com.sun.star.chart.PieDiagram").

This results in the following code (i've tested this with OpenOffice.org Calc 4.1.0 on Win7):

Sub Macro1

    Dim oRange as Object
    Dim oRangeAddress(1) As New com.sun.star.table.CellRangeAddress
    Dim oRect As New com.sun.star.awt.Rectangle
    Dim cTitle as String

    oRange = thisComponent.getCurrentSelection.getRangeAddress
    oSheets = ThisComponent.getSheets()
    oSheet = oSheets.getByIndex(0)
    oCharts = oSheet.Charts

    oRect.Width = 10000
    oRect.Height = 10000
    oRect.X = 8000
    oRect.Y = 1000

    oRangeAddress(0).Sheet = oRange.Sheet
    oRangeAddress(0).StartColumn = 0
    oRangeAddress(0).StartRow = 0
    oRangeAddress(0).EndColumn = 1
    oRangeAddress(0).EndRow = 2

    cTitle = "Test Results"
    oCharts.addNewByName(cTitle,oRect,oRangeAddress(),TRUE, TRUE)
    oChart = oCharts.getByName(cTitle).embeddedObject
    oChart.Diagram = oChart.createInstance("com.sun.star.chart.PieDiagram")
    oChart.HasMainTitle = True
    oChart.Title.String = cTitle

End Sub

Running the macro should give the following result:

Pie Chart created by Basic macro

NB: The macro won't run on LibreOffice Calc; it only works with OpenOffice.org Calc. I think this a LibreOffice bug, since i coudn't find any differences in the API of OOo and LO.

OpenOffice Calc macro to add pie chart

1 Answers1

Linked