Ask Your Question
1

How to run a single macro for all excel files

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

BinoyCherian gravatar image

updated 2018-10-03 11:29:17 +0100

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
2

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

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

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

Comments

@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 +0100 )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 +0100 )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 +0100 )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 +0100 )edit

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

BinoyCherian gravatar imageBinoyCherian ( 2018-10-04 09:48:39 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 70 times

Last updated: Oct 03 '18