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
-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.