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.
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
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) |
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
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. |
Users browsing this forum: No registered users and 1 guest