How to run a single macro for all excel files

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("")

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

end sub

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

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
  On Error Resume Next
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

@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?

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.

@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.

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.

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

Ref: tdf#104441