Ask Your Question
0

Create graph with macro

asked 2017-10-11 14:51:34 +0200

Fede gravatar image

Hello, I would like to create a graph with ooo macro, and I have this doubt: data are in "Sheet1" and the graph is in "Sheet2", what's is the is truction the I must to use, so:

-> keep the data from "sheet1" -> create the graph in "sheet2"

this is my code

Sub CreateCalcWithSimpleChart

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
Dim oCalcDoc

oCalcDoc = CreateCalcForChart()

sName = "Example_01"
sDataRng = "W71:AD120"
oSheets = ThisComponent.getSheets()
oSheet  = oSheets.getbyIndex(2)
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 = 10000
oRect.width = 200000
oRect.Height= 100000
' 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), False, True)
End If

oChart = oCharts.getByName( sName )  
oChart.setRanges(Array(oAddress))
oChartDoc = oChart.getEmbeddedObject()
'oChartDoc.attachData(oAddress)  
oTitle = oChartDoc.getTitle()  
oTitle.String = "Speed - Torque"

' 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

Thank you

LO. 5.4.1 OS windpws10

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2017-10-11 16:53:53 +0200

Ratslinger gravatar image

Hello,

Your thinking of sheets seems to be incorrect. If you have two sheets, 'Sheet1' and 'Sheet2' in that order, the index for 'Sheet1' = 0 and the index for 'Sheet2' = 1. You must then get the range from the proper index.

Your section of code:

sName = "Example_01"
sDataRng = "W71:AD120"
oSheets = ThisComponent.getSheets()
oSheet  = oSheets.getbyIndex(2)
oAddress = oSheet.getCellRangeByName( sDataRng ).getRangeAddress()
oCharts = oSheet.getCharts()

Should be:

sName = "Example_01"
sDataRng = "W71:AD120"
oSheets = ThisComponent.getSheets()
oSheet  = oSheets.getbyIndex(0)                       REM Get DATA from Sheet1
oAddress = oSheet.getCellRangeByName( sDataRng ).getRangeAddress()
oSheet  = oSheets.getbyIndex(1)                       REM Now work with chart on Sheet2
oCharts = oSheet.getCharts()
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-10-11 14:51:34 +0200

Seen: 693 times

Last updated: Oct 11 '17