LibreOffice to XLS script

[capzilla]

Hello!
I’m trying to create a VB script to open a libre office calc template, write some data and save as xlsx .
Do any of you has done it before? Thanks

[Wanderer]

@capzilla

trying to create a VB script

If you really wish to use VB-Script or Visual Basic .NET you may need to start LibreOffice and send commands via an receiving port, as discussed in the following thread for (external) python programs.
https://forum.openoffice.org/en/forum/viewtopic.php?t=53657

If you wish to create a macro inside LibreOffice, you may find out LO has BASIC, but VisualBasic is something special from M$. The API in Open- or LibreOffice is quite different from MS-Office. Follow the link provided by @JohnSUN for more information.


To port some scripts it should be mentioned there is some “VBASupport” as shown here:

Need guidance on Delete Shift Left/Up macros

Try again with: option VBASupport 1 Sub DeleteShiftUp() Selection.Delete Shift:=xlUp End Sub Sub DeleteShiftLeft() Selection.Delete Shift:=xlToLeft End Sub

[JohnSUN]

Welcome!

@capzilla

Do any of you has done it before?

Yes, this has been done for quite a long time.
Please download this file - https://www.pitonyak.org/AndrewMacro.odt.
See chapters

• 5.8.4. Create a new document from a template

• 6.3. Set cell value, format, string, or formula

• 5.17. Saving And Exporting A Document

@capzilla

I’m trying to create a VB script

I’m not sure I understand which programming language you’re talking about now. Do you really mean VisualBasic?

[mikekaganski]

@JohnSUN

Do you really mean VisualBasic?

Or even VBScript?

[sokol92]

@capzilla, here is VBScript (VBS) according to the technical specifications.
Replace inFile and outFile with your values.

VBS:

Option Explicit
Sub Main()
  Dim oSM, StarDesktop, oDoc, args, retval
  Dim inFile, outFile
  
  inFile = "file:///C:/Temp/Example.ots"   ' C:\temp\Example.ots
  outFile = "file:///C:/temp/Example.xlsx" ' C:\temp\Example.xlsx
  
  Set oSM = CreateObject("com.sun.star.ServiceManager")
  Set StarDesktop = oSM.createInstance("com.sun.star.frame.Desktop")

  ' 1. "Open a libre office calc template"
  Set oDoc = StarDesktop.loadComponentFromURL(inFile, "_blank", 0, Array())

  ' 2. "Write some data"
  oDoc.getSheets.getCellByPosition(0, 0, 0).SetValue 1  ' Put 1 to cell A1 of 1st sheet
  
  ' 3. "Save as xlsx"
  Redim args(0)
  Set args(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
  args(0).Name = "FilterName"
  args(0).Value = "Calc MS Excel 2007 XML"
  oDoc.storeToUrl outFile, args
  
  oDoc.Close True
  
End Sub

Main  ' run script

See also this topic.