How to run a single macro for all excel files

[BinoyCherian]

Hi All,

Is it possible to run a single macro for all excel files and if so how. The macro scales the excel file to fit to single page. After which I plan to convert it to pdf using lo cli.

Currently the macro is inside the “My Macros and Dialog - > Standard → Module1 → Main”. The conversion to pdf through lo cli works fine only for one document through which I saved the macro. I am looking for something that helps me to run the same macro for all files via 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

[mikekaganski]

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

[BinoyCherian]

@Mike2, It works like magic. Thank you. A quick question, i tried adding --headless to your potion ‘path/to/LibreOffice/program/soffice’
–headless path/to/excelfile.ext macro:///Standard.Module1.FitToPage. But it didn’t work. headless is not possible or can I do something else?

[mikekaganski]

Well - in reality, it does work (in a sense) - you might notice that there’s no splash window when LO starts in that case. You can check it further: just doing soffice --headless file.ext would open the file in a visible window, but wouldn’t allow you to open dialogs (like page format). Actually, I don’t know if that should be treated as a bug, since headless soffice is not expected to be run with command line for just opening (as opposed to conversion) - LO has no way to know what macro does.

[BinoyCherian]

@Mike2, Yes, i noticed that there was no splash but I miswrote my question above, I am looking for something that could hide the scaling activity. I did test --invincible mode, that didn’t work out for me.

[mikekaganski]

Possibly you could change the macro to take the filename as a parameter, and then open the file itself, operate on it, and close… then move the filename from soffice param to macro param… but that’s up to you - unfortunately I can’t invest into this, sorry.

[BinoyCherian]

@Mike2, I will try your suggestion and test.
Thank you

[mikekaganski]

Ref: tdf#104441