How to print only one sheet

[couper]

I want to print only one sheet, such as sheet 2, however in sheet1, there may be serveral pages.
If you don't how many pages in sheet 1, how to do this? Could you offer me some sample code?
Appreciate your answer.

[squenson]

I attach a file with a macro that only prints one sheet with the current page numbers. The trick is to hide all the other sheets before printing and to restore their status afterward. You should run the macro PrintSheet that displays an input box, and it calls the macro PrintSingleSheet that does all the work. I copy the code below, so it will also be indexed and can be retrieved using the search function of this forum.

Code: Select all   Expand viewCollapse view

Sub PrintSheet

   Dim sSheetName as String
   
   
   sSheetName = InputBox("Which sheet do you want to print?")
   PrintSingleSheet(sSheetName)
   
End Sub


Sub PrintSingleSheet(sSheetName as String)
' This macro prints a single sheet based on its name
' The trick is to hide all sheets but the one to print
' and restore their status immediately after

   Dim oSheets as Object
   Dim bSheetIsVisible(255) as Boolean
   Dim bSheetExists
   Dim i as Long
   Dim oDoc as Object
   Dim args2(2) as New com.sun.star.beans.PropertyValue
   Dim dispatcher as Object
   
   
                     ' Search the sheet within the workbook
                     ' If not found, display a message
   oSheets = ThisComponent.Sheets
   bSheetExists = FALSE
   For i = 0 to oSheets.getCount() - 1
      If sSheetName = oSheets.getByIndex(i).Name Then
         bSheetExists = TRUE
         Exit For
      End If
   Next i
   
   If Not(bSheetExists) Then
      MsgBox "The sheet """ & sSheetName & """ does not exist in this document. Nothing will be printed."
      Exit Sub
   End If
   
   
                     ' Hide all the sheets but the one we need to print
   For i = 0 to oSheets.getCount() - 1
      bSheetIsVisible(i) = oSheets.getByIndex(i).IsVisible
      If sSheetName <> oSheets.getByIndex(i).Name Then
         oSheets.getByIndex(i).IsVisible = FALSE
      Else
         oSheets.getByIndex(i).IsVisible = TRUE
      End If
   Next i

                     ' Print the document (only one visible sheet)   
   oDoc = ThisComponent.CurrentController.Frame
   args2(0).Name = "Copies"
   args2(0).Value = 1
   args2(1).Name = "Selection"
   args2(1).Value = true
   args2(2).Name = "Collate"
   args2(2).Value = false
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   dispatcher.executeDispatch(oDoc, ".uno:Print", "", 0, args2())

                     ' Restore the visibility status of all sheets
   For i = 0 to oSheets.getCount() - 1
      oSheets.getByIndex(i).IsVisible =  bSheetIsVisible(i)
   Next i

End Sub

[Villeroy]

Great demo! Let's nail down things a little bit.
First of all, the "uno:.print" dispatch does nothing on my machine although printing to the one and only default printer works fine. This may be due to my currently used Ubuntu BrokenOffice.org or something else. I can simply click the print icon after running your code.

Sheet names are case insensitive. Your macro fails to recognize "sheet1" although oSHeets.hasByName("sheet1"). I reduced your code by several lines, introducing this method on one side and Basic StrComp(s1,s2,0) on the other side.

Finally I added Sub Sabotage in order to demonstrate a situation where this technique must fail.

Code: Select all   Expand viewCollapse view

REM  *****  BASIC  *****
Sub PrintSheet

   Dim sSheetName as String

   sSheetName = InputBox("Which sheet do you want to print?")
   if ThisComponent.Sheets.hasByName(sSheetName) then

   Sabotage
      PrintSingleSheet(sSheetName)
   
   else
      MsgBox "The sheet """ & sSheetName & """ does not exist in this document. Nothing will be printed."
   endif
   
End Sub

Sub Sabotage
Const sN = "Sabotage"
   With ThisComponent.getSheets()
      If Not .hasByName(sN) then .insertNewByName(sN,0)
      With .getByName(sN)
         With .getCellByPosition(0,0)
            .setString(sN)
            addr = .getRangeAddress()
         End With
         .setPrintAreas(Array(addr))
         .IsVisible = False
      End With
   End With
End Sub

Sub PrintSingleSheet(sSheetName as String)
' This macro prints a single sheet based on its name
' The trick is to hide all sheets but the one to print
' and restore their status immediately after

Dim oSheets as Object
Dim bSheetIsVisible(255) as Boolean
Dim i as Long, s as String
Dim oDoc as Object
Dim args2(2) as New com.sun.star.beans.PropertyValue
Dim dispatcher as Object


               ' Search the sheet within the workbook
               ' If not found, display a message
oSheets = ThisComponent.Sheets


' Hide all the sheets but the one we need to print
For i = 0 to oSheets.getCount() - 1
   With oSheets.getByIndex(i)
      s = .getName()
      bSheetIsVisible(i) = .IsVisible
      .IsVisible = (StrComp(sSheetName,s,0) = 0)
   End With
Next i

' Print the document (only one visible sheet)   
oDoc = ThisComponent.CurrentController.Frame
args2(0).Name = "Copies"
args2(0).Value = 1
args2(1).Name = "Selection"
args2(1).Value = true
args2(2).Name = "Collate"
args2(2).Value = false
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(oDoc, ".uno:Print", "", 0, args2())

' Restore the visibility status of all sheets
For i = 0 to oSheets.getCount() - 1
   oSheets.getByIndex(i).IsVisible =  bSheetIsVisible(i)
Next i

End Sub

Edit: (StrComp(sSheetName,s,0)) = 0 (StrComp(sSheetName,s,0) = 0)

[squenson]

Thank you Villeroy for your comments and constructive enhancements!

First, there *must* be an easier way to do it with the uno:Print object. When you use the dialog box to print, you can select a sheet, then print it, so there *must* be a parameter to pass as an argument but my searches have been unsuccessful so far.

I like the "sabotage" procedure that creates a print range which apparently takes precedence when you print. In fact, once the "Sabotage" sheet is created, you cannot print anything else but this sheet using the Print menu option! Bug or feature, I don't know, but it looks odd and I guess many people will search for hours why they cannot print their current sheet or whole document...

So a solution would be to delete all the print ranges in the document and recreate them after the print is completed.

[Villeroy]

This is exactly the behaviour of the user interface and raised lots of confusion in the Calc forums. If you set up print ranges for a single sheet or any collection of sheets, then there is only one option to override these print ranges: Select what you want to print and print with option "Selected cells".

IMHO, it would take some time to solve this problem by means of macros, so we could deliver a high-quality add-on which simply does what the user expects when he calls a command "Print sheet":

[Dialog Print Sheets]
drop down Sheet: <default: active sheet>
[o] Used range
[ ] Set up ranges (disabled if none)

Note: Call the built-in dialog to print selected cells only

[OK] [Cancel]

[Villeroy]

Meanwhile I'm totally confused about which options overrides which other option, but I'm fairly confident that the following routine does something useful. It seems to work in this simple manner since I dropped the dispatch-calls. XPrintable.print(aProperties()) seems to print set up print areas of the active sheet selected sheets, disregarding other sheets having print areas or not. So this code can be focussed on the active sheet's print areas. The important comments are in the code.

Code: Select all   Expand viewCollapse view

REM  *****  BASIC  *****
Option Explicit

Sub PrintActiveSheet()
'calls: getUsedRange
REM Print active sheet's selection without prompting for details.
REM If the selection is a single cell or not a range at all,
REM print set up print ranges or the sheet's used range.
REM The API can not distinguish between a single cell being highlighted or not,
REM so this routine can not print a single highlighted cell.
Dim oView, oSheet, oUsedRange, oSelection
Dim aOldAreas(), aPrintAreas(), oBasket

oView = ThisComponent.getCurrentController()
oSheet = oView.getActiveSheet()
aOldAreas() = oSheet.getPrintAreas()
oSelection = oView.getSelection()

REM Reduce a multiple sheet selection to active sheet:
If oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") then
   oSelection = oSelection.queryIntersection(oSheet.getRangeAddress())
   if oSelection.getCount() = 1 then oSelection =oSelection.getByIndex(0)
Endif

REM get an array of addresses:
If oSelection.supportsService("com.sun.star.sheet.SheetCell") then
   aPrintAreas() = aOldAreas()
elseif oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") then
   aPrintAreas() = oSelection.getRangeAddresses()
elseif oSelection.supportsService("com.sun.star.sheet.SheetCellRange") then
   aPrintAreas() = Array(oSelection.getRangeAddress())
else ' shape, note, chart, ...
   aPrintAreas() = aOldAreas()
endif

REM got no addresses?
if uBound(aPrintAreas()) < 0 then
   REM load helpers if necessary: GlobalScope.BasicLibraries.loadLibrary("Calc")
   oUsedRange = getUsedRange(oSheet)
   aPrintAreas = Array(oUsedRange.getRangeAddress())
endif

oSheet.setPrintAreas(aPrintAreas())

Dim aPrintOptions(2) as new com.sun.star.beans.PropertyValue
aPrintOptions(0).Name = "CopyCount"
aPrintOptions(0).Value = 1

REM save paper while testing:
' aPrintOptions(1).Name = "FileName"
' aPrintOptions(1).Value = "file:///tmp/printActiveSheet.ps"

REM http://api.openoffice.org/docs/common/ref/com/sun/star/view/PrintOptions.html
REM @attention Using of this property with TRUE as value is highly reommended.
REM Otherwhise following actions (as e.g. closing the corresponding model) can fail.
aPrintOptions(2).Name = "Wait"
aPrintOptions(2).Value = True


ThisComponent.print(aPrintOptions())
oSheet.setPrintAreas(aOldAreas())
End Sub

'_______ Helper functions ____________
Function getUsedRange(oSheet)
Dim oRg
   oRg = oSheet.createCursor()
   oRg.gotoStartOfUsedArea(False)
   oRg.gotoEndOfUsedArea(True)
   getUsedRange = oRg
End Function


The macro prints contents of the active sheet and only the active sheet.

1. The selected range(s) if more than one cell is selected (disregarding other selected sheets)

2. If a single cell is selected
2.1 The active sheet's set up print range(s) if any
2.2 The active sheet's used range
Bugs anyone? Does it work with any printer setup?

Edit: Played with this code another time and noticed that I did not take multiple sheets into account. Added a small snippet to disregard other sheets than the single active one.

[abhiabhi_84]

Hi,

I have multiple printable sheets in file. All of them have print range defined.
But I could not print only ACTIVE sheet. Is there anyway to print only ACTIVE sheet or only UNHIDDEN sheets.

I tried hiding all the sheets except one, and printed the unhidden sheet using macro. But even unhidden sheets were printed, may be because the sheets had print range defined.

Any solution or suggestion?

Abhi

[Villeroy]

The above macro does exactly what you want. It prints the active sheet to the default printer.

[abhiabhi_84]

No BOSS !
This code prints all the sheets.
By all sheets i mean, hidden sheets and also sheets for which print range is not defined.

Abhi

[Villeroy]

You are right. I always set a PDF-printer as default printer when testing the macro in order to run many tests without wasting material and nerves. Now I proved that my physical printer receives all the sheets. I give up on this.

But since you have set print ranges for all your sheets and you want to print the print ranges of the active sheet, you can call the printer dialog (Ctrl+P) and set option "Selected Sheets". This works for me.
The macro tried to handle the case where the selected sheet has no print ranges defined. It defines a temporary print range based on your selection or the used range.

If I ever had this problem I would print the whole thing to to a file (PostScript) or export to PDF and then use my preferred PDF/PostScript-viewer to print out the pages I need.

[abhiabhi_84]

Is it then possible to call printer dialog through macro and also write a code to select "Print selected sheet only"?