How to print automatic in a macro

Hi,
I have a worksheet in Calc that i made in Excel with buttons who trigger a macro to print selected cells.
In Excel it works fine. When i push the button my printer starts to print.
Now i recorded the same macro in Calc and when i push the button i get a print dialog and i have to click “OK” to print.
How can i make the macro click the “OK” button so i can print directly like in Excel.
I am not a programmer so i record my macro’s. I allready looked in VBA to find a solution but i don’t understand much of programming in VBA.

Can someone help me with this problem and do it in human language because i’m a total nooby in Calc.
Thanks.

Yann

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

  • This particular macro prints the “selected” cell region: $B$2:$D$6

  • Printed output - PDF file - is saved at:
    file:///home/userID/Documents/pdfPrint_selectedCellsCalcSheet_byMacro.pdf

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?