[Solved] Macro To Export Print Range of Active Sheet To PDF

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
MacroAmateur
Posts: 1
Joined: Mon Mar 06, 2017 3:36 pm

[Solved] Macro To Export Print Range of Active Sheet To PDF

Post by MacroAmateur »

I've gained a tremendous amount of information by trolling this board. Thank you to all of you regular posters and OO! Your work is much appreciated, even if it mostly goes unsaid! :bravo:

So I figured I'd create an account and hopefully give back in a small way. I finally figured out the code for a problem of mine (subject line) after pulling out my hair out on it for a few days. I couldn't find the exact solution anywhere online. So here's the goal and solution in case anyone else can benefit from it:

GOAL:
What I wanted was a Macro to provide what the following steps in Calc provide:
File
Export As PDF...
Choose 'Selection' under 'Range' on the General Tab
Export

What this does, if no range is currently selected, is it will export the currently Defined Print Range of only the current Active Sheet to a PDF, prompting the user for the directory and filename to save the PDF as, and prompt the user if about to overwrite an existing PDF.

There are examples out there of doing this, but only if the Macro names the file and then saves it in the background with a predetermined name. Great code but doesn't give the normal user any control over where to save the file, or what to name the file on a per instance basis. Many other examples out there incorrectly export the entire Spreadsheet (which is what I kept running into again and again if not allowing the Macro to write and name the file)

You could also use a simple Macro to "Print To Pdf", which runs much faster, but Printing To PDF doesn't preserve the hidden hyperlinks like "Export To PDF" does. And though you could simply use the outlined manual steps above each Export instead of a Macro, in my case I wanted to a) insure the user doesn't skip the "Selection" option (which would instead create a PDF for the entire Spreadsheet), or, b) I also needed to use an IF/ELSE statement in my macro for form validation before allowing the PDF to be exported (validation stuff removed from code below for clarity)

SOLUTION:
So here's the code that accomplishes this:

Code: Select all

Sub ExportActiveSheetPrintRangeToPDF

 'Get Active Sheet
	dim document   as object
	dim dispatcher as object
	dim oSheet as object

	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	oSheet = thiscomponent.getcurrentcontroller.activesheet
	
 'Get Print Range - if no print range has been defined for the Active Sheet, then it will export the entire Active Sheet
	dim aFilterData(0) as new com.sun.star.beans.PropertyValue
	aFilterData(0).Name = "Selection"
	aFilterData(0).Value = oSheet

 'Export to PDF
	dim args1(1) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "FilterName"
	args1(0).Value = "calc_pdf_Export"
	args1(1).Name = "FilterData"
	args1(1).Value = aFilterData()

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

'File Done (added this next line so that user knows when file is complete)
	Beep

End Sub
-The final "Beep" line can be removed if not needed. I wanted it because it takes quite a bit longer to Export than Print To PDF, and it's difficult to tell when the Export is complete.
-This Macro differs in one way from the manual Export steps outlined above; it will Export the Defined Print Range even if the user manually selects another range. If no Print Range has been Defined for the Active Sheet, then it will Export the entire Active Sheet.
-Finally, there are many optional settings for a PDF Export. This Macro uses the last used settings from the last manual Export. If needed, the settings can be configured through the Macro by adding another args1 with an Array value as seen HERE (near bottom of 1st Macro, look for line that begins "Array(Array")

Simple code for a simple procedure but, wow, I had a hard time figuring this task out.
OpenOffice 4.1.3 on Windows 10
Post Reply