Nextcloud/Collabora Online - Macro to export a PDF

User Support
1

I have been trying to create a macro to export a sheet in Calc as a PDF. The macro will run (there are other things in the Macro that I can verify), but no file is created at all. It doesn’t matter to me if it writes back to Nextcloud, or if it downloads via the Web Browser.

Can anyone help me with a Macro to do this? Much appreciated!

2

hi @muzicman0

in Collabora Online you cannot freely export files to arbitrary paths from a macro. That is why your macro runs but no PDF appears. This is a known limitation of the Online security sandbox.

  • Your macro runs, but file export is blocked by design
  • Use .uno:ExportDirectToPDF for browser download
  • Avoid filesystem paths

Thanks
Darshan

1 Like
3

Thanks! I am decent at VBA, but have absolutely ZERO experience with LibreOffice BASIC. Is there any example online of making this work that I can reference?

4

Best starting points:

1.LibreOffice Wiki – Export Calc to PDF (Basic macro)
Official example, very close to what you want:
https://wiki.documentfoundation.org/Macros/Calc/016

  1. DebugPoint tutorial – Export Calc sheet to PDF using macro
    Clear explanation, easier to follow than the wiki:
    https://www.debugpoint.com/export-or-save-as-pdf-in-libreoffice-calc-sheets-using-macro/

  2. LibreOffice Basic intro (for VBA users)
    Short overview of syntax and concepts:
    https://wiki.documentfoundation.org/Macros/Basic

Tip for Collabora Online:
Desktop examples use file paths (file:///…). In Collabora Online, focus on examples using:

  • ExportDirectToPDF
  • private:stream

Those work in the browser sandbox.

5

Again, many thanks for the help. Here is what I am trying, which gives no errors, but also doesn’t download a file.

Sub ExportToPDF()
  dim document as object
  dim dispatcher as object

  document = ThisComponent.CurrentController.Frame
  dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
  dim args1(0) as new com.sun.star.beans.PropertyValue
  args1(0).Name = “URL”
  args1(0).Value = “private:stream”
  dispatcher.executeDispatch(document, “.uno:ExportDirectToPDF”, “”, 0, args1())
End Sub

This is based on the code from the DebugPoint Tutorial.

If it matters (and I assume this is the only way to do this), I am creating the Macro in the Windows desktop version of Calc and saving it back to my Nextcloud instance.

6

This code works mostly, but I was hoping to avoid the popup dialog, but I can live with it if that is the only option.

Sub ExportToPDFDirect()
  Dim oDoc As Object
  Dim oDispatcher As Object
  Dim oFrame As Object
  Dim args(0) As New com.sun.star.beans.PropertyValue

  'Get the current document and frame 
  oDoc = ThisComponent
  oFrame = oDoc.CurrentController.Frame

  'Create dispatcher
  oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

  'Set up the argument for PDF export
  'Using FilterName to specify PDF export
  args(0).Name = "FilterName"
  args(0).Value = "calc_pdf_Export"

  'Execute the export to PDF command
  oDispatcher.executeDispatch(oFrame, ".uno:ExportToPDF", "", 0, args())

End Sub

EDIT: Is there ANY way to name the exported file? I have tried pretty much everything AI had to offer with zero luck. It seems the PDF export will be named according to the .ods file name in Nextcloud. I would love to name the exported file something unique based on Cell data.

7

Hi @muzicman0

greate to see the progress nice well done :tada:

Is there ANY way to name the exported file? I have tried pretty much everything AI had to offer with zero luck. It seems the PDF export will be named according to the .ods file name in Nextcloud. I would love to name the exported file something unique based on Cell data.

No. With .uno:ExportToPDF in Collabora Online, you cannot avoid the dialog and you cannot set a custom output filename.

Why:

  • The .uno:ExportToPDF dispatch is UI-driven
  • In Collabora Online the actual save is controlled by the host (Nextcloud via WOPI)
  • Macros are sandboxed and cannot choose the target path or filename
  • The PDF name is always derived from the document name (.ods → .pdf)