Export or Save As PDF in LibreOffice Calc Sheets using Macro

Advertisement

This tutorial will show how to save a LibreOffice Calc sheet directly as a pdf file using basic macro. In many automation task this simple feature frequently required.


Writing the Macro


Lets call the function as “exportToPDF()” that we will hold the entire process.
First define two objects which would hold the Calc document and a dispatch to execute actions.

dim document as object
dim dispatcher as object

document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Now create an empty file using basic’s “Open” function. Note that the path is of the file system path. Thus we have prefixed the path with “file://”. Once the file is created, close the file.

path ="file:///home/arindam/sheet1.pdf"
Open path For Append As #1
Close #1

Now, create an array of size 1 to hold the property value for exporting as pdf. Then fill the property in the array of name “URL” with the path of the file we just created.

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///home/arindam/sheet1.pdf"

Finally call the executeDispatch method to put the sheet1 content to the pdf and save.

dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())

This completes the code.

The complete function is present below.

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

   document = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   ' change the path below as per your needs
   path ="file:///home/arindam/sheet1.pdf"
   Open path For Append As #1
   Close #1

   dim args1(0) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "URL"
   args1(0).Value = "file:///home/arindam/sheet1.pdf"  ' change the path below as per your needs

   dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
End Sub


Running the save as pdf function


Open LibreOffice calc and Create a macro and put the above function. Write anything in the sheet1. For this example, I have written something like this –

LibreOffice CALC Save As PDF Macro - Sheet

LibreOffice CALC Save As PDF Macro – Sheet

Run the macro

You can see the entire sheet1 content is saved in the specified directory mentioned in above macro. Here is the content of the pdf that is created using this macro.

LibreOffice Save As PDF Macro

LibreOffice Save As PDF Macro

Drop a comment using below comment box, if you faced any problem using this.

You may also like...

  • Adriel Shawn

    HI. im a newbie in libre basic calc. i have a program that will copy the highlighted row to another sheet. but my problem is how can i determine if the cell have data. because whats heppening here is my program is just replacing the 2nd data that i transfer.

    i would like to know the function for detecting if the cell have data if so go to next line. thanks in advance.

    #HelpMeSensei!

  • gergn

    In Excel I use the following (sorry, I am Dutch):

    Rem Attribute VBA_ModuleType=VBAModule
    Option VBASupport 1
    Sub PDF()

    ‘ PDF Macro


    Dim Periode As String

    Periode = Range(“B5”).Value
    ChDir (ThisWorkbook.Path)

    ‘ de pdf wordt in dezelfde map opgeslagen als het spreadsheet zelf
    ‘ in de naam van de pdf komt de naam van de cliënt, de periode en de afdrukdatum
    ActiveSheet.Range(“A1:I41”).ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:=ActiveSheet.Name & “ratie_” & Periode & “_” & Date & “.pdf”, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    End Sub

    You see I do not export the whole sheet, only the range A1:i41.
    The filename is the active sheet name, followed by some text, the period = month name (that is stored in B5) and the date the pdf is made. It is stored in the same folder as the xlam-sheet itself.
    A relative uses this to generate monthly declarations for his clients. He has two sheet per client. The first one contains all his entries for this client in the running year. The second sheets loads the data for the current month.

    How can I do this in LO?

    gergn

    • I created a post on how to export selection and range to a pdf with dynamic fine name. Read here. I think it will solve your problem. Everything is doable in LibreOffice.

      http://www.debugpoint.com/2015/08/export-or-save-as-pdf-a-specific-range-in-libreoffice-calc-sheets-using-macro/

      • gergn

        Thanks for your help. But your code does not work on the Active Sheet, but looks at the first sheet. In my case it is a blank sheet, just like the last sheet.

        Your code contains … fileName = ThisComponent.Sheets(0).Name & …
        My Excel code contains Filename:=ActiveSheet.Name & …

        • That’s even more easy. Use this to get the current active/selected sheet name::

          fileName = ThisComponent.getCurrentController.getActiveSheet.Name & ….

Translate »