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!
