Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
BASIC MACROINSTRUCTIONS (MACROS) OO Calc offers a functionality of building complex actions and quasi-programs by means of a special scripting language called OpenOffice.org Basic (OOo Basic), applied in many variants (usually known as Visual Basic or Visual Basic for Applications – VBA) in many common applications (e.g. MS Office, AutoCAD). Macros are available in the same manner in all OpenOffice applications. In order to run macros you will probably need a Java Virtual Machine (JVM). If it is not available in your system, download it (JRE 1.5) and install: http://java.sun.com/j2se/1.5.0/download.jsp. Then configure your JRE in OpenOffice by Tools → Options → OpenOffice.org → Java.
Recording and running a macro You can automate many functions and activities by recording them and running as macros. In order to demonstrate this feature, open OO Calc and enter a few random cell values (e.g. text, numbers, dates) Select Tools → Macros → Record Macro. A small dialog enabling to stop recording opens. Select the range of cells with values you have just entered. Apply some cell formatting (e.g. negative numbers in red, some special date formats, fonts, borders, alignments, colours, etc.). Stop recording and decide where and how to save your macro (in the workbook, in a general library, etc.). Remember its name and location so that you could reuse it. Now remove the formatting from the cell range, you can also change some of the values. Start your macro (Tools → Macros → Run macro) and see if it works.
Macro editor Every macro is stored as a plain-text code. In order to see it, select Tools → Macros → Organize macros → OpenOffice.org Basic, find you macro and press Edit (a macro edition is also available from other places). The editor and a code could look as follows:
By pressing Organizer button you can also create a new empty macro and then start editing its code. page 1 of 7
Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
Simple macros and dialogs Create a new module and a new subprocedure within the module and start the editor. A module is an independent OOo Basic structure (like a program in programming languages) that can contain a set of subprocedures (similar to functions in programming languages). A subprocedure is referred to as a macro.
A subprocedure starts with a Sub keyword and ends with End Sub keywords: Sub MyFirstMacro print("Hello, World!") End Sub
When you press a Run BASIC button, a dialog with a message should open:
Please notice that no action has been assigned to the dialog buttons yet (it may cause some “strange” behaviour and warnings). You can also create a macro enabling some interaction with a user: Sub MySecondMacro answer = inputbox("How old are you?", "Question", "") print(answer) End Sub
If a module contains more then one procedures, the first of them is run by default. If you need to run some other subprocedure, select Tools → Macros → Run macro and point the appropriate one. Furthermore, some complex user-defined (custom) dialogs can be created.
Custom dialog Lets create a macro enabling user to open one of external applications (creating a macro select the ones that are installed in your system). Select Tools → Macros → Organize dialogs and create a new dialog. A visual editor opens: page 2 of 7
Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
In order to add some controls (buttons, text fields, etc.) select them from the toolbox and “draw” them in a dialog area. At first, add a command button. The IDE automatically assigns a button its name “CommandButton1” and a label (caption) “CommandButton1”. Change these properties opening the properties dialog from the context menu (or by a double-click):
Similarly, create another button: page 3 of 7
Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
The next part of creating the dialog is to assign the buttons their actions (events), in our case they should start some external applications. The events are available in the second tab of the properties dialog:
Here you can assign an existing macro to each event appearing on a control component. It means that a macro must be created before (then it can be selected with the ellipsis button: “...” ). Let's create macros starting the external applications. It is convenient to use a Shell() function (it takes a full path of an executable as an argument). The macros can look as below: Sub StartGimp Shell("C:\Program Files\GIMP-2.2\bin\gimp-2.2.exe") End Sub Sub StartInkscape Shell("C:\Program Files\Inkscape\inkscape.exe") End Sub page 4 of 7
Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
Assign the macros to a “mouse button pressed” event of each button. A long name appearing in the properties dialog, e.g.: vnd.sun.star.script:Standard.Module2.StartInkscape?language= Basic&location=application is derived from a hierarchical structure of OO tools, which is a very useful solution and allows a very fast allocation and identification of a called procedure. In order to show our custom dialog we should create another macro. It should look like the one below (put attention to the names of libraries and a dialog – in bold in the code sample): Sub DialogMacro DialogLibraries.LoadLibrary("Standard") MyDialog = CreateUnoDialog(DialogLibraries.Standard.MyFirstDialog) MyDialog.Execute() End Sub
The result is following (check if the buttons really work):
OpenOffice enables to create quite complex dialogs containing multiple controls and all the OO functions are available from the OOo Basic code. Therefore some quasi-applications can be built in this way working as a specialized user front-end.
Working with text documents Till now, all the described mechanisms did not refer to any documents. In fact the main goal of writing macros is to facilitate working with different types of documents, e.g.: opening, saving, adding, editing and deleting document elements. The next example illustrates a simple document action (do not worry if you do not understand it yet): Sub Main Dim arg() oDesktop = createUnoService("com.sun.star.frame.Desktop") sUrl = "private:factory/swriter" oDocument = oDesktop.LoadComponentFromURL(sUrl ,"_blank", 0, arg()) oText = oDocument.Text oCursor = oText.createTextCursor() oCursor.gotoStart(FALSE) oText.insertString(oCursor, "What's up, doc?", FALSE) End Sub
An interesting thing is a code convention (inherited from some other products, e.g. Microsoft). The variable names start with a lower-case character followed by an upper-case character. The first letter in an identifier stands for a variable type, e.g. “o” is for object variables, “s” for strings, “n” for integers, etc. The main mechanism of OpenOffice API are services. They can be divided into context-dependent and context-independent ones. The context-independent services can be called in arbitrary moment, like inputbox() function. On the other hand, the context-dependent services are bound to some page 5 of 7
Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
environment that must be created before calling a service. As in the example above, we cannot make a script to “just insert some text”. A macro must exactly know where to place it – this is a reason for writing a set of lines before the actual string insertion (the last line). Look at the whole procedure: 1. The first step is a call to createUnoService() procedure. It calls a service meant for creating new documents. 2. The next line assigns to sUrl variable a string value representing a special structure denoting a new document to be created (in order to open an existing one you could use a simple URL, e.g. file:///c:/mydocu~1/file.odt or file:///home/username/documents/file.odt for local files or http://some.server/dir/file.odt for remote files). A call to private:factory makes OpenOffice create a new document and the other part of the string defines it as a text document. 3. Finally we create a document (object oDocument). However a document is too general for the purpose of the macro. 4. Therefore we must obtain a text object (oText) for the document. oText is the main object enabling document's text manipulations. 5. From oText we can obtain a cursor object (oCursor) working as a pointer (the text will be inserted exactly where the oCursor is). Be aware, that this object does not refer to a single location in a document – it can also mark a selection (“from a location to another location”). So if we need only to move a cursor, not to select a text range, we should call gotoStart() with FALSE argument (otherwise it should be TRUE, e.g. for replacing a selection with another text). 6. The last line is the actual text insertion at the cursor position. Another aspect of working with text is a text substitution (replacement). This function is very useful especially if we are to perform the operation with many documents, or the number of changes is large, or some complex expressions should be evaluated (in simple cases standard OO Writer functions are enough). The next example replaces all occurrences of a “dogs” word with a “cats” word: Sub Main oDocument = ThisComponent oReplace = oDocument.createReplaceDescriptor oReplace.SearchString = "dogs" oReplace.ReplaceString = "cats" oDocument.ReplaceAll(oReplace) End Sub
Notice, that in this case we do not create or open a document – a macro is invoked from within a text document – ThisComponent. ThisComponent points at the current document and it will work only if it is started from an open text document (it wouldn't work with other, e.g. a Calc document). oReplace is an object responsible for the replacement procedure, while its properties: SearchString and ReplaceString represent a searched pattern (what to replace) and a replacement, respectively. Further, a ReplaceAll() function replaces all the occurrences of a pattern. The last example of OOo Basic demonstrates how to save a text document (created by user or some automatic process) in an MS Word 97 format. Sub Main oDocument = ThisComponent sUrl="file:///C:/test.doc" Dim mFileProperties(1) As New com.sun.star.beans.PropertyValue mFileProperties(0).Name = "Overwrite" page 6 of 7
Utility Software I
lab 10
Jacek Wiślicki, jacenty@kis.p.lodz.pl
mFileProperties(0).Value = "true" mFileProperties(1).Name = "FilterName" mFileProperties(1).Value = "MS Word 97" oDocument.StoreToURL(sURL, mFileProperties()) End Sub Notice, that StoreAsURL() would act in a similar way, but it would hold a new file (its URL) as active document URL, while StoreToURL() just saves a “copy” of a document, not changing
URL.
References A complete OpenOffice SDK, API and Basic reference can be found at: http://www.kalitech.fr/clients/doc/VB_APIOOo_en.html http://api.openoffice.org/DevelopersGuide/DevelopersGuide.html http://api.openoffice.org/SDK/ http://www.pitonyak.org/oo.php (see http://www.pitonyak.org/AndrewMacro.sxw) http://www.uni-essen.de/hrz/staroffice/basic_guide/index.html
page 7 of 7
an its