We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to run a single macro for all excel files

asked 2018-10-03 10:52:05 +0200

BinoyCherian gravatar image

updated 2020-09-03 10:37:03 +0200

Alex Kemp gravatar image

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
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-10-03 12:47:47 +0200

updated 2018-10-03 14:10:09 +0200

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
  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
edit flag offensive delete link more


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

BinoyCherian gravatar imageBinoyCherian ( 2018-10-03 14:37:07 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-10-03 14:43:13 +0200 )edit

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

BinoyCherian gravatar imageBinoyCherian ( 2018-10-03 15:28:46 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2018-10-04 09:41:32 +0200 )edit

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

BinoyCherian gravatar imageBinoyCherian ( 2018-10-04 09:48:39 +0200 )edit
Mike Kaganski gravatar imageMike Kaganski ( 2020-07-07 10:53:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-03 10:52:05 +0200

Seen: 734 times

Last updated: Oct 03 '18