How to run a single macro for all xls/xlsx files for libreoffice

Question

Is it possible to run a single macro for all xls/xlsx files and if so how. The macro shown below scales the excel file to fit to single page which is necessary as the number of columns is 19 and is needed to convert it to pdf using lo cli.

Libre office version: 6.0.6

Macro has been recorded with libreoffice and can be seen below:

REM  *****  BASIC  *****

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
vrem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:PageFormatDialog", "", 0, Array())
end sub

Please let me know if any info is needed regarding the tests.

Answer 1

Got the answer from one of the developers at Libreoffice and it works like a charm, so sharing it here. The link to the answer can be found here

Mike's Solution

First: your recorded macro wouldn't work: it doesn't apply changes, it just opens a dialog. Please always test recorded macros :-)

You may use this macro instead:

Sub FitToPage
  Dim document As Object, pageStyles As Object
  document   = ThisComponent
  pageStyles = document.StyleFamilies.getByName("PageStyles")
  For i = 0 To document.Sheets.Count - 1
    Dim sheet As Object, style As Object
    sheet = document.Sheets(i)
    style = pageStyles.getByName(sheet.PageStyle)
    style.ScaleToPagesX = 1
  Next
  On Error Resume Next
  document.storeSelf(Array())
  document.close(true)
End Sub

It operates on the current document, and after setting the scale, it saves (overwrites!) and closes the document.

To use this macro from a command line, you need to save it to some of libraries, e.g. Standard. In my example below, I use Module1 to store it.

You may use this macro on a single document like this:

'path/to/LibreOffice/program/soffice' path/to/excelfile.ext macro:///Standard.Module1.FitToPage

To use it on multiple documents, you need to make this in a loop (mentioning multiple filenames as arguments to a single soffice invocation, like with shell globbing on Linux using *, will not work - actually, it will only run the macro for the last document, keeping the others open and unmodified). A loop for Windows could be like this:

for %f in (*.xls) do start /wait "" "C:\Program Files\LibreOffice\program\soffice.exe" "%f" macro:///Standard.Module1.FitToPage