Using Lilo Starbase Macros in LibreCalc, transfer tables and charts to writer document

estatistics · August 30, 2021, 11:42am

Using Lilo Starbase Macros in LibreCalc, transfer tables and charts to writer document.

Can you offer an example how to transfer named charts and named tables from librecalc to libre writer document using Macros lilo starbase?

estatistics · August 30, 2021, 12:48pm

I found this VBA code for microsoft excel to transfer charts into word. How can I modify this code in order to work in libre calc?

Public Sub Copy_Charts_From_2_Workbooks_To_Word()

    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    Dim WordRange As Word.Range
    Dim WordDocumentFullName As String
    Dim wb1 As Workbook, wb2 As Workbook
    Dim chObject As ChartObject
    Dim i As Long, c1 As Long, c2 As Long
    
    WordDocumentFullName = "C:\folder\path\Word document.docx"  'CHANGE THIS
    
    Set wb1 = Workbooks.Open("C:\folder\path\Workbook1.xlsx", ReadOnly:=True)  'CHANGE THIS
    Set wb2 = Workbooks.Open("C:\folder\path\Workbook2.xlsx", ReadOnly:=True)  'CHANGE THIS
    
    'Get existing instance of Word or create a new one
    
    On Error Resume Next
    Set WordApp = GetObject(Class:="Word.Application")
    Err.Clear
    If WordApp Is Nothing Then Set WordApp = CreateObject(Class:="Word.Application")
    If Err.Number = 429 Then
        MsgBox "Microsoft Word is not installed.", vbCritical
        Exit Sub
    End If
    On Error GoTo 0
    WordApp.Visible = True
    
    'Open Word document
    
    Set WordDoc = WordApp.Documents.Open(FileName:=WordDocumentFullName, ReadOnly:=False)
    Set WordRange = WordDoc.Range
    
    c1 = 0
    c2 = 0
    
    For i = 1 To wb1.Worksheets("Sheet1").ChartObjects.Count + wb2.Worksheets("Sheet1").ChartObjects.Count
    
        c1 = c1 + 1
        
        If c1 <= wb1.Worksheets("Sheet1").ChartObjects.Count Then
        
            Set chObject = wb1.Worksheets("Sheet1").ChartObjects(c1)
            chObject.CopyPicture xlScreen, xlPicture
            
            'Paste clipboard to Word document.
            'Trap occasional Run-time error 4198: Method 'PasteSpecial' of object 'Range' failed

            On Error Resume Next
            Do
                Err.Clear
                WordRange.PasteSpecial DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
                DoEvents
                If Err.Number <> 0 Then Application.Wait DateAdd("s", 1, Now)
            Loop While Err.Number <> 0
            On Error GoTo 0
            
            WordRange.SetRange WordRange.End, WordRange.End
            WordRange.InsertParagraphAfter
            WordRange.Collapse wdCollapseEnd
        
        End If
        
        c2 = c2 + 1
        
        If c2 <= wb2.Worksheets("Sheet1").ChartObjects.Count Then
        
            Set chObject = wb2.Worksheets("Sheet1").ChartObjects(c1)
            chObject.CopyPicture xlScreen, xlPicture
            
            'Paste clipboard to Word document.
            'Trap occasional Run-time error 4198: Method 'PasteSpecial' of object 'Range' failed

            On Error Resume Next
            Do
                Err.Clear
                WordRange.PasteSpecial DataType:=wdPasteMetafilePicture, Placement:=wdInLine, DisplayAsIcon:=False
                DoEvents
                If Err.Number <> 0 Then Application.Wait DateAdd("s", 1, Now)
            Loop While Err.Number <> 0
            On Error GoTo 0
            
            WordRange.SetRange WordRange.End, WordRange.End
            WordRange.InsertParagraphAfter
            WordRange.Collapse wdCollapseEnd
        
        End If
        
    Next
    
    MsgBox "Done"

mikekaganski · August 30, 2021, 2:17pm

Using Lilo Starbase

Something from star trek future

estatistics · August 30, 2021, 2:27pm

I have found a way to get the Dir path, to create a Writer Document, to have open write access on that Document.

I have found a way to copy chart from Calc sheet to Calc Sheet.
I would like to find a method to copy this chart into opened word.
see my early code.

’ XrayTool that helps to investigate VBA objects.
’ Found here: Bernard Marcelly Web site
’ Download word document and click the button inside to install
’ it

Sub LoadingLibraries
 BasicLibraries.LoadLibrary("XrayTool")
End Sub


Sub GetSourceDirUsingOpenDocument
  Dim oDoc  'The document on which to work.
  Dim s$    'Temporary string variable.
  Dim sOut() 'Output text initially written to the CSV file.
  Dim oParms()
  
  oDoc      = useDoc
  If IsEmpty(oDoc) OR IsNull(oDoc) Then
    oDoc   = ThisComponent
  End If
  
   If oDoc.hasLocation() Then
    REM This is assumed to be in URL notation!
    s = DirectoryNameoutofPath(oDoc.getLocation(), "/")
    GetSourceCodeDir() = s & "/"
    'Print GetSourceCodeDir()
  Else
    Print "Warning, current document has no location"
  End If
  
  
  oBaseContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
  sFileName = "results"
  sURLInitial = GetSourceCodeDir() & sFileName & ".odt"
  
  n = FreeFile() 'Next free file number
  Open sURLInitial For Output Access Read Write As #n
  For i = 0 To UBound(sOut())
    Print #n, sOut(i)
  Next
  Close #n
  
End Sub 


Sub CopyChartstoWriter

' Definitions of variables
Dim oSheet    'Sheet containing the chart
Dim oCharts   'Charts in the sheet
Dim oChart    'Created chart
Dim sName$    'Chart name
Dim oChartDoc 'Embedded chart object
Dim oDiagram  'Inserted diagram (data).
Dim oCalcDoc  

sName = "statChart_1"

oSheets     = ThisComponent.getSheets()
oSheet      = oSheets.getbyName( "Graphs_stat" ) 
oCharts     = oSheet.getCharts()
oChart      = oCharts.getByName(sName)  
' oChart.setRanges(Array(oAddress))
oChartDoc   = oChart.getEmbeddedObject()


oDrawPage = oSheet.getDrawPage()
oZero = oDrawPage.getByIndex(1)

'The next three lines could be used to check that oZero is a chart
EmbObj = oZero.EmbeddedObject
Component = EmbObj.Component
print Component.supportsService("com.sun.star.chart.ChartDocument")

'Select the chart
ThisComponent.CurrentController.select(oZero)

'Get ready to do dispatcher calls
frame   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

'Copy the selected chart
dispatcher.executeDispatch(frame, ".uno:Copy", "", 0, Array())


'Go to the cell B3
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$B$3"
dispatcher.executeDispatch(frame, ".uno:GoToCell", "", 0, args3())

'Paste the chart
dispatcher.executeDispatch(frame, ".uno:Paste", "", 0, Array())


end sub

anon73440385 · August 30, 2021, 5:05pm

FTR: tdf#144191

karolus · August 31, 2021, 12:31pm

Isnt it unkind to post the same topic in several forums at the same time?
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=106011#p515159

estatistics · August 31, 2021, 1:15pm

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.

Villeroy · August 30, 2021, 1:42pm

Copy and paste will do.

estatistics · August 30, 2021, 2:21pm

I am working on this. I have found some examples.
pitonyak

openoffice forum

Villeroy · August 31, 2021, 10:55am

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.

KamilLanda · August 30, 2021, 2:52pm

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 Function

estatistics · August 30, 2021, 3:39pm

Problem…

The name of Chart is not passed as intented and this creates to me problems
Xray tool reassure it is passed as name into chart
By trying to rename the empty name of chart manually, it prohibits you to add “again” the same name even if its empty.

Steps to reproduce Bug"

Name a sheet as Graph_stat
put these data in cells D17 to F28

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 sub

estatistics · August 30, 2021, 3:57pm

Your 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 Sub

KamilLanda · August 30, 2021, 4:56pm

The 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)

estatistics · August 30, 2021, 5:18pm

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?

KamilLanda · August 30, 2021, 6:27pm

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.

estatistics · August 30, 2021, 6:31pm

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.

estatistics · August 30, 2021, 6:50pm

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 sub

KamilLanda · August 30, 2021, 9:55pm

Yes, 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 if

estatistics · August 30, 2021, 10:26pm

Very helpful! Thanks again.

I am struggling with this now… (maybe to open a different topic?)

About DrawPages:

This is done by getByIndex(0) NOT by name of the object
As I understood well, Draw objects are accessed after you call getDrawPages.getByIndex(0) and not before.
So Drawobjects can be called by any (unique) name inside a Sheet?
For Example:

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?

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