I believe that you can do this quite easily, if you “print” to a PDF file. I created a Macro that works like a charm … using only Macro Recording to create it. (For some reason, I do see what you mean about printing a “selection” to a normal printer - it does stop and wait for an “OK” before completing the print. If printing (or exporting) to a PDF, the job completes without stop.)
The final macro is listed below (with some shortening of the vast array associated with PDF option).
These parameters were set in the process of recording the Macro. When I first recorded the macro, the selected cells were identified by just that - I selected them by hand and then started recording the actions for doing a print (or, export - as referred to by LO) to PDF, as follows:
-
Selected a region to print,
-
Start Macro recording,
-
On Main Menu: edit / export as pdf …
-
In PDF Export Dialogue: tab-general / Range: Selection
-
… make any other option selections you want in the PDF Dialogue,
-
Click export …
-
In Save Export File Dialogue: enter file name and location of your choice.
-
Stopped macro.
The resulting Macro is provided below. This could certainly be attached to a button on the Calc sheet, and clicked to make it run.
Note: Once recorded, this Macro will print the same originally “selected” region every time (and save to the same file name … possibly overwriting prior versions). You don’t say anything out the selection process, and it seems your existing macro worked as need for that. I assume that your can easily combine the existing Macro with the printing elements provided here.
I hope this helps. Here’s the code (which really needs to be generated by being recorded on the users system, using the users prefered PDF settings):
sub printRange
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 ----------------------------------------------------------------------
rem this defines the range to be printed. If the desired range is not a contiguous block, something
rem more complicated would be needed
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$2:$D$6"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
rem define the parameters for the export/print to PDF – including filename for output.
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "URL"
args2(0).Value = "file:///home/userID/Documents/pdfPrint_selectedCellsCalcSheet_byMacro.pdf"
args2(1).Name = "FilterName"
args2(1).Value = "calc_pdf_Export"
args2(2).Name = "FilterData"
args2(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),,Array("SignatureCertificate",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))
dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args2())
enter code hersub printRange
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 ----------------------------------------------------------------------
rem this defines the range to be printed. If the desired range is not a contiguous block, something
rem more complicated would be needed
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$B$2:$D$6"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
rem define the parameters for the export/print to PDF – including filename for output.
dim args2(2) as new com.sun.star.beans.PropertyValue
args2(0).Name = "URL"
args2(0).Value = "file:///home/userID/Documents/pdfPrint_selectedCellsCalcSheet_byMacro.pdf"
args2(1).Name = "FilterName"
args2(1).Value = "calc_pdf_Export"
args2(2).Name = "FilterData"
args2(2).Value Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),...[a really long array definition covering every PDF Export option available as listed on six or so tabs] ... Array("SignatureCertificate",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE))
dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args2())
The recorded Macro runs without stopping all the way to a fully “print” of the PDF file. For me, PDF is my default printer … who wants paper?