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

Creating a macro - Writing a Script - Using the API

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

Postby MacroAmateur » Mon Mar 06, 2017 4:35 pm

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:

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

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)

So here's the code that accomplishes this:

Code: Select all   Expand viewCollapse view
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)

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
Posts: 1
Joined: Mon Mar 06, 2017 3:36 pm

Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 8 guests