Craig Johnson wrote:
Hi folks! Just joined the [email protected] mailing list.
I'm trying to write some staroffice basic macros to update some charts
that display data from weekly updated spreadsheets. I have the macros
in place to update the spreadsheet rows with new data but can't find
how to access the chart data ranges. I know how to get at them from
the Format->Data Ranges popup but I need to change the ranges from
within a macro.
Anyone know how to access spreadsheet chart data ranges from a macro?
Depends on what you need or desire to do...
First, I created a chart:
Sub CreateChart
Dim oSheet 'Sheet containing the chart
Dim oRect 'How big is the chart
Dim oCharts 'Charts in the sheet
Dim oChart 'Created chart
Dim oAddress 'Address of data to plot
Dim sName$ 'Chart name
Dim oChartDoc 'Embedded chart object
Dim oTitle 'Chart title object
Dim oDiagram 'Inserted diagram (data).
Dim sDataRng$ 'Where is the data
sName = "ADP_Chart"
sDataRng = "A1:D6"
'sDataRng = "A33:D38"
oSheet = ThisComponent.sheets(0)
oAddress = oSheet.getCellRangeByName( sDataRng ).getRangeAddress()
oCharts = oSheet.getCharts()
If NOT oCharts.hasByName(sName) Then
oRect = createObject("com.sun.star.awt.Rectangle")
oRect.X = 10000
oRect.Y = 1000
oRect.width = 10000
oRect.Height= 10000
' The rectangle identifies the dimensions in 1/100 mm.
' The address is the location of the data.
' True indicates that column headings should be used.
' False indicates that Row headings should not be used.
oCharts.addNewByName(sName, oRect, Array(oAddress), True, False)
End If
oChart = oCharts.getByName( sName )
oChart.setRanges(Array(oAddress))
oChartDoc = oChart.getEmbeddedObject()
'oChartDoc.attachData(oAddress)
oTitle = oChartDoc.getTitle()
oTitle.String = "Andy - " & Now
' Create a diagram.
'oDiagram = oChartDoc.createInstance( "com.sun.star.chart.LineDiagram" )
oDiagram = oChartDoc.createInstance( "com.sun.star.chart.XYDiagram" )
oChartDoc.setDiagram( oDiagram )
oDiagram = oChartDoc.getDiagram()
oDiagram.DataCaption = com.sun.star.chart.ChartDataCaption.VALUE
oDiagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
End Sub
If I want to use the same services and set new disjoint ranges, I can do
things such as the following:
Sub InspectChart
Dim oSheet 'Sheet containing the chart
' Dim oRect 'How big is the chart
Dim oCharts 'Charts in the sheet
Dim oChart 'Created chart
Dim sName$ 'Chart name
' Dim oChartDoc 'Embedded chart object
Dim oTitle 'Chart title object
Dim oDiagram 'Inserted diagram (data).
Dim sDataRng1$ 'Where is the data
Dim sDataRng2$ 'Where is the data
Dim oAddress1 'Address of data to plot
Dim oAddress2 'Address of data to plot
sName = "ADP_Chart"
sDataRng1 = "A1:B6"
sDataRng2 = "E1:E6"
oSheet = ThisComponent.sheets(0)
oCharts = oSheet.getCharts()
If NOT oCharts.hasByName(sName) Then
Exit Sub
End If
oAddress1 = oSheet.getCellRangeByName( sDataRng1 ).getRangeAddress()
oAddress2 = oSheet.getCellRangeByName( sDataRng2 ).getRangeAddress()
Dim oRanges
Dim oRange
Dim i%
Dim s$
Dim oConv
oConv =
ThisComponent.createInstance("com.sun.star.table.CellRangeAddressConversion")
oChart = oCharts.getByName( sName )
oRanges = oChart.getRanges()
oChart.setRanges(Array(oAddress1, oAddress2))
' oChartDoc = oChart.getEmbeddedObject()
' oD = oChartDoc.getData()
' oD2 = oD.getColumnDescriptions()
' MsgBox Join(oD2, CHR$(10))
' MsgBox Join(oD.getRowDescriptions(), CHR$(10))
' Create a diagram.
' oDiagram = oChartDoc.createInstance( "com.sun.star.chart.LineDiagram" )
' oChartDoc.setDiagram( oDiagram )
' oDiagram = oChartDoc.getDiagram()
' oDiagram.DataCaption = com.sun.star.chart.ChartDataCaption.VALUE
' oDiagram.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
End Sub
There is, however, a new service, that is not yet documented, that
allows some more flexible changes, but I am having trouble figuring out
exactly how that works. I can post some examples for that as well if you
require them.
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
