[SOLVED] Recorded macro exports all pages of PDF

[bhrgunatha]

Version: 7.6.2.1 (X86_64) / LibreOffice Community
Build ID: 60(Build:1)
CPU threads: 4; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
7.6.2-3
Calc: threaded

---

If I manually select a range of cells in one sheet from a spreadsheet with multiple sheets, then File | Export as PDF… | Under Range Check the Selection/Selected Sheeet(s) then click Export. The selection is printed to a PDF as a single page from the selection I chose.

However if I record a macro following exactly the same steps, the exported PDF includes all the sheets with many pages.
Here’s the recorded macro with all properties not related to Selection elided from the array generated from the Export To PDF dialog.

sub ExportPDF
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "glycaemia (glycaemia)"

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DefinePrintArea", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "URL"
args3(0).Value = "file:///home/bhrgunatha/selection.pdf"
args3(1).Name = "FilterName"
args3(1).Value = "calc_pdf_Export"
args3(2).Name = "FilterData"
args3(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE)[elided many properties...,]Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),[...],Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),[...]Array("Selection",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE)[...])

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args3())


end sub

Can I modify the macro so it doesn’t export all sheets as many pages and just the selection as a single page?

[Zizi64]

The Args(3) not contain any information about the area what you want to export into PDF format.
The variable “document” contain the full of active sheet or the full of active spreadsheet document, but no the seletion only.
.
If you want to use the Export the Selection" option, then you must make a selection by the macro.
.
The macro recorder can not record all of the user’s activity. It is better to WRITE your macros (based on the API functions) instead of recording them.

[Zizi64]

If you want to use a third party virtual PDF Printer (installed on your operating system), then you can use the properties of the Print function (like the PrintRange). In this case you must use a Print command instead of the Export command.
.
If you want use the built-in Export feature of the LO, then you must use the properties of the Export feature (like the “Export the Selection”. In this case you must SELECT / highlight/ one or more cell ranges by your macro code before you try to export it.

[schiavinatto]

Hi @bhrgunatha , see the macros in this example file.

Salvar PDF ver PDF_2.ods (73.9 KB)

[Zizi64]

Here is my macro version based on API functions:

REM  *****  BASIC  *****

option explicit

Sub MyAPI_ExportSelectionToPdf

 Dim oDoc as object
 Dim oController as object
 Dim oRange as object
 Dim Arg1(0) as new com.sun.star.beans.PropertyValue
 Dim Args2(1) as new com.sun.star.beans.PropertyValue
 Dim PDF_URL as string 

	oDoc = ThisComponent
	oController = oDoc.CurrentController
	oRange = oDoc.NamedRanges.getByName("glycaemia").ReferredCells
	oController.Select(oRange)
	
	Arg1(0).Name = "Selection"
	Arg1(0).Value = oRange

	Args2(0).Name = "FilterName"
	Args2(0).Value = "calc_pdf_Export"
	Args2(1).Name = "FilterData"
	Args2(1).Value = Arg1()		

	PDF_URL = ConvertToURL("file:///home/bhrgunatha/selection.pdf")
	
	oDoc.storeToURL(Pdf_URL, args2())
End Sub

[bhrgunatha]

@Zizi64

After many failures myself, this works exactly how I asked, thank you! I appreciate it very much.

I find it incredibly hard to follow the API since they are a mixture of UNO and Basic/ Python,… and it’s difficult to know what methods, or Property names/values to look for and use (especially UNO) - e.g. not knowing to add .ReferredCells to the named range.

[bhrgunatha]

@schiavinatto Thank you for sharing, it’s good to be able to reference this much code for inspiration.

[Zizi64]

@bhrgunatha

and it’s difficult to know what methods, or Property names/values to look for and use (especially UNO)

I strongly suggest you to install one (or both) of the excellent Object inspection tools: MRI or XrayTool. Then you will able to list the existing properties of the programming objects.