[Solved] Macro Export selection to PDF

[sideshowbond]

Hi guys,

I'm trying to get a macro together that exports selected sheet/area to PDF with a custom filename.
However everything I seem to be doing results in the whole document being exported. I tried recording macros, I tried macros I've found on here but all to the same result.

This is the one I just recorded. When I record the macro it's all fine, as soon as I run it it won't do as expected.

Any help is greatly appreciated

Cheers

Code: Select all

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

sub PDFneu
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 = "Nr"
args1(0).Value = 10

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$1:$L$41"

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

rem ----------------------------------------------------------------------
dim args3(2) as new com.sun.star.beans.PropertyValue
args3(0).Name = "URL"
args3(0).Value = "file:///Users/adb/adb.cloud/Business/Bilanz%20neu/Macro%20test.pdf"
args3(1).Name = "FilterName"
args3(1).Value = "calc_pdf_Export"
args3(2).Name = "FilterData"
args3(2).Value = Array(Array("UseLosslessCompression",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ViewPDFAfterExport",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SignPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Watermark",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Selection",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SignatureLocation",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SignatureReason",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SignatureContactInfo",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SignaturePassword",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SignatureCertificate",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))

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


end sub

[Zizi64]

Code: Select all

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$1:$L$41"

Your recorded macro can export a fixed range only. Do you want export the currently (and manually) selected ranges?
I think, you need use the API functions instead of recorded macros.

[sideshowbond]

Hey Zizi,

thanks! Hm whenever it runs it seems to completely ignore that range though.

Funnily enough one macro I tried was one of yours from a couple years back and in its own file it worked just perfect (and only exported specified range) but when I transferred it to my file it did the same thing as my macro.

I'll have a look into the API side of things but as I'm pretty much an utter noob a hint where to start would be great Some of the stuff I've seen that deals with export macros is admittedly beyond my capabilities.

[Lupp]

As Tibor already stated there is nothing in your recorded macro referring in any way to the current selection. The recorder simply copied the RangeAddress it got during the recording to the assignment in

Code: Select all

args2(0).Value = "$A$1:$L$41"

as a constant text.
A very crude way to replace that assignment with a variable is:

Code: Select all

args2(0).Value = ThisComponent.getCurrentSelection.AbsoluteName

There may be lots of possible erros. One of them will occur if the subsequent parts of the macro cannot handle a multirange address while a multiselection is actually made.
[s=Split(ThisComponent.getCurrentSelection.AbsoluteName,";")(0) will get the first range e.g.]
You need proper security measures and error handling, anyway.
No warranty of any kind. Errors expected!

(Don't rely on recorded macros!)

[sideshowbond]

Thanks Lupp,

if I do replace what you suggested funnily enough it exports all but the page I actually want. Any way to invert that selection?

Thanks
Alex

PS: I don't rely on recorded macros. I only use them to try and understand which direction to go when I need a new one. I'm new to writing macros but try to understand rather than just copy stuff...

[Zizi64]

PS: I don't rely on recorded macros. I only use them to try and understand which direction to go when I need a new one. I'm new to writing macros but try to understand rather than just copy stuff...

The macro recorder always uses the Dispatcher. You do not need use it in your "handmade" macro functions and procedures.
Just call the 'pure' API functions directly.

[sideshowbond]

is there an API or Basic call for PDF export? Everything I've seen in the forum or Andrew's book(s) was going via the dispatcher?

[Zizi64]

You can use the function StoreToURL() together with the export filter (it is not a completed code snippet, it is some example only):

Code: Select all

Dim document as object
Dim FileURL As String
Dim args2(1) as new com.sun.star.beans.PropertyValue
Dim Arg(0) as new com.sun.star.beans.PropertyValue

...

REM Set the URL string of the pdf file:
fileURL = "file:// ... Type the full url of the .pdf file here - or you can get it from the URL of the ODF document "	

...

REM: Select (highlight) a cell range to export
oSheet = ThisComponent.Sheets.getByName("Sheet1")
ThisComponent.CurrentController.setActiveSheet(oSheet)
oRange = oSheet.getCellRangeByName("$A1:F20')
ThisComponent.CurrentController.Select(oRange)	REM  ...or you can get the manually selected cell range

...

REM: Set the export filter:
Arg(0).Name = "Selection"
Arg(0).Value = oRange

args2(0).Name = "FilterName"
args2(0).Value = "calc_pdf_Export"
args2(1).Name = "FilterData"
args2(1).Value = Arg()

ThisComponent.storeToURL(fileURL,args2())

...

[mcmurchy1917]

The way I've tackled this problem in the past is to hide all the sheets that I'm NOT interested before exporting to a PDF. I use the dispatch method for exporting to a PDF - not got round to changing it to the API.

In my case it's always the last sheet I want to export to PDF so this is my code snippet.

Code: Select all

iSheet = 0
For iSheet=0 To oDoc.Sheets.Count() - 1
	oCurrentSheet = oDoc.Sheets.getByIndex(iSheet)
	oController.setActiveSheet(oCurrentSheet)
	oCurrentSheet.isVisible = False
Nex

Of course why it works maybe more luck then judgement or skill.

[sideshowbond]

Hey Tibor,

thanks! Had a few other things I worked on but got back to it like 2 days ago and got it working. Now only to edit the target URL but that's another topic

@mc: as a workaround that would have been to awkward as I only have a few sheets shown, most are hidden and that changes constantly. The code I used was pretty much what Tibor posted only I had to understand that fileURL should include the filename and extension...