[Solved] Macro to change Chart width

[frozbie]

Hi,

Can anyone help with how to change the actual width of an embedded chart in StarOffice basic?

I'm using StarOffice 8 PU10 on Windows Vista Ultimate.

Code I have tried is below:

Code: Select all

Sub Main
Dim oDoc As Object
Dim Charts As Object
Dim Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress

oDoc = ThisComponent 
Charts = oDoc.getSheets().getByName("Sheet1").Charts
	
	
Rect.X = 12000
Rect.Y = 2000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 0 
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 4
RangeAddress(0).EndRow = 2
 
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)

ChangeChart
'ChangeWidth
End Sub


Sub ChangeChart()
'//Aim of code is to change the actual width of the chart
Dim oDoc As Object
Dim Charts As Object
Dim Chart as Object
Dim Cht as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress

oDoc = ThisComponent 
Charts = oDoc.getSheets().getByName("Sheet1").Charts
	
	
RangeAddress(0).Sheet = oDoc.getSheets().getByName("Sheet1").RangeAddress.Sheet '// passes sheet index to celladdress object
RangeAddress(0).StartColumn = 0 
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 4
RangeAddress(0).EndRow = 4
 
Chart = Charts.getByName("MyChart")'.embeddedObject
Cht = Charts.getByName("MyChart").embeddedObject
Chart.setRanges(RangeAddress())
'Cht.Area.Size.Width = 40000 ' didn't fail but didn't change

'Cht.Area.Width = 40000 'failed
'Chart.Size.Width = 40000 ' failed
'Chart.Area.Size.Width = 40000 ' property or method not found
'Chart.Area.Width = 40000 ' property or method not found

'Cht.Area.setPropertyValue.("Width",40000)'variable expected
'Cht.setPropertyValue("Area.Size.Width",40000) 'fail
'Cht.Area.Size.setPropertyValue("Width",40000) 'fail
'Chart.Area.Size.setPropertyValue("Width",40000) 'fail
'Chart.Area.setPropertyValue.("Width",40000)'variable expected

Rect.X = 12000
Rect.Y = 2000
Rect.Width = 40000
Rect.Height = 7000

'Cht.setPropertyValue("Rectangle",40000)'fail
'Chart.setPropertyValue("Rectangle",40000)'fail

'Rect = Chart.Rectangle
'Chart.Rect.X = 12000'fail
'Chart.Rect.Y = 2000'fail
'Chart.Rect.Width = 40000'fail
'Chart.Rect.Height = 7000'fail
'Rect.X = 12000'fail
'Rect.Y = 2000'fail
'Rect.Width = 40000'fail
'Rect.Height = 7000'fail

End Sub

As you can see, I'm guessing how to set the width property of the chart object. Looking at the object properties, the width seems to be:
Area.Size.Width

I've hunted on http://api.openoffice.org/docs; the Developers manual; this forum; and http://wiki.services.openoffice.org/wiki/Documentation
but nothing clearly shows how to set this property.

I think setPropertyValue() should work or in the Developers manual, just referencing the property directly as in:

Code: Select all

Chart.Diagram.Deep = true

Also, as chart is set up using the Rectangle Struct, I wonder if that is how to access it but am unsure how.

Any pointers or help very welcome!

I've attached a sheet example with code.
You would need to run Main to create the chart, then I have tried to modify using sub ChangeChart

Thanks
Mark

[Villeroy]

Every sheet in a spreadsheet has a draw page. A draw page has shapes. Shapes are anything you can draw, move and resize on a draw page regardless of what it may represent otherwise. Browse the draw page's collection of shapes.

[frozbie]

by Villeroy on Fri Feb 06, 2009 5:06 pm
Every sheet in a spreadsheet has a draw page. A draw page has shapes. Shapes are anything you can draw, move and resize on a draw page regardless of what it may represent otherwise. Browse the draw page's collection of shapes.

Villeroy,

Thanks, I'll do this and post back.

Mark

[frozbie]

Villeroy,

Thank you! That was the advice I needed!

Not terribly clear in the documentation how to translate into basic but the logic applies no matter what the language. I've updated the code I've used which could replace the existing code in previously uploaded spreadsheet

Regards

Mark

Code: Select all

Sub Main
Dim oDoc As Object
Dim Charts As Object
Dim Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress

oDoc = ThisComponent 
Charts = oDoc.getSheets().getByName("Sheet1").Charts
	
Rect.X = 12000
Rect.Y = 2000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 0 
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 4
RangeAddress(0).EndRow = 2
 
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)

'// Expand data range  of chart
ChangeChartData
'// Expand width of chart
ChangeDrawPageShapeWidth
End Sub

Sub ChangeChartData()
'//Aim of code is to change the actual width of the chart
Dim oDoc As Object
Dim Charts As Object
Dim Chart as Object
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress

oDoc = ThisComponent 
Charts = oDoc.getSheets().getByName("Sheet1").Charts

RangeAddress(0).Sheet = oDoc.getSheets().getByName("Sheet1").RangeAddress.Sheet '// passes sheet index to celladdress object
RangeAddress(0).StartColumn = 0 
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 4
RangeAddress(0).EndRow = 4
 
Chart = Charts.getByName("MyChart")
Chart.setRanges(RangeAddress())

End Sub

Sub ChangeDrawPageShapeWidth()
Dim oDoc As Object
Dim oDrawPage as Object
Dim aSize as Object

oDoc = ThisComponent 
oDrawPage = oDoc.getSheets().getByName("Sheet1").getDrawPage().getByIndex(0)

aSize = oDrawPage.getSize()
aSize.Width = aSize.Width * 4
oDrawPage.setSize(aSize)
End Sub