Ask Your Question

How to print automatic in a macro

asked 2013-09-30 15:27:07 +0200

Yann gravatar image

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.


edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2015-01-14 10:41:41 +0200

dgbutterworth gravatar image

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("")

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
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
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,,,Array("SignatureCertificate",0,,

dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args2())
    enter code hersub printRange
rem ----------------------------------------------------------------------
rem define ...
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2013-09-30 15:27:07 +0200

Seen: 3,233 times

Last updated: Jan 14 '15