Export or Save As PDF A Specific Range in LibreOffice Calc Sheets using Macro

Exporting a LO sheet’s content to PDF is often necessary because of wide use of PDF files for distributions, reporting etc. In the earlier tutorial I have shown how to export an entire sheet’s content to a pdf file. In this tutorial I will show how to export a specific range (e.g. A1:B2 etc) as a content of a pdf file.

How to export an entire sheet to pdf tutorial can be found here.

Objective

I have a source data like this (see below). The pdf should contain the color and values from A1 to I3 range and the pdf file name should contain the current Date, the Sheet Name (i.e. in this example ‘mySheet’) and a fixed value from a cell (i.e. in this example B5).

Details of the Macro

There are two parts of this macro. First to dynamically prepare the pdf file name based on certain value. The pdf file name consists of the sheet name, a value from B5 cell and date. Below piece of code does this part:

   dim fileName
   fileName = ThisComponent.Sheets(0).Name & _
   "_ratie_" & ThisComponent.Sheets(0).getCellRangebyName("B5").String & _
   "_" & Replace(Date,"/","-") & ".pdf"

[update#1]
One of my reader asked about on how to get the Active sheet name Or the name of current Calc sheet is selected. To get the current active selected sheet’s name, use below::

fileName = ThisComponent.getCurrentController.getActiveSheet.Name & _

Now, create a Frame object and dispatcher object to Uno service com.sun.star.frame.DispatchHelper which would help to export the pdf via function.

Create an empty file as well in any path of your own and close the file.

   ' change the path below as per your needs
   path ="file:///home/arindam/" & fileName
   Open path For Append As #1
   Close #1

Now, its time to put the content inside the pdf. To do that, we need to use Filters. Filters are the types and value pairs which can be put in an array and can be passed to executeDispatch function as an argument. The types are pre-defined and when passed they will be processed what they are supposed to do. In this example, we need to pass the filename and the Range of LO Sheet which needed to be printed only in the pdf, nothing else form the active sheet. The in-built Filter property FilterData to be used here.

   document = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
   oSheet =  ThisComponent.CurrentController.getActiveSheet()
   oCellRange = oSheet.getCellRangeByName("A1:I3")
   
   dim aFilterData(0) as new com.sun.star.beans.PropertyValue
   aFilterData(0).Name = "Selection"
   aFilterData(0).Value = oCellRange
   dim args1(1) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "URL"
   args1(0).Value = "file:///home/arindam/" & fileName  ' change the path below as per your needs
   
   args1(1).Name = "FilterData"
   args1(1).Value = aFilterData()

Now pass this argument to the executeDispatch function as an argument.

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

Running the Macro

Here is the pdf file that is created with the range contents for this tutorial:

Complete Macro

Sub ExportRangeToPDF()
   dim document as object
   dim dispatcher as object
   dim fileName
   fileName = ThisComponent.Sheets(0).Name & _
   					"_ratie_" &  ThisComponent.Sheets(0).getCellRangebyName("B5").String & _
   					 "_" & Replace(Date,"/","-") & ".pdf"
   document = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
   oSheet =  ThisComponent.CurrentController.getActiveSheet()
   oCellRange = oSheet.getCellRangeByName("A1:I3")
   
   dim aFilterData(0) as new com.sun.star.beans.PropertyValue
   aFilterData(0).Name = "Selection"
   aFilterData(0).Value = oCellRange
 
   ' change the path below as per your needs
   path ="file:///home/arindam/" & fileName
   Open path For Append As #1
   Close #1
 
   dim args1(1) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "URL"
   args1(0).Value = "file:///home/arindam/" & fileName  ' change the path below as per your needs
   
   args1(1).Name = "FilterData"
   args1(1).Value = aFilterData()
 
   dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
End Sub

If you face any problem running this, drop a comment using comment box below.

We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!