At first, I was posting to Stackoverflow, but I didnt found much support about LO base macros. So, then I posted here. However, I didnt know If I will receive help from here. So I posted to openoffice forum too and then help came from both places.
So, now, I know. 
Copy and paste will do.
Two bugs prevent a macro free solution:
If charting in database reports would work as in LO 4.x, we could bind the spreadsheet to a database document and use a report with charts. Charts in reports would provide an additional feature. A report can generate individual charts for each grouped category, e.g. one chart for each region, time interval, sales point etc.
Please don’t include the VBA code, it’s not useful for LibreOffice :-).
Test this:
Sub CopyChartToWriter 'copy the chart from the Calc sheet to the Writer document
dim oDoc as object, oSheet as object, vChart as variant, data as object
oDoc=ThisComponent
oSheet=oDoc.Sheets(0) 'sheet with chart
vChart=getChart("MyChart1", oSheet) 'the chart named for example by right-mouse and Name, or from menu Format/ Name
if NOT isObject(vChart) then 'the chart doesn't exists
msgbox vChart & " - doesn't exists!"
exit sub
end if
oDoc.CurrentController.ActiveSheet=oSheet
oDoc.CurrentController.Select(vChart)
data=oDoc.CurrentController.getTransferable() 'LibreOffice internal Ctrl+C
rem Writer document
dim oDocWriter as object, sUrl$, oVCur as object
sUrl="private:factory/swriter" 'new writer document
'sUrl=ConvertToUrl("d:/documents/example.odt") 'or your writer file
oDocWriter=StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, array()) 'open Writer document
rem paste chart to the end of the visible cursor
oVCur=oDocWriter.CurrentController.getViewCursor 'visible cursor
oVCur.collapseToEnd
oDocWriter.CurrentController.Select(oVCur) 'select visible cursor
oDocWriter.CurrentController.insertTransferable(data) 'Libre internal Ctrl+V
End Sub
Function getChart(s$, oSheet as object) as variant 'returns the chart as object (if chart exists); or returns the chart name as string
dim i&, o as object
for i=0 to oSheet.DrawPage.Count-1
o=oSheet.DrawPage.getByIndex(i)
if o.Name=s then
getChart=o 'return the chart as object
exit function
end if
next i
getChart=s 'return the name of the chart as string
End FunctionProblem…
Steps to reproduce Bug"
Gender Female Male a1 1 1,2 a2 2 2,35 a3 2,24 2,02 a4 2,58 3,94 a5 3,6 3,77 a6 3 2,75 a7 2,65 2,51 a8 3,1 2,58 a9 3,7 2,35 a10 3,87 3,94 a11 3,78 3,77
Run the following Macro code
Sub LoadingLibraries
BasicLibraries.LoadLibrary("XrayTool")
End Sub
Sub CreateCalcWithSimpleChart
' Definitions of variables
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
' it is needed to define Point and Size in order to
' change Position and Size of Chart Objects
Dim Pos_Chart As New com.sun.star.awt.Point
Dim Pos_Title As New com.sun.star.awt.Point
Dim Pos_SubTitle As New com.sun.star.awt.Point
Dim Pos_xTitle As New com.sun.star.awt.Point
Dim Pos_yTitle As New com.sun.star.awt.Point
Dim Pos_Legend As New com.sun.star.awt.Point
Dim Size_Chart As New com.sun.star.awt.Size
Dim Size_Title As New com.sun.star.awt.Size
Dim Size_SubTitle As New com.sun.star.awt.Size
Dim Size_xTitle As New com.sun.star.awt.Size
Dim Size_yTitle As New com.sun.star.awt.Size
Dim Size_Legend As New com.sun.star.awt.Size
'Dont Forget to Define "Name of Sheet
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getbyName( "Graph_stat" )
Chart_XPos = 0
Chart_YPos = 17000
Chart_Width = 16000
Chart_Height = 10000
Chart_New_XPos = Chart_New_XPos + Chart_Width + 900
'Creation of objects into variables
oCalcDoc = CreateCalcForChart()
sName = "stat1"
sDataRng = "D17:F28"
oAddress = oSheet.getCellRangeByName(sDataRng).getRangeAddress()
oCharts = oSheet.getCharts()
oRange = oSheet.getCellRangeByName( sDataRng )
'The size of the whole chart
If NOT oCharts.hasByName(sName) Then
oRect = createObject("com.sun.star.awt.Rectangle")
oRect.X = Chart_New_XPos
oRect.Y = Chart_YPos
oRect.width = Chart_Width
oRect.Height= Chart_Height
oCharts.addNewByName(sName, oRect, Array(oAddress), True, True)
End If
' Getting the Chart for manipulations
oChart = oCharts.getByName(sName)
oChart.setRanges(Array(oAddress))
oChartDoc = oChart.getEmbeddedObject()
oDiagram = oChartDoc.createInstance("com.sun.star.chart.ChartDiagram")
oChartDoc.setDiagram(oDiagram)
oDiagram = oChartDoc.getDiagram()
'Inside the Chart, Setting Position and Size of the Diagram
oChartDoc.RefreshAddInAllowed = True
Pos_Chart.X = 300
Pos_Chart.Y = 1200
Size_Chart.width = Chart_Width - 800
Size_Chart.height = Chart_Height - 1500
oDiagram.setPosition( Pos_Chart )
oDiagram.setSize( Size_Chart )
end subYour code which I changed a little, says vCard problem
Sub CopyChartToWriterA 'copy the chart from the Calc sheet to the Writer document
dim oDoc as object
dim oSheet as object
dim vChart as variant
dim data as object
dim oDocWriter as object
' dim sUrl$ as object
dim oVCur as object
sName = "a1"
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getbyName( "Graphs_stat" )
vChart = getChart(sName, oSheet)
oDoc.CurrentController.Select(vChart)
Doc.CurrentController.ActiveSheet=oSheet
data=oDoc.CurrentController.getTransferable() 'LibreOffice internal Ctrl+C
rem Writer document
sUrl="private:factory/swriter" 'new writer document
' sUrl=ConvertToUrl("/document.odt") 'or your writer file
oDocWriter=StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, array()) 'open Writer document
rem paste chart to the end of the visible cursor
oVCur=oDocWriter.CurrentController.getViewCursor 'visible cursor
oVCur.collapseToEnd
oDocWriter.CurrentController.Select(oVCur) 'select visible cursor
oDocWriter.CurrentController.insertTransferable(data) 'Libre internal Ctrl+V
End SubThe procedure CreateCalcWithSimpleChart sets the property PersistName, so in the function getChart I changed the condition. There is also example for xray in the getChart → to view a properties of the chart.
I hope the vCard is the variable vChart
vChart=getChart(…) → you must also copy the function getChart to your macro module
Example for copy the chart created by the procedure CreateCalcWithSimpleChart is here
graf-pokus.ods (17.2 kB)
My System persists to say that no “stat1” chart exists.
If i remove the if condition, the writer document is opened but nothing is copied.
I have stucked to “name” condition.
I found out that chart also has Description and Title as whole Chart. I set them all as “stat1”.
The same error.
I filed a bug in bugzilla.
In your case, my function set properly the chart name - did you manually checked that or it is empty?
Test again
graf-pokus.ods (12.1 kB)
Only two steps: 1) Tools/ Macros/ Run macro… → from the file graf-pokus.odt the macro CreateCalcWithSimpleChart
2) Tools/ Macros/ Run macro… → from the file graf-pokus.odt the macro CopyChartToWriter
First step will put the chart to the sheet, second step will copy the chart to the new document. In my computer it is functional. Unfortunately I don’t know how to help you more.
I did it with your help (a big thanks). I will post the solution in a while (to tidy the code). I will accept your post as solution.
This macro code is working for me in your example. Can you check please that this code is working too for you? Thanks.
Sub CopyChartstoWriter
Dim oSheet 'Sheet containing the chart
Doc = ThisComponent
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getbyName( "Graph_stat" )
oDrawPage = oSheet.getDrawPage()
oZero = oDrawPage.getByIndex(0)
'The next three lines could be used to check that oZero is a chart
EmbObj = oZero.EmbeddedObject
Component = EmbObj.Component
Component.supportsService("com.sun.star.chart.ChartDocument")
'Select the chart
Doc.CurrentController.select(oZero)
data=Doc.CurrentController.getTransferable() 'LibreOffice internal Ctrl+C
rem Writer document
dim oDocWriter as object, sUrl$, oVCur as object
sUrl="private:factory/swriter" 'new writer document
'sUrl=ConvertToUrl("d:/documents/example.odt") 'or your writer file
oDocWriter=StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, array()) 'open Writer document
rem paste chart to the end of the visible cursor
oVCur=oDocWriter.CurrentController.getViewCursor 'visible cursor
oVCur.collapseToEnd
oDocWriter.CurrentController.Select(oVCur) 'select visible cursor
oDocWriter.CurrentController.insertTransferable(data) 'Libre internal Ctrl+V
end subYes, it works also for me. But use functional check that oZero is a chart:
rem check that oZero is a chart
if oZero.supportsService("com.sun.star.drawing.OLE2Shape") then
EmbObj=oZero.EmbeddedObject
Component=EmbObj.Component
if NOT Component.supportsService("com.sun.star.chart.ChartDocument") then 'not a chart
exit sub
end if
else 'oZero is not a chart
exit sub
end ifVery helpful! Thanks again.
I am struggling with this now… (maybe to open a different topic?)
About DrawPages:
a) Charts (which store Charts in a sheet) provide a list with object names as string e.g .Charts.getByName(“xx”) or Charts.getByIndex(0)
b) I havent found something similar with DrawPages.
c) The weird thing IS that Draw Objects DO have names e.g .“Object 1” but initially, DrawPages nowhere stores them as an index string. This is accessed after specifying index of Chart eg. getByIndex()
Note this:
’ Doc.getDrawPages.getByIndex(0).getByIndex(0).Title
’ Doc.getDrawPages.getByIndex(0).getByIndex(0).Description
’ oSheet.getDrawPage().getByIndex(0).PersistName
What I would like to do:
I would like to itirate through name of objects (charts) BEFORE Index of Drawpages.
Why? Because it is more “safe” solution.
If Calc document have other draw objects, this may create confusion. I would like all calc charts to be named with specific names.
So, Is there a solution ?
How can i be assured that only named-specific charts will be appended to the Writer document?
I tried to use charts.getByName(“blablabla”) but Doc.CurrentController.select refuse to select it.
Is there any other way to copy paste a chart object as Chart in libre writer from Calc except Drawpages?
Anything else?
I am thinking to store all drawobjects of a page somewhere eg. in an array, and then do a check if their names are same as expected.
But here is the other problem…
Macros cant change the name of the chart in real!
They remain empty!
So I am double stuck now!
The Doc.CurrentController.Select doesn’t select the object from Charts.getBy…, but the objects from the DrawPage. So if you want to get the chart by the name you set in the procedure CreateCalcWithSimpleChart, then you must iterate objects from DrawPage in some loop and test the property PersistName like in my upper example Using Lilo Starbase Macros in LibreCalc, transfer tables and charts to writer document - #10 by KamilLanda
The best way for copy&paste is getTransferable, next way is uno:Copy&uno:Paste. Or absolute hardcore - create new chart in the Writer and set to it all properties from the Calc chart.
Finally, I did it.
I could set Title and Description Title, but I could not set the Name of the Chart (right click on chart → name → empty string).
Note that I try to set the name of chart using
oCharts.addNewByName(sName, oRect, Array(oAddress), True, True)
oSheets = ThisComponent.getSheets()
For i = 0 to 1 step 1
genChartName = "stat_" & i
DrawObject = oSheet.getDrawPage().getByIndex(i)
DrawObject.Title = genChartName
DrawObject.Description = genChartName
next iThe name of the Chart (rightClick&Name or Format/Name) is in the properties in DrawPage, not in the Charts.
Sub getChartNameFromMenu
dim o as object
o=ThisComponent.Sheets(0).DrawPage.getByIndex(0)
msgbox "Name: " & o.Name & chr(13) & "LinkDisplayName: " & o.LinkDisplayName
End Sub
More elements can be name by rightClick&Name, not only a charts.The object structure in the LibreOffice is capacious and sometimes it isn’t easy to discover where the some property is.
About LinkName, it says that it is read-only when:
ThisComponent.getDrawPages.getByIndex(0).getByIndex(0).LinkDisplayName = “aaa”
But
ThisComponent.getDrawPages.getByIndex(0).getByIndex(0).Name = "aaa"
set the name in LO basic, but not in actual chart. I filed a bug in bugzila, as i said.
I worked with Title of Chart (not main title), and Description of Chart.
This did the trick.
Thanks Kamil Landa
ps. Interesting! → Kamil (كامل and كميل)(കാമിൽ) is an Arabic name, sometimes used as an adjective, more usually transliterated as Kamel and Kameel which can be translated as “perfect” or “the Perfect One”. It is also used in Urdu, Persian and Sindhi meaning “complete”. (wiki)
ps. From Occitan *[landa], but ultimately from [Proto-Celtic] (“heath, open field”).