Setting and printing Cell ranges in Calc (Basic?)

[OldJack]

I’ve figured out how to set the range of cells I want to print and I have the syntax to print the whole page. But I can’t figure out how to bring the two together.

    Dim Document As object
Dim Dispatcher As Object
Dim Frame As Object
Dim oSheet
Dim oRange
oSheet = ThisComponent.Sheets.getByName("ScoreCards_14")
oRange = oSheet.getCellRangeByName("F8:AE59")


Document = ThisComponent.CurrentController.Frame
Dispatcher = createUNOService("com.sun.star.frame.DispatchHelper")
Dispatcher.executeDispatch(Document,".uno:Print", "",0, Array())

Can someone help me with how I bring thesse two elements to work together? Also for multiple “print areas” would the range be defined as (“F8:AE59, F65:AE116”) … etc.
Thanks,
OldJack

[schiavinatto]

You want to print the two areas on a single page, the way to achieve this is using a new spreadsheet, paste the first area and paste the second area below.
Select this total area to print.

Or to print on two pages.

Sub PrintPrint
rem ----------------------------------------------------------------------
dim document   as object
dim dispatcher as object
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$ScoreCards_14.$F$8:$AE$59"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:DefinePrintArea", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Print", "", 0, Array())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$ScoreCards_14.$F$65:$AE$116"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:DefinePrintArea", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Print", "", 0, Array())
rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$ScoreCards_14.$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args7())
End Sub

[OldJack]

Thank you for your kind help. Below is the code I have entered into my program but when I run it it skips page one and prints page two twice and then page three. Can you tell me what I am doing wrong? Also, but of much lesser importance, is it possible to combine these three different print areas into one statement so that you don’t have to click print more than once?

Jack

Sub Print_ScoreCards_14
Dim Document As object
Dim Dispatcher As Object
Document = ThisComponent.CurrentController.Frame
Dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
REM Game 1
Dim Args1(0) as new com.sun.star.beans.PropertyValue
Args1(0).Name = "ToPoint"
Args1(0).Value = "ScoreCards_14.$F$8:$AE$167"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args1())
Dispatcher.executeDispatch(Document, ".uno:DefinePrintArea", "", 0, Array())
Dispatcher.executeDispatch(Document, ".uno:Print", "", 0, Array())
Wait 500
REM Game 2
Dim Args2(0) as new com.sun.star.beans.PropertyValue
Args2(0).Name = "ToPoint"
Args2(0).Value = "ScoreCards_14.$AG$8:$BF$167"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args2())
Dispatcher.executeDispatch(Document, ".uno:DefinePrintArea", "", 0, Array())
Dispatcher.executeDispatch(Document, ".uno:PrintDefault", "", 0, Array())
Wait 500
REM Game 3
Dim Args3(0) as new com.sun.star.beans.PropertyValue
Args3(0).Name = "ToPoint"
Args3(0).Value = "ScoreCards_14.$BH$8:$CG$167"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args3())
Dispatcher.executeDispatch(Document, ".uno:DefinePrintArea", "", 0, Array())
Dispatcher.executeDispatch(Document, ".uno:PrintDefault", "", 0, Array())
Wait 500
REM Return Home
Dim Args4(0) as new com.sun.star.beans.PropertyValue
Args4(0).Name = "ToPoint"
Args4(0).Value = "ScoreCards_14.$A$1"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args4())
End Sub

[schiavinatto]

Solved, the macro is faster than the printer’s buffer, it includes the Wait 500 command after the print command (half a second time), the macro stops for half a second. And the PRINT command I left only in the first one (check if the printer data is correct) the other two replace it with PRINTDEFAULT.

CHANGED IN YOUR MACRO ABOVE.

Or rather, adding printable areas and a single print command.

Sub Print_ScoreCards_14b
Dim Document As object
Dim Dispatcher As Object
Document = ThisComponent.CurrentController.Frame
Dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
REM Game 1
Dim Args1(0) as new com.sun.star.beans.PropertyValue
Args1(0).Name = "ToPoint"
Args1(0).Value = "ScoreCards_14.$F$8:$AE$167"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args1())
Dispatcher.executeDispatch(Document, ".uno:DefinePrintArea", "", 0, Array())
REM Game 2
Dim Args2(0) as new com.sun.star.beans.PropertyValue
Args2(0).Name = "ToPoint"
Args2(0).Value = "ScoreCards_14.$AG$8:$BF$167"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args2())
Dispatcher.executeDispatch(Document, ".uno:AddPrintArea", "", 0, Array())
REM Game 3
Dim Args3(0) as new com.sun.star.beans.PropertyValue
Args3(0).Name = "ToPoint"
Args3(0).Value = "ScoreCards_14.$BH$8:$CG$167"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args3())
Dispatcher.executeDispatch(Document, ".uno:AddPrintArea", "", 0, Array())
Dispatcher.executeDispatch(Document, ".uno:Print", "", 0, Array())
REM Return Home
Dim Args4(0) as new com.sun.star.beans.PropertyValue
Args4(0).Name = "ToPoint"
Args4(0).Value = "ScoreCards_14.$A$1"
Dispatcher.executeDispatch(Document, ".uno:GoToCell", "", 0, Args4())
End Sub

[Villeroy]

PrintRanges.odt (38.5 KB)
Open with macro execution enabled.
Click the install button.
Use the macros which can be found under My_Macros>pyCalc>PrintRanges

[OldJack]

Thank you once again, Gilberto. It works like a charm. You make it sound so simple and easy to follow. I’m getting the hang of it but have a long way to go.

Thanks again.

Jack.