Ask Your Question
0

Create graph with macro [closed]

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

Fede gravatar image

updated 2020-10-17 10:32:31 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-17 10:32:43.293417

1 Answer

Sort by » oldest newest most voted
1

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

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

Question Tools

1 follower

Stats

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

Seen: 1,743 times

Last updated: Oct 11 '17