[Solved] Print specific sheet on button click

[nasch]

Hello all, I am working on a calc document with many sheets which are formatted to match the style/design of a retail price sign. The formatting is very specific and I've taken to a spreadsheet to simplify the inputs. In any case, I am hitting a wall on enabling a macro to print a specific sheet without navigating to it first.

To better explain.. sheet "input" is where the user records ALL information that is relevant to any style of sheet: StyleA, SubstyleB, StyleC etc. I very much want to enable the user to never need to leave the input sheet, so multiple buttons that read "Print StyleA", "Print SubstyleB", and "Print StyleC" on the side of the input sheet seems like the easiest solution.. however I cannot wrap my head around creating a macro to do this.

Each sheet has a defined print range, so if the sheet is printed it should be properly formatted based off this - I think? I do not want the active sheet to be changed, at least not "permanently" when the button is selected. I have searched forum entries and have found pieces of a solution but nothing that quite captures all these requirements and admittedly I lack the understanding necessary to adjust them.

To summarize, I am looking for a macro I can map to a button which can be called from Sheet1 that will print (for example) Sheet4 without changing the active sheet on the user-side. I will reproduce the macro for all the sheets, unless there is a more efficient method available.

[Zizi64]

Please upload your ODF type sample file here.

[ms777]

Hi,

I am pretty sure that this is not easily achievable as neither https://www.openoffice.org/api/docs/com ... tions.html nor https://wiki.openoffice.org/wiki/Docume ... b_Settings nor the dialog when executed through the UI have any other option besides printing all sheets or the active sheet.

I believe you have two options for the macro:
1. lock the controller so that the user cannot interact with the document
2. switch to the sheet to be printed
3. print the then active sheet
4. switch back to the sheet "input"
5. unlock the controller
This would be relatively easy to implement, but the user would see the switching to the sheet to be printed and back to the original sheet.

The other option:
1. make sure that the document is saved to some location
2. Reopen the document in a 2nd window in hidden / readonly mode
3. In the second document, switch to the sheet to be printed
4. print the active sheet of the second document.
5. close the second document
This is more difficult to implement and more failure prone, but should be invisible for the user ...

Good luck,
ms777

[JeJe]

Another way to stop the user interacting with the document is to show a modal dialog saying "Printing... please wait" or something on it. It could include a progress bar and be made the size of the screen to obscure whatever operations you are doing.

[nasch]

Please upload your ODF type sample file here.

Apologies for the delay. A generic sample file is attached here, hopefully illustrating what I am seeking. Admittedly the macro assigned to the button was sourced from another ask for help.

I believe you have two options for the macro:
1. lock the controller so that the user cannot interact with the document
2. switch to the sheet to be printed
3. print the then active sheet
4. switch back to the sheet "input"
5. unlock the controller
This would be relatively easy to implement, but the user would see the switching to the sheet to be printed and back to the original sheet.

This would be totally fine, presuming it would really not be ultra-visible to the user as the time required would be minimal.. As relatively easy as this route might be I have no clue what direction this is.. in principle it sounds logical and something I can probably sniff out, but would the print option look similar to what I've put in the sample? I've read in many posts about time delays being added for varying reasons.. necessary?

Another way to stop the user interacting with the document is to show a modal dialog saying "Printing... please wait" or something on it. It could include a progress bar and be made the size of the screen to obscure whatever operations you are doing.

I've seen some pseudo-implementations of this in my reading. Ideally it won't be necessary to be honest as I'm really hopeful this "tool" overall will make for rapid printing. I'd love for a way to suppress the print confirmation altogether and just forcibly send the page as specified in the PrintRange to the default printer automatically upon button press.

[Zizi64]

Your macro was created by the MacroRecorder. It is an easy, but not effective mode of the macro creating.
It is better to WRITE your macros based on the API functions.

I just added some RECORDED code to your macro set: to select the desired sheet to print, and finally to jump back to the first sheet. And I have added a passed parameter manually to the recorded SelectSheet routine. See my notes in the code.

The first three small routines of the code list is created manually. They contain simple calling of the recorded routines with passing the sheet number to the SelectSheet() routine.

Note:
The numbering of the sheets is "1" based in the Dispatcher but it is "0" based in the API functions.
You can select a sheet by its name when you use the API functions.

Edited in LO 6.4.7.

Samplefile_Zizi64.ods

(53.93 KiB) Downloaded 77 times

[Zizi64]

A bugfix:
It need a Wait cycle after you call the print routine, because the "jump back" routine activated too early, and the Print routine picked the sheet 1 again as "actual sheet".

Code: Select all

Sub Print2
	SelectSheet(2) 'Zizi64: Jump to the desired sheet. Note: The numbering of the  sheets is 1 based in the dispatcher but it is 0 based in the API.
	PrintDoc
	wait 500
	SelectSheet(1) 'Zizi64: Jump back to the first sheet
End sub


Sub Print3
	SelectSheet(3) 'Zizi64: Jump to the desired Sheet Note: The numbering of the  sheets is 1 based in the dispatcher but it is 0 based in the API.
	PrintDoc
	wait 500
	SelectSheet(1) 'Zizi64: Jump back to the first sheet
End sub


Sub Print4
	SelectSheet(4) 'Zizi64: Jump to the desired Sheet Note: The numbering of the  sheets is 1 based in the dispatcher but it is 0 based in the API.
	PrintDoc
	wait 500
	SelectSheet(1) 'Zizi64: Jump back to the first sheet
End sub

[nasch]

Thank you very much, with the bugfix edits the button reacts the way I was hoping for.

I'm a little confused by "The numbering of the sheets is "1" based in the Dispatcher but it is "0" based in the API functions.You can select a sheet by its name when you use the API functions." As it is calling the Dispatcher this method does not make use of the API functions? The true document has many more sheets to it and I've needed to add additional based off different situations as they arise; I would prefer making use of their name but I'm gathering that is going to be more complex/go beyond the recorder?

Thank you again for this assistance.

[JeJe]

You can use either the name or the index of the sheet - numbered from 0 as that is the norm for programming

Code: Select all

'byname
sheet = thiscomponent.sheets.getbyname("Sheet1")
'or
sheet = thiscomponent.sheets("Sheet1")

'by index
sheet = thiscomponent.sheets.getbyindex(0)
'or
sheet = thiscomponent.sheets(0)

If "Sheet1" is the first sheet in the spreadsheet the above all make the variable sheet equal to "Sheet1"

Edit: the number "1" in a sheet called "Sheet1" has no significance at all - its just part of an arbitrary name like any other letter/character in that name.

[Zizi64]

As it is calling the Dispatcher this method does not make use of the API functions?

The Dispatcher try to record and simulates (try to simulate) the activity of the User.

The real programming (controlling) method of the LibreOffice and the Apache OpenOffice is the usage of the API functions (Application Programming Interface), like in JeJe's sample code snippet.
His code snippet quasi equivalent to the Dispatcher related "SelectSheet" subroutine.
See Andrew Pitonyak's free macro books.

[nasch]

Thank you both very much. This helped me immensely.