Updates And Comments On

OpenOffice.org

Macros Explained

By

Andrew Pitonyak

Last Modified:

Friday, August 12, 2005 at 05:27:22 pm

Thank You

Thank you for purchasing a copy of my book. I sincerely hope that you find the book helpful in your endevor to learn and use Macros in OpenOffice.

Disclaimer

Although I strive to provide the most accurate information possible, the material in this document carries no guarantee of applicability, accuracy, or safety. Use the information and macros in this document at your own risk. In the unlikely event that they cause the loss of some or all of your data and/or computer hardware, that neither I, nor any of the contributors, will be held responsible.

Table of Contents

1 What is this document?	3

1.1 Changes in OOo version 2.0	3

1.2 Will there be a new release of the book for OOo version 2.0?	3

2 Errors	5

2.1 Chapter 1, page 3, last line, change “module” to “library”	5

2.2 Chapter 2, page 23, Table 3. Change “DebDbl” to DefDbl	5

2.3 Chapter 2, page 29, Table 7 lists all Types as Date.	5

2.4 Chapter 6, page 118, line 6, incorrect return value	6

2.5 Chapter 6, page 119, is String(2) a bug?	6

2.6 Chapter 6, page 120, middle of page, change Right to Left.	7

2.7 Chapter 8, pages 155-156, Tables 8 and 9.	7

2.8 Chapter 9, page 179, Accessing document libraries.	7

2.9 Chapter 11, page 205, Listing 3, case sensitive constants	8

2.10 Chapter 11, page 214, Table 5, Hidden parameter	8

2.11 Chapter 11, page 216, Listing 17, use a better variable name	8

2.12 Chapter 11, page 216, New location in OOo 2.0	8

2.13 Chapter 11, page 216, Tip references invalid URL	9

2.14 Chapter 12, page 232, Listing 2, consistent case usage	9

2.15 Chapter 12, page 255, Listing 25, spelling error	9

2.16 Chapter 14, page 326, Table 2, change “exits” to “exist”	9

2.17 Chapter 14, page 328, the CellAddressConverter	9

2.18 Chapter 14, page 331, Table 6, swap “left” and “right”	10

2.19 Chapter 14, page 336, Listing 11 "SetValidationRange"	10

2.20 Chapter 14, page 340, Listing 14, QueryRange.	10

2.21 Chapter 14, page 341, Reference incorrect figure.	10

2.22 Chapter 14, page 346, before Listing 20, clarification	10

2.23 Chapter 14, page 346, fourth paragraph, third sentence:	11

2.24 Chapter 14, pages 351, sorting in OOo 2.0	11

2.25 Chapter 14, page 355, Table 33	11

2.26 Chapter 14, page 355, Listing 29 is missing a forward slash	11

2.27 Chapter 14, page 356, Listing 29 requires a line break	12

2.28 Chapter 14, page 360, Listing 34 I modified a comment	12

2.29 Chapter 17, page 428, Change “Ctrl-Enter” to “Shift-Enter”	12

3 New features in OOo 2.0.	13

3.1 Chapter 2, Pages 20 and 21	13

3.1.1 Compatibility mode exists	13

3.1.2 Latin-1 character set are valid in identifier names.	13

3.1.3 Function rmdir() can not remove directories: Verified	13

3.2 Chapter 2, pages 27 – 28. String handling	13

3.2.1 Two double quotes is a single quote. Yes	13

3.2.2 New string constants. Yes	13

3.3 Chapter 2, Page 30, A new way to define variables. Yes	14

3.4 Chapter 2, Page 33, Option Base 1 affected by Compatible.	14

3.5 Chapter 2, Page 36, ReDim() works with UBound(). Yes	14

3.6 Chapter 2, Page 39, A Sub can be declared Private. Yes	14

3.7 Chapter 2, Page 41, Default values are now supported. Yes	14

3.8 Chapter 2, Page 45, Private works.	15

3.9 Chapter 2, Page 70, UBound and LBound and empty arrays.	15

3.10 Chapter 2, page 82, CCur and CVar are now supported.	15

3.11 Chapter 4, Page 91, ReDim Preserve easier to use.	15

3.12 Chapter 6, Page 126, Listing 6, Format is improved.	15

3.13 Chapter 15, Page 396, ConnectorType.LINE. No	15

3.14 Chapter 17, Page 447, Improved dialog functionality.	16

4 Questions from readers	17

4.1 Chapter 13. Write documents	17

4.1.1 Convert text tables into regular text	17

4.2 Chapter 14. Calc documents	18

4.2.1 Chapter 14, page 355, Listing 29: Howto break a link	18

What is this document?

This document was created for the following reasons:

Record noteworthy questions, and the answers, that have been asked about the book.

Document any errors or typos that have been found.

Document any other noteable changes or comments that should have appeared in the book.

Changes in OOo version 2.0

There have been very few changes in the API with the release of OpenOffice.org version 2.0. The development team has been intentional about leaving the API as it is.

Macros are now part of the scripting framework, which supports both Java and Python. Some of the menus have been changed as well. For example, Tools | Macros | Macro has been replaced with Tools | Macros | Organize Macros | OpenOffice.org Basic. In other words, the changes are minor and generally obvious.

Although the library functionality has not changed in implementation or in the API (of which I am aware), the user presentation changed. In OOo version 1.x, the “soffice” library container holds both the users specific libraries and the libraries that are distributed with OpenOffice.org. Although this is the same in OOo version 2.x, the “soffice” name is gone and the users macros are presented as though they are in a different library. Having used the new mechanism, I consider a great improvement even if it does hide the fact that the library container is the same according to the API.

OOo version 2 introduces a new document type for the new database API.

Will there be a new release of the book for OOo version 2.0?

No, there will not be a new release of the book. The release of OOo version 2.0 does not significantly affect the content of the book. Some updates may be made available online, but the changes do not warrant a new edition. I updated Chapter 1 with changes even though the changes are minor. These changes will be made available. Well, the changes are already available on the oooauthors site – did you forget that the publisher made Chapter 1 freely available?

Errors

Chapter 1, page 3, last line, change “module” to “library”

(Thanks Thomas Hunger) Change:

The Delete button deletes the currently selected module; it's not available unless the module is selected.

To:

The Delete button deletes the currently selected library; it's not available unless the library is selected.

Chapter 2, page 23, Table 3. Change “DebDbl” to DefDbl

(Thanks to Michael Doremus). Change DebDbl to DefDbl in Table 3.

Chapter 2, page 29, Table 7 lists all Types as Date.

When I started writing my book, I created a list of all supported functions. The second column, named Type, contained the function category. I used the category to determine the chapter in which I would discuss the function. I forgot to remove the Type column from Table 7. Rather than relisting Table 7 with the Type column removed, I decided to use the Type column to indicate the return values, as shown below. Note that the Description column also indicates the return type.

Table 7. Functions and subroutines related to dates and times.

Function	Type	Description
CDate(expression)	Date	Convert a number or string to a date.
CDateFromIso	Date	Convert to a date from an ISO 8601 date representation.
CDateToIso	String	Convert a date to an ISO 8601 date representation.
Date	String	Return the current date as a String.
DateSerial(yr, mnth, day)	Date	Create a date from component pieces: Year, Month, Day.
DateValue(date)	Date	Extract the date from a date/time value by truncating the decimal portion.
Day(date)	Integer	Return the day as an Integer from a Date value.
GetSystemTicks	Date	Return the number of system ticks as a Long.
Hour(date)	Long	Return the hour as an Integer from a Date value.
IsDate	Boolean	Is this a date?
Minute(date)	Integer	Return the minute as an Integer from a Date value.
Month(date)	Integer	Return the month as an Integer from a Date value.
Now	Date	Return the current date and time as a Date object.
Second(date)	Integer	Return the seconds as an Integer from a Date value.
Time	String	Return the time as a String.
Timer()	Date	Return the number of seconds since midnight as a Date. Convert to a Long.
TimeSerial(hour, min, sec)	Date	Create a date from component pieces: Hours, Minutes, Seconds.
WeekDay(date)	Integer	Return the integer 1 through 7, corresponding to Sunday through Saturday.
Year(date)	Integer	Return the year as an Integer from a Date value.

Chapter 6, page 118, line 6, incorrect return value

(Thanks Heinrich Siewers) Change:

set return value to -1

To:

set return value to 1

Note: Do not change the first occurrence on line 2.

Chapter 6, page 119, is String(2) a bug?

(Heinrich Siewers) I indicate that “String(2)” generates a string with two ASCII 0 characters and that I consider this a bug. I expect the Basic system to flag this as an error and instead it provides a default value of zero for the second argument. In other words, it is equivalent to “String(2, 0)”. Although I still consider this a bug, if I had to do it again, I would document the unexpected behavior and not call it a bug.

Chapter 6, page 120, middle of page, change Right to Left.

(Heinrich Siewers) Change:

In its simplest form, the Mid function has the same functionality as the Right function.

To:

In its simplest form, the Mid function has the same functionality as the Left function.

Chapter 8, pages 155-156, Tables 8 and 9.

John Viestenz pointed out that in Table 8, a value of 2 includes the choices Abort, Retry, and Ignore, which is not the same as a value of 5. For Table 9, add a return value of 3 corresponds to Abort. I created an issue to correct the help pages that are included with OOo.

http://qa.openoffice.org/issues/show_bug.cgi?id=53268

Chapter 9, page 179, Accessing document libraries.

The variables BasicLibrary and DialogLibraries refer to the library container that contains the subroutine or function that is currently running, rather than the library container in the current document – as the book states. To help illustrate this point, I added the following subroutine to an application level library:

Sub AppCheckIt

Print EqualUnoObjects(ThisComponent.BasicLibraries, BasicLibraries) 'False

End Sub

Next, I created the following subroutine in a document library.

Sub DocCheckIt

Print EqualUnoObjects(ThisComponent.BasicLibraries, BasicLibraries) 'True

Call AppCheckIt()

End Sub

The DocCheckIt subroutine prints True, and the AppCheckIt routine prints False. You can use the BasicLibraries property or the LibraryContainer property of the document to obtain the modules. Unfortunately, the dialogs are not available because of a bug – the getDialogContainer() method always returns null as shown in the following code:

oDoc.LibraryContainer.getByName("MyLib").getDialogContainer()

Listing 12 on page 179 uses BasicLibraries and DialogLibraries to list the number of dialogs and libraries contained in a document. Unfortunately, the macro must be contained in the document of interest. The ExamineDocumentLibraries_1 macro uses the LibraryContainer to work with any document, but it fails because getDialogContainer() always returns null.

Sub ExamineDocumentLibraries_1(oDoc)

Dim vLibs 'Hold the library names

Dim vMod 'Hold the modules/dialogs object

Dim nNumMods% 'Number of modules or dialogs in a library

Dim i% 'Scrap index variable

Dim s$ 'Scrap string variable

s = "*** Dialog Libs In Document" & CHR$(10) 'Initialize s

vLibs = oDoc.LibraryContainer.getElementNames() 'Library names

For i = LBound(vLibs) To UBound(vLibs) 'Look at each name

vMod = oDoc.LibraryContainer.getByName(vLibs(i)).getDialogContainer()

nNumMods = 0

If NOT IsNull(vMod) Then

nNumMods = UBound(vMod.getElementNames()) + 1

End If

s = s & vLibs(i) & " has " & nNumMods & " Dialogs"

s = s & CHR$(10)

Next i

s = s & CHR$(10)

s = s & "*** Basic Libs In Document" & CHR$(10)

For i = LBound(vLibs) To UBound(vLibs)

vMod = oDoc.LibraryContainer.getByName(vLibs(i)).getModuleContainer()

nNumMods = 0

If NOT IsNull(vMod) Then

nNumMods = UBound(vMod.getElementNames()) + 1 'Number of modules

End If

s = s & vLibs(i) & " has " & nNumMods & " modules" 'Build the string

s = s & CHR$(10)

Next i

MsgBox s, 0, "Libraries"

End Sub

An unfortunate consequence of the current behavior and bugs is that a dialog can not be accessed from a document library except from a macro running in the document's library.

Chapter 11, page 205, Listing 3, case sensitive constants

In OOo Basic, constants are case sensitive only the first time that they are used. The following two lines of code work:

Print com.sun.star.frame.FrameSearchFlag.TASKS

Print COM.SUN.STAR.frame.FrameSearchFLAG.tasKs

This following line fails if run by itself:

Print COM.SUN.STAR.frame.FrameSearchFLAG.tasKs

Thanks to Bernard Marcelly for this clarification.

Chapter 11, page 214, Table 5, Hidden parameter

For the Hidden parameter, change:

A value of False loads the document so that it is hidden.

To:

A value of True loads the document so that it is hidden.

Chapter 11, page 216, Listing 17, use a better variable name

Change the variable named noArgs to args. This is not really an error, but why did I choose this name?

Chapter 11, page 216, New location in OOo 2.0

In OOo 1.x, type detection is stored in the file:

...\OpenOfice.org1.1.0\share\registry\data\org\openoffice\Office\TypeDetection.xcu

In a pre-version 2.0 beta, the type detection information is stored in a separate directory. The directory contains numerous files. The directory is as follows:

...\openoffice.org1.9.109\share\registry\modules\org\openoffice\TypeDetection\Types\

Chapter 11, page 216, Tip references invalid URL

A tip in the middle of the page references http://oooconv.free.fr/engine/HowToConv.php, which no longer exists. http://oooconv.free.fr/oooconv/oooconv_en.html, currently works, but for how long I wonder.

Chapter 12, page 232, Listing 2, consistent case usage

The variable "s" is sometimes in upper case and sometimes in lower case.

Chapter 12, page 255, Listing 25, spelling error

Change

MsgBox msg, 0, "Althernate Meanings"

To

MsgBox msg, 0, "Alternate Meanings"

Chapter 14, page 326, Table 2, change “exits” to “exist”

(Thanks Heinrich Siewers) Change the text for “hasByName(name)” from:

Return True if the named spreadsheet exits.

To

Return True if the named spreadsheet exists.

Chapter 14, page 328, the CellAddressConverter

The behavior of the CellAddressConverter, which displays a Calc cell in a human readable form, is not working as expected. In OOo version 1.1.2, the UserInterfaceRepresentation property does not contain the contains the sheet name if the cell is in Sheet1, and it does if the cell is in any other sheet. An issue has been created in Issuezilla to track this discrepency (see http://www.openoffice.org/issues/show_bug.cgi?id=32817). I modified Listing 5 on page 328 to reflect these changes and I converted the listing to be subroutine rather than just a stub.

Sub DemoCellAddressConversion

Dim oConv

Dim oCell

oConv = ThisComponent.CreateInstance("com.sun.star.table.CellAddressConversion")

oCell = ThisComponent.Sheets(2).getCellByPosition(0,0) 'Cell Sheet3.A1

oConv.Address = oCell.getCellAddress()

Print oConv.UserInterfaceRepresentation 'Sheet3.A1

Print oConv.PersistentRepresentation 'Sheet3.A1

End Sub

Chapter 14, page 331, Table 6, swap “left” and “right”

(Thanks Heinrich Siewers) Change the text for “HoriJustify” from:

STANDARD - Default alignment is left for numbers and right for text.

To

STANDARD - Default alignment is right for numbers and left for text.

Chapter 14, page 336, Listing 11 "SetValidationRange"

The two lines:

oValidation.setFormula1(1.0)

oValidation.setFormula1(10.0)

Should read:

oValidation.setFormula1(1.0)

oValidation.setFormula2(10.0)

Chapter 14, page 340, Listing 14, QueryRange.

(Thanks Heinrich Siewers) The problem is that Figure 2 is generated assuming that oCell references cell C7. Unfortunately, oCell is used to set C1, B1, B8, E3, and E8 before the text for the dialog is used. One solution is to add the following line after the value in E8 is set and before the string s is assigned its first value:

oCell = oSheet.getCellByPosition(2, 6)

Another solution is to move the code that sets C1, B1, B8, E3, and E8 before the loop “For i = 1 to 5”.

Chapter 14, page 341, Reference incorrect figure.

(Thanks Heinrich Siewers) Immediately before Figure 3, I state:

“I leave it as an exercise for the reader to explain the row differences as shown in Figure 3.”

The problem is that Figure 3 shows column differences. There are two possible solutions, either remove the text “ as shown in Figure 3”, or change the text to read “ as listed in the last line of Figure 2”.

Chapter 14, page 346, before Listing 20, clarification

(Thanks Heinrich Siewers) Immediately before Listing 20, I state:

“The code snippet in Listing 20 assumes that Sheet 3 contains”

I fail to mention that “Sheet 3” is named “Sheet4” because because sheets are numbered staring with zero but named starting with 1.

Chapter 14, page 346, fourth paragraph, third sentence:

The paragraph after Listing 20, third sentence, change:

"The autoFill() method..."

Should be:

"The fillAuto() method..."

Chapter 14, pages 351, sorting in OOo 2.0

Old sorting methods will be deprecated and new sorting functionality will be used in the future. The SheetSortDescriptor2 provides enhanced sorting capabilities in spreadsheets. Listing 27 and Listing 28 should both be modified to use the new TableSortField structure rather than the deprecated SortField structure. The new structure supports all of the old properties and a few new ones. Ironically, the TableSortField structure is detailed in Table 28 and the deprecated structure is not mentioned, it is only used.

Chapter 14, page 355, Table 33

The changes made to Table 33 are shown in red. Notice that there are two changes.

Table 33. Methods defined by the XSheetLinkable interface.

Method	Description
getLinkMode()	Get the sheet’s link mode (see Table 32).
setLinkMode(SheetLinkMode)	Set the link mode (see Table 32).
getLinkUrl()	Get the link URL.
setLinkUrl(url)	Set the link URL.
getLinkSheetName()	Get the name of the linked sheet.
setLinkSheetName(name)	Set the name of the linked sheet.
link(url, sheetName, filterName, filterOptions, SheetLinkMode)	Link the sheet to another sheet in another document.

Chapter 14, page 355, Listing 29 is missing a forward slash

An extra forward slash is required in the URL. Change

sURL = "file://C:/My%20Documents/CH15/test.sxc"

to

sURL = "file:///C:/My%20Documents/CH15/test.sxc"

Chapter 14, page 356, Listing 29 requires a line break

Listing 29 starts on page 355 and ends on page 356. The last line of the listing reads as follows:

oSheet.link(sURL, "Sheet1", "", "", com.sun.star.sheet.SheetLinkMode.NORMAL) End Sub

This should read as follows:

oSheet.link(sURL, "Sheet1", "", "", com.sun.star.sheet.SheetLinkMode.NORMAL)

End Sub

This is a formatting error. The source code available from the publisher does not contain this error.

Chapter 14, page 360, Listing 34 I modified a comment

Change

REM Insert the new sheet at as the last document.

To

REM Insert the new sheet at the end.

Chapter 17, page 428, Change “Ctrl-Enter” to “Shift-Enter”

(Thanks walker jackson) On page 428, in the paragraph immediately following the note, I incorrectly state that Ctrl-Enter is used to enter multiple entries into a “List Entries” property for a list box. Change this to say “Shift-Enter”.

New features in OOo 2.0.

My book makes claims of new features scheduled for release with OOo 2.0. This section verifies these claims.

Chapter 2, Pages 20 and 21

Compatibility mode exists

The module option “ Option Compatible” is implemented and exists, as does the routine CompatibilityMode(True/False).

Latin-1 character set are valid in identifier names.

Letters in the Latin-1 (ISO 8859-1) character set are valid as identifier names – variable and routine names.

Sub TestäVariableName

Dim ä As Integer

ä = 4

Print ä

End Sub

I could change the section on page 21 that lists requirements for valid identifier names.

Function rmdir() can not remove directories: Verified

Both Visual Basic and OOo Basic support the rmdir() command to remove a directory. VBA can remove only empty directories, but OOo Basic can recursively remove an entire directory tree. If CompatibilityMode(True) is called prior to calling rmdir(), OOo Basic will act like VBA and generate an error if the specified directory is not empty.

page 20, 21, 129 (bottom)...

Sub TestRmDir()

MkDir("/andrew0/home/andy/a/b/c") REM Create nested subdirectories.

CompatibilityMode(False) REM Turn off compatibility mode.

RmDir("/andrew0/home/andy/a") REM Remove directory with subdirectories.

CompatibilityMode(True) REM Turn off compatibility mode.

MkDir("/andrew0/home/andy/a/b/c") REM Create nested subdirectories.

RmDir("/andrew0/home/andy/a") REM Runtime error, a is not empty.

End Sub

Chapter 2, pages 27 – 28. String handling

Two double quotes is a single quote. Yes

The following code verifies two double quotes acts as desired.

Dim s As String

S = "She said ""Hello""" REM She said "Hello"

Print s

New string constants. Yes

Table 6 on page 28 contains string constants currently supported by VB that are now supported in OOo.

Option Compatible

Sub TestNewStrings

Print ASC(vbCr) REM 13

End Sub

As of version 1.95.m74, you must use Option Compatible for the module rather than using CompatibilityMode(True). Also, the new string vbNullChar is a zero length string, which is not expected – I expected it to be a string of length 1 with the character CHR$(0).

Chapter 2, Page 30, A new way to define variables. Yes

The new methods as shown on page 30 work. A single example is shown as follows:

Dim W As Word.Application

W.Visible = True

Note that the new OLE object factory is also in place so you can use the CreateObject method, as mentioned on page 30.

Chapter 2, Page 33, Option Base 1 affected by Compatible.

As noted on page 33, Option Compatible changes the behavior of Option Base 1 so that the upper bound of an array is left unchanged.

Chapter 2, Page 36, ReDim() works with UBound(). Yes

The note on the top of page 36 is correct so now UBound() works with an empty array returned by ReDim().

Chapter 2, Page 39, A Sub can be declared Private. Yes

Although you can declare a subroutine as Private or Public, the subroutine is public unless CompatibilityMode(True) is called prior to trying to call the Duh subroutine.

Private Sub Duh

Print "duh"

End sub

Chapter 2, Page 41, Default values are now supported. Yes

The code at the bottom of page 41 now works. Option Compatible must be used.

Chapter 2, Page 45, Private works.

Page 45, I state that a bug related to private variables is scheduled to be fixed. Although declaring a variable using Dim is equivalent to declaring a variable as Private, prior to OOo version 2.0, private variables were not really private. Starting with OOo 2.0, after CompatibilityMode(True) is used, private variables are not visible outside of the module in which they are declared. Finally, Option Compatible has no affect on private variables.

Chapter 2, Page 70, UBound and LBound and empty arrays.

The UBound and LBound functions now work with empty arrays so you do not need to write your own “safe” version. This includes arrays returned by Array(), as mentioned in Chapter 4 on pages 93 and 94. DimArray() is mentioned on page 94. See also the bug note on page 97.

Chapter 2, page 82, CCur and CVar are now supported.

The tip on the top of page 82 is correct, the CCur and CVar functions are now supported.

Chapter 4, Page 91, ReDim Preserve easier to use.

In Table 1, I state that the “As Type” will become optional with the release of OOo version 2.0. I have verified this. This is stated again on page 94.

Chapter 6, Page 126, Listing 6, Format is improved.

The format function is improved, but scientific notation is still a problem. Listing 6 is recreated here with updated values.

Sub ExampleFormat

MsgBox Format(6328.2, "##,##0.00") REM 6,328.20

MsgBox Format(123456789.5555, "##,##0.00") REM 123,456,789.56

MsgBox Format(0.555, ".##") REM .56

MsgBox Format(123.555, "#.##") REM 123.56

MsgBox Format(123.555, ".##") REM 123.56 (FIXED)

MsgBox Format(0.555, "0.##") REM 0.56

MsgBox Format(0.1255555, "%#.##") REM %12.56

MsgBox Format(123.45678, "##E–####") REM 12E11 (broken)

MsgBox Format(.0012345678, "0.0E–####") REM 1.2E31(broken)

MsgBox Format(123.45678, "#.e–###") REM 1.e21 (Newly broken)

MsgBox Format(.0012345678, "#.e–###") REM 1.e31 (broken)

MsgBox Format(123.456789, "#.## is ###") REM 123.45 is 679 (strange)

MsgBox Format(8123.456789, "General Number") REM 8123.456789

MsgBox Format(8123.456789, "Fixed") REM 8123.46

MsgBox Format(8123.456789, "Currency") REM 8,123.46$ (broken)

MsgBox Format(8123.456789, "Standard") REM 8,123.46

MsgBox Format(8123.456789, "Scientific") REM 8.12E+03

MsgBox Format(0.00123456789, "Scientific") REM 1.23E-03 (FIXED)

MsgBox Format(0.00123456789, "Percent") REM 0.12%

End Sub

Chapter 15, Page 396, ConnectorType.LINE. No

The LINE ConnectorType still does not exist. This is probably because Line is a built-in command in Basic (see http://qa.openoffice.org/issues/show_bug.cgi?id=26221).

Print com.sun.star.drawing.ConnectorType.LINE

Chapter 17, Page 447, Improved dialog functionality.

I did not test outside of Basic so I can not comment on this.

Questions from readers

Chapter 13. Write documents

Convert text tables into regular text

Vagn Cramer asked how to solve the problem outlined in Listing 1

Listing 1. TextTable to text problem description.

for each TextTable

Convert the table to text with a paragraph or a tab as delimiter

First, you need to identify the text tables. You can enumerate the text in your document as shown in Book Listing 6 on pages 269 to 270 – this works because text tables are included as paragraphs. It is easier, however, to directly obtain the text tables by using the method getTextTables() as shown in Book Listing 37 on pages 303 to 304.

Assuming that the text tables are simple, as explained on page 308 of the book, and that they do not contain text fields or graphics, and you do not care about formatting, the simple macro shown in Listing 2 will do what you want.

Listing 2. TextTable to text problem description.

Sub TableToText

Dim oTable ' The primary table in question.

Dim oTables ' All of the TextTables.

Dim oCell ' A single cell.

Dim oText ' The text object that contains the text table.

Dim oCursor ' Temporary text cursor.

Dim oViewCursor ' The view cursor

Dim nRow As Integer

Dim nCol As Integer

Dim nRows As Integer

Dim nCols As Integer

Dim i As Integer

Dim s As String

oTables = ThisComponent.getTextTables()

For i = 0 To oTables.getCount() - 1

oTable = oTables.getByIndex(i)

nCols = oTable.getColumns().getCount() - 1

nRows = oTable.getRows().getCount() - 1

oText = oTable.getAnchor().getText()

ThisComponent.currentController.select(oTable)

oViewCursor = ThisComponent.currentController.getViewCursor()

oViewCursor.goLeft(1, False)

oCursor = oText.createTextCursorByRange(oViewCursor)

For nRow = 0 To nRows

s = ""

For nCol = 0 To nCols

oCell = oTable.getCellByPosition(nCol, nRow)

If nCol > 0 Then s = s & CHR$(9)

s = s & oCell.getString()

Next

oText.insertControlCharacter(oCursor, _

com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, False)

oText.insertString(oCursor, s, False)

'oTable.dispose()

Next

Next

End Sub

Listing 2 is simple and straightfoward except for one trick. You can not use the a text table anchor as a text range. If I want to insert text BEFORE a text table, my inclination is to create a text cursor from the anchor start location (see Listing 3).

Listing 3. You can not create a text cursor from a text table anchor.

oText.createTextCursorByRange(oTable.getAnchor().getStart())

An inability to simply use a text table anchor has caused me many headaches, so I decided that it was time to find a solution. In Listing 2, I select the text table using the current controller. I then use the view cursor to move left one space, which should move the cursor to just before the text table. This solution is not well thought out or well tested, so it will probably break if the table is the first thing in the document.

Chapter 14. Calc documents

Chapter 14, page 355, Listing 29: Howto break a link

Frank Jarratt asked the following question:

The macro in Chapter 14, page 355, Listing 29 "LinkASheet" creates a sheet link, how can I remove it besides using the menu: Edit | Links?

Before investigation, I expected is to either dispose the link or remove the link from the container. It turns out that the link can be removed from the container (in a fashion), but the container is really the sheet, not the SheetLinks object. In other words, I inspected three objects before I found the answer.

Each sheet supports the XSheetLinkable interface as is shown in Table 33 on page 355. There are two errors in Table 33 as shown above. If you set the link mode to NONE, then the link is broken. For the listing that you mention in the book, you could add the following code to remove the link

oSheets.getByName("LinkIt").setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)

18

Andrew Pitonyak

2004-08-07T08:46:02