Category Archives: OpenOffice

OpenOffice Spreadsheets

Spreadsheets

OpenOffice.org Basic provides an extensive interface for program-controlled creation and editing of spreadsheets. This chapter describes how to control the relevant services, methods and properties of spreadsheet documents.

The Structure of Spreadsheet Documents

The document object of a spreadsheet is based on the com.sun.star.sheet.SpreadsheetDocument service. Each of these documents may contain several spreadsheets. In this guide, a table-based document or spreadsheet document is the entire document, whereas a spreadsheet (or sheet for short) is a sheet (table) in the document.

Documentation note.png VBA : Different terminology for spreadsheets and their content is used in VBA and OpenOffice.org Basic. Whereas the document object in VBA is called a Workbook and its individual pages Worksheets, they are called SpreadsheetDocument and Sheet in OpenOffice.org Basic.

Accessing Spreadsheets

You can access the individual sheets of a spreadsheet document through the Sheets list.

The following examples show you how to access a sheet either through its number or its name.

Example 1: access by means of the number (numbering begins with 0)

Dim Doc As Object
Dim Sheet As Object

Doc = ThisComponent
Sheet = Doc.Sheets (0)
Documentation note.png ThisComponent returns the currently active document.The expression Doc.Sheets(0) is a Basic simplification of the API call : Doc.getSheets.getByIndex(0)

Example 2: access by means of the name

Dim Doc As Object
Dim Sheet As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")

In the first example, the sheet is accessed by its number (counting begins at 0). In the second example, the sheet is accessed by its name and the getByName method.

The Sheet object that is obtained by the getByName method supports the com.sun.star.sheet.Spreadsheet service. In addition to providing several interfaces for editing the content, this service provides the following properties:

IsVisible (Boolean)
value True if the spreadsheet is visible.
PageStyle (String)
name of the page template for the spreadsheet.

Renaming Sheets

A sheet provides methods getName and setName to read and modify its name. Basic can handle both methods like a property Name. Here we rename the first sheet of the spreadsheet document.

Dim Doc As Object
Dim Sheet As Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Sheet.Name = "First"

Creating and Deleting Sheets

The Sheets container of a spreadsheet document is also used to create and delete individual sheets. The following example uses the hasByName method to check if a sheet called MySheet exists. If it does, the method determines a corresponding object reference by using the getByName method and then saves the reference in a variable in Sheet. If the corresponding sheet does not exist, it is created by the createInstance call and inserted in the spreadsheet document by the insertByName method.

Dim Doc As Object
Dim Sheet As Object

Doc = ThisComponent

If Doc.Sheets.hasByName("MySheet") Then
   Sheet = Doc.Sheets.getByName("MySheet")
Else
   Sheet = Doc.createInstance("com.sun.star.sheet.Spreadsheet")
   Doc.Sheets.insertByName("MySheet", Sheet)
End If

The hasByName, getByName and insertByName methods are obtained from the com.sun.star.container.XNameContainer interface as described in Introduction to the API.

The interface com.sun.star.sheet.Spreadsheets provides a better method to create a new sheet: insertNewByName. It inserts a new sheet with the name specified by the first argument, at the position specified by the second argument.

Dim Doc As Object

Doc = ThisComponent

Doc.Sheets.insertNewByName("OtherSheet", 2)

The same interface provides methods moveByName and copyByName.

The com.sun.star.container.XNameContainer interface provides a method to remove a sheet of a given name:

Dim Doc As Object

Doc = ThisComponent

Doc.Sheets.removeByName("OtherSheet")

Formatting Spreadsheet Documents

A spreadsheet document provides properties and methods for formatting cells and pages.

Cell Properties

There are numerous options for formatting cells, such as specifying the font type and size for text. Each cell supports the com.sun.star.style.CharacterProperties and com.sun.star.style.ParagraphProperties services, the main properties of which are described in Text Documents. Special cell formatting is handled by the com.sun.star.table.CellProperties service. The main properties of this service are described in the following sections.

You can apply all of the named properties to individual cells and to cell ranges.

Documentation note.png VBA : The CellProperties object in the OpenOffice.org API is comparable with the Interior object from VBA which also defines cell-specific properties.

Background Color and Shadows

The com.sun.star.table.CellProperties service provides the following properties for defining background colors and shadows:

CellBackColor (Long)
background color of the table cell
IsCellBackgroundTransparent (Boolean)
sets the background color to transparent
ShadowFormat (struct)
specifies the shadow for cells (structure in accordance with com.sun.star.table.ShadowFormat)

The com.sun.star.table.ShadowFormat structure and the detailed specifications for cell shadows have the following structure:

Location (enum)
position of shadow (value from the com.sun.star.table.ShadowLocation structure).
ShadowWidth (Short)
size of shadow in hundredths of a millimeter
IsTransparent (Boolean)
sets the shadow to transparent
Color (Long)
color of shadow

The following example writes the number 1000 to the B2 cell, changes the background color to red using the CellBackColor property, and then creates a light gray shadow for the cell that is moved 1 mm to the left and down.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
Dim ShadowFormat As New com.sun.star.table.ShadowFormat

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(1,1)

Cell.Value = 1000

Cell.CellBackColor = RGB(255, 0, 0)

ShadowFormat.Location = com.sun.star.table.ShadowLocation.BOTTOM_RIGHT
ShadowFormat.ShadowWidth = 100
ShadowFormat.Color = RGB(160, 160, 160)

Cell.ShadowFormat = ShadowFormat

Justification

OpenOffice.org provides various functions that allow you to change the justification of a text in a table cell.

The following properties define the horizontal and vertical justification of a text:

HoriJustify (enum)
horizontal justification of the text (value from com.sun.star.table.CellHoriJustify)
VertJustify (enum)
vertical justification of the text (value from com.sun.star.table.CellVertJustify)
Orientation (enum)
orientation of text (value in accordance with com.sun.star.table.CellOrientation)
IsTextWrapped (Boolean)
permits automatic line breaks within the cell
RotateAngle (Long)
angle of rotation of text in hundredths of a degree

The following example shows how you can “stack” the contents of a cell so that the individual characters are printed one under another in the top left corner of the cell. The characters are not rotated.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(1,1)

Cell.Value = 1000

Cell.HoriJustify = com.sun.star.table.CellHoriJustify.LEFT
Cell.VertJustify = com.sun.star.table.CellVertJustify.TOP
Cell.Orientation = com.sun.star.table.CellOrientation.STACKED

Number, Date and Text Format

OpenOffice.org provides a whole range of predefined date and time formats. Each of these formats has an internal number that is used to assign the format to cells using the NumberFormat property. OpenOffice.org provides the queryKey and addNew methods so that you can access existing number formats as well as create your own number formats. The methods are accessed through the following object call:

NumberFormats = Doc.NumberFormats

A format is specified using a format string that is structured in a similar way to the format function of OpenOffice.org Basic. However there is one major difference: whereas the command format expects English abbreviations and decimal points or characters as thousands separators, the country-specified abbreviations must be used for the structure of a command format for the NumberFormats object.

The following example formats the B2 cell so that numbers are displayed with three decimal places and use commas as a thousands separator.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
Dim NumberFormats As Object
Dim NumberFormatString As String
Dim NumberFormatId As Long
Dim LocalSettings As New com.sun.star.lang.Locale

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(1,1)

Cell.Value = 23400.3523565

LocalSettings.Language = "en"
LocalSettings.Country = "us"

NumberFormats = Doc.NumberFormats
NumberFormatString = "#,##0.000"

NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
If NumberFormatId = -1 Then
   NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
End If

MsgBox NumberFormatId
Cell.NumberFormat = NumberFormatId

The Format Cells dialog in OpenOffice.org Calc provides an overview of the different formatting options for cells.

Page Properties

Page properties are the formatting options that position document content on a page as well as visual elements that are repeated page after page. These include

  • Paper formats
  • Page margins
  • Headers and footers.

The procedure for defining page formats differs from other forms of formatting. Whereas cell, paragraph, and character elements can be formatted directly, page formats can also be defined and indirectly applied using page styles. For example, headers or footers are added to the page style.

The following sections describe the main formatting options for spreadsheet pages. Many of the styles that are described are also available for text documents. The page properties that are valid for both types of documents are defined in the com.sun.star.style.PageProperties service. The page properties that only apply to spreadsheet documents are defined in the com.sun.star.sheet.TablePageStyle service.

Documentation note.png VBA : The page properties (page margins, borders, and so on) for a Microsoft Office document are defined by means of a PageSetup object at the Worksheet object (Excel) or Document object (Word) level. In OpenOffice.org, these properties are defined using a page style which in turn is linked to the associated document.

Page Background

The com.sun.star.style.PageProperties service defines the following properties of a pages background:

BackColor (long)
color of background
BackGraphicURL (String)
URL of the background graphics that you want to use
BackGraphicFilter (String)
name of the filter for interpreting the background graphics
BackGraphicLocation (Enum)
position of the background graphics (value according to enumeration)
BackTransparent (Boolean)
makes the background transparent

Page Format

The page format is defined using the following properties of the com.sun.star.style.PageProperties service:

IsLandscape (Boolean)
landscape format
Width (long)
width of page in hundredths of a millimeter
Height (long)
height of page in hundredths of a millimeter
PrinterPaperTray (String)
name of the printer paper tray that you want to use

The following example sets the page size of the “Default” page style to the DIN A5 landscape format (height 14.8 cm, width 21 cm):

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object

Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")

DefPage.IsLandscape = True
DefPage.Width = 21000
DefPage.Height = 14800

Page Margin, Border, and Shadow

The com.sun.star.style.PageProperties service provides the following properties for adjusting page margins as well as borders and shadows:

LeftMargin (long)
width of the left hand page margin in hundredths of a millimeter
RightMargin (long)
width of the right hand page margin in hundredths of a millimeter
TopMargin (long)
width of the top page margin in hundredths of a millimeter
BottomMargin (long)
width of the bottom page margin in hundredths of a millimeter
LeftBorder (struct)
specifications for left-hand line of page border (com.sun.star.table.BorderLine structure)
RightBorder (struct)
specifications for right-hand line of page border (com.sun.star.table.BorderLine structure)
TopBorder (struct)
specifications for top line of page border (com.sun.star.table.BorderLine structure)
BottomBorder (struct)
specifications for bottom line of page border (com.sun.star.table.BorderLine structure)
LeftBorderDistance (long)
distance between left-hand page border and page content in hundredths of a millimeter
RightBorderDistance (long)
distance between right-hand page border and page content in hundredths of a millimeter
TopBorderDistance (long)
distance between top page border and page content in hundredths of a millimeter
BottomBorderDistance (long)
distance between bottom page border and page content in hundredths of a millimeter
ShadowFormat (struct)
specifications for shadow of content area of page (com.sun.star.table.ShadowFormat structure)

The following example sets the left and right-hand borders of the “Default” page style to 1 centimeter.

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object

Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")

DefPage.LeftMargin = 1000
DefPage.RightMargin = 1000

Headers and Footers

The headers and footers of a document form part of the page properties and are defined using the com.sun.star.style.PageProperties service. The properties for formatting headers are:

HeaderIsOn (Boolean)
header is activated
HeaderLeftMargin (long)
distance between header and left-hand page margin in hundredths of a millimeter
HeaderRightMargin (long)
distance between header and right-hand page margin in hundredths of a millimeter
HeaderBodyDistance (long)
distance between header and main body of document in hundredths of a millimeter
HeaderHeight (long)
height of header in hundredths of a millimeter
HeaderIsDynamicHeight (Boolean)
height of header is automatically adapted to content
HeaderLeftBorder (struct)
details of the left-hand border of frame around header (com.sun.star.table.BorderLine structure)
HeaderRightBorder (struct)
details of the right-hand border of frame around header (com.sun.star.table.BorderLine structure)
HeaderTopBorder (struct)
details of the top line of the border around header (com.sun.star.table.BorderLine structure)
HeaderBottomBorder (struct)
details of the bottom line of the border around header (com.sun.star.table.BorderLine structure)
HeaderLeftBorderDistance (long)
distance between left-hand border and content of header in hundredths of a millimeter
HeaderRightBorderDistance (long)
distance between right-hand border and content of header in hundredths of a millimeter
HeaderTopBorderDistance (long)
distance between top border and content of header in hundredths of a millimeter
HeaderBottomBorderDistance (long)
distance between bottom border and content of header in hundredths of a millimeter
HeaderIsShared (Boolean)
headers on even and odd pages have the same content (refer to HeaderText , HeaderTextLeft, and HeaderTextRight )
HeaderBackColor (long)
background color of header
HeaderBackGraphicURL (String)
URL of the background graphics that you want to use
HeaderBackGraphicFilter (String)
name of the filter for interpreting the background graphics for the header
HeaderBackGraphicLocation (Enum)
position of the background graphics for the header (value according to com.sun.star.style.GraphicLocation enumeration)
HeaderBackTransparent (Boolean)
shows the background of the header as transparent
HeaderShadowFormat (struct)
details of shadow of header (com.sun.star.table.ShadowFormat structure)

The properties for formatting footers are:

FooterIsOn (Boolean)
footer is activated
FooterLeftMargin (long)
distance between footer and left-hand page margin in hundredths of a millimeter
FooterRightMargin (long)
distance between footer and right-hand page margin in hundredths of a millimeter
FooterBodyDistance (long)
distance between footer and main body of document in hundredths of a millimeter
FooterHeight (long)
height of footer in hundredths of a millimeter
FooterIsDynamicHeight (Boolean)
height of footer is adapted automatically to the content
FooterLeftBorder (struct)
details of left-hand line of border around footer (com.sun.star.table.BorderLine structure)
FooterRightBorder (struct)
details of right-hand line of border around footer (com.sun.star.table.BorderLine structure)
FooterTopBorder (struct)
details of top line of border around footer (com.sun.star.table.BorderLine structure)
FooterBottomBorder (struct)
details of bottom line of border around footer (com.sun.star.table.BorderLine structure)
FooterLeftBorderDistance (long)
distance between left-hand border and content of footer in hundredths of a millimeter
FooterRightBorderDistance (long)
distance between right-hand border and content of footer in hundredths of a millimeter
FooterTopBorderDistance (long)
distance between top border and content of footer in hundredths of a millimeter
FooterBottomBorderDistance (long)
distance between bottom border and content of footer in hundredths of a millimeter
FooterIsShared (Boolean)
the footers on the even and odd pages have the same content (refer to FooterText, FooterTextLeft, and FooterTextRight )
FooterBackColor (long)
background color of footer
FooterBackGraphicURL (String)
URL of the background graphics that you want to use
FooterBackGraphicFilter (String)
name of the filter for interpreting the background graphics for the footer
FooterBackGraphicLocation (Enum)
position of background graphics for the footer (value according to com.sun.star.style.GraphicLocation enumeration)
FooterBackTransparent (Boolean)
shows the background of the footer as transparent
FooterShadowFormat (struct)
details of shadow of footer (com.sun.star.table.ShadowFormat structure)

Changing the Text of Headers and Footers

The content of headers and footers in a spreadsheet is accessed through the following properties:

LeftPageHeaderContent (Object)
content of headers for even pages (com.sun.star.sheet.HeaderFooterContent service)
RightPageHeaderContent (Object)
content of headers for odd pages (com.sun.star.sheet.HeaderFooterContent service)
LeftPageFooterContent (Object)
content of footers for even pages (com.sun.star.sheet.HeaderFooterContent service)
RightPageFooterContent (Object)
content of footers for odd pages (com.sun.star.sheet.HeaderFooterContent service)

If you do not need to distinguish between headers or footers for odd and even pages (the FooterIsShared property is False), then set the properties for headers and footers on odd pages.

All the named objects return an object that supports the com.sun.star.sheet.HeaderFooterContent service. By means of the (non-genuine) properties LeftText, CenterText, and RightText, this service provides three text elements for the headers and footers of OpenOffice.org Calc.

The following example writes the “Just a Test.” value in the left-hand text field of the header from the “Default” template.

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object
Dim HText As Object
Dim HContent As Object
Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")

DefPage.HeaderIsOn = True
HContent = DefPage.RightPageHeaderContent
HText = HContent.LeftText
HText.String = "Just a Test."
DefPage.RightPageHeaderContent = HContent

Note the last line in the example: Once the text is changed, the TextContent object must be assigned to the header again so that the change is effective.

Another mechanism for changing the text of headers and footers is available for text documents (OpenOffice.org Writer) because these consist of a single block of text. The following properties are defined in the com.sun.star.style.PageProperties service:

HeaderText (Object)
text object with content of the header (com.sun.star.text.XText service)
HeaderTextLeft (Object)
text object with content of headers on left-hand pages (com.sun.star.text.XText service)
HeaderTextRight (Object)
text object with content of headers on right-hand pages (com.sun.star.text.XText service)
FooterText (Object)
text object with content of the footer (com.sun.star.text.XText service)
FooterTextLeft (Object)
text object with content of footers on left-hand pages (com.sun.star.text.XText service)
FooterTextRight (Object)
text object with content of footers on right-hand pages (com.sun.star.text.XText service)

The following example creates a header in the “Default” page style for text documents and adds the text “Just a Test” to the header.

Dim Doc As Object
Dim Sheet As Object
Dim StyleFamilies As Object 
Dim PageStyles As Object
Dim DefPage As Object
Dim HText As Object

Doc = ThisComponent
StyleFamilies = Doc.StyleFamilies
PageStyles = StyleFamilies.getByName("PageStyles")
DefPage = PageStyles.getByName("Default")

DefPage.HeaderIsOn = True
HText = DefPage.HeaderText 

HText.String = "Just a Test."

In this instance, access is provided directly through the HeaderText property of the page style rather than the HeaderFooterContent object.

Centering (Spreadsheets Only)

The com.sun.star.sheet.TablePageStyle service is only used in OpenOffice.org Calc page styles and allows cell ranges that you want printed to be centered on the page. This service provides the following properties:

CenterHorizontally (Boolean)
table content is centered horizontally
CenterVertically (Boolean)
table content is centered vertically

Definition of Elements to be Printed (Spreadsheets Only)

When you format sheets, you can define whether page elements are visible. For this purpose, the com.sun.star.sheet.TablePageStyle service provides the following properties:

PrintAnnotations (Boolean)
prints cell comments
PrintGrid (Boolean)
prints the cell gridlines
PrintHeaders (Boolean)
prints the row and column headings
PrintCharts (Boolean)
prints charts contained in a sheet
PrintObjects (Boolean)
prints embedded objects
PrintDrawing (Boolean)
prints draw objects
PrintDownFirst (Boolean)
if the contents of a sheet extend across several pages, they are first printed in vertically descending order, and then down the right-hand side.
PrintFormulas (Boolean)
prints the formulas instead of the calculated values
PrintZeroValues (Boolean)
prints the zero values

Editing Spreadsheet Documents

Whereas the previous section described the main structure of spreadsheet documents, this section describes the services that allow you to easily access individual cells or cell ranges.

Cell Ranges

In addition to an object for individual cells (com.sun.star.table.Cell service), OpenOffice.org also provides objects that represent cell ranges. Such CellRange objects are created using the getCellRangeByName call of the spreadsheet object:

Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByName("A1:C15")

A colon (:) is used to specify a cell range in a spreadsheet document. For example, A1:C15 represents all the cells in rows 1 to 15 in columns A, B, and C.

If the position of the cell range is only known at runtime, use the following code:

Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByPosition(0, 0,  2, 14)

The arguments of getCellRangeByPosition are the position of the upper left cell of the range, followed by the position of the bottom right cell of the same range.

The location of individual cells in a cell range can be determined using the getCellByPosition method, where the coordinates of the top left cell in the cell range is (0, 0). The following example uses this method to create an object of cell C3.

Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object
Dim Cell As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByName("B2:D4")
Cell = CellRange.GetCellByPosition(1, 1)

Formatting Cell Ranges

Just like individual cells, you can apply formatting to cell ranges using the com.sun.star.table.CellProperties service. For more information and examples of this service, see Formatting Spreadsheet Documents.

Computing With Cell Ranges

You can use the computeFunction method to perform mathematical operations on cell ranges. The computeFunction expects a constant as the parameter that describes the mathematical function that you want to use. The associated constants are defined in the com.sun.star.sheet.GeneralFunction enumeration. The following values are available:

SUM
sum of all numerical values
COUNT
total number of all values (including non-numerical values)
COUNTNUMS
total number of all numerical values
AVERAGE
average of all numerical values
MAX
largest numerical value
MIN
smallest numerical value
PRODUCT
product of all numerical values
STDEV
standard deviation
VAR
variance
STDEVP
standard deviation based on the total population
VARP
variance based on the total population

The following example computes the average value of the A1:C3 range and prints the result in a message box:

Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByName("A1:C3")

MsgBox CellRange.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE)
Documentation caution.png Functions VAR, VARP, STDVERP return an incorrect value when applied to a properly defined range. See Issue 22625 .

Deleting Cell Contents

The clearContents method simplifies the process of deleting cell contents and cell ranges in that it deletes one specific type of content from a cell range.

The following example removes all the strings and the direct formatting information from the B2:C3 range.

Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object
Dim Flags As Long

Doc = ThisComponent
Sheet = Doc.Sheets(0)
CellRange = Sheet.getCellRangeByName("B2:C3")

Flags = com.sun.star.sheet.CellFlags.STRING + _
      com.sun.star.sheet.CellFlags.HARDATTR

CellRange.clearContents(Flags)

The flags specified in clearContents come from the com.sun.star.sheet.CellFlags constants list. This list provides the following elements:

VALUE
numerical values that are not formatted as date or time
DATETIME
numerical values that are formatted as date or time
STRING
strings
ANNOTATION
comments that are linked to cells
FORMULA
formulas
HARDATTR
direct formatting of cells
STYLES
indirect formatting
OBJECTS
drawing objects that are connected to cells
EDITATTR
character formatting that only applies to parts of the cells

You can also add the constants together to delete different information using a call from clearContents.

Searching and Replacing Cell Contents

Spreadsheet documents, like text documents, provide a function for searching and replacing.

The descriptor objects for searching and replacing in spreadsheet documents are not created directly through the document object, but rather through the Sheets list. The following is an example of a search and replace process:

Dim Doc As Object
Dim Sheet As Object
Dim ReplaceDescriptor As Object
Dim I As Integer

Doc = ThisComponent
Sheet = Doc.Sheets(0)

ReplaceDescriptor = Sheet.createReplaceDescriptor()
ReplaceDescriptor.SearchString = "is"
ReplaceDescriptor.ReplaceString = "was"
For I = 0 to Doc.Sheets.Count - 1
   Sheet = Doc.Sheets(I)
   Sheet.ReplaceAll(ReplaceDescriptor) 
Next I

This example uses the first page of the document to create a ReplaceDescriptor and then applies this to all pages in a loop.

End