Macros & Basic with OpenOffice Calc E-Book

Rémy Lentzner

• Herausgeber: Remylent
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2022

Seitenzahl: 84

Beschreibung

This book focuses on the macros you can record with OpenOffice CALC. A macro is actually a BASIC procedure that you can modify in a specific programming environment. It enables you to create customized applications to control the objects interaction in the spreadsheet accurately. You will learn how to develop a dialogue box to manage information easily. 

BASIC programming is relatively simple to understand and all the exercises of this book will help you to achieve manual actions automatically.


ABOUT THE AUTHOR

Rémy Lentzner has been an IT trainer since 1985. Specialized in mastering office automation tools, he supports companies in the professional training of their employees. Self-taught, he has several computer books to his credit.

Leseprobe

REMY LENTZNER

Macros & Basic with OpenOffice CALC

French original title: Les macros avec OpenOffice CALC

EDITIONS REMYLENT, Paris, 1ère édition, 2022

R.C.S. 399 397 892 Paris

25 rue de la Tour d’Auvergne - 75009 Paris

[email protected]

www.REMYLENT.FR

OpenOffice is a registered trademark of Apache Software Foundation.

ISBN EPUB : 9782490275816

The Intellectual Property Code prohibits copies or reproductions intended for collective use. Any representation or reproduction in whole or in part by any means whatsoever, without the consent of the author or his successors in title or cause, is unlawful and constitutes an infringement, pursuant to articles L.335-2 and following of Intellectual Property Code.

This book is dedicated to Isabelle and Tama

I could not have written it without their support, advice, encouragements and proofreading.

Graphic illustration : Anna LENTZNER

In the same collection

Improve your PivotTables with Excel

Upgrading your skills with Excel

Improve your skills with Google Sheets

Programming macros with Google Sheets

Getting started with HTML

Getting started with JavaScript

Getting started with PHP & MySQL

Google Docs

Google Slides

Google Gmail

Macros & VBA with Excel

Getting started with WordPress

Getting started with Programming

Getting started with Numbers

Getting started with Pages

Getting started with Keynotes

Upgrading your skills with Word

Upgrading your skills with PowerPoint

Upgrading your skills with Outlook

Getting started with OpenOffice Calc

Getting started with OpenOffice Writer

Getting started with OpenOffice Impress

Getting started with OpenOffice Base

WWW.REMYLENT.FR

TABLE OF CONTENTS

Chapter 1 Macros with CALC

1.1 General information about macros

1.1.1 Recording a macro

1.1.2 Running a macro

1.1.3 Modules and procedures

1.1.4 The local variables window

1.1.5 Starting a macro using a button

1.1.6 The Form Controls

Chapter 2 The BASIC

2.1 Introduction to BASIC

2.1.1 An object-oriented language

2.1.2 Finding information about API

2.1.3 The programming toolbar

2.1.4 Comments

2.1.5 Variables

2.1.6 Variable types

2.1.7 The variables scope

2.1.8 Operators

2.2 Control structures

2.2.1 Conditional structures

2.2.2 The loop structures

2.3 The interactive dialogue boxes

2.3.1 The MsgBox function

2.3.2 The InputBox function

2.4 Functions

2.4.1 The syntax

2.4.2 Passing parameters in a procedure

2.4.3 OpenOffice functions and procedures

2.4.4 String functions

2.4.5 Date functions

2.5 Workshops

2.5.1 Handling cells with conditions

2.5.2 Emptying cells automatically

2.5.3 Printing with Layout settings

2.6 Files and folders

2.6.1 Showing file names

2.6.2 Creating and deleting a directory

2.6.3 Reading the contents of a text file

2.6.4 Exporting a file content

2.6.5 Reading a file and writing to cells

2.7 The Error Handler

Chapter 3 UNO Services

3.1 The object hierarchy

3.1.1 The ThisComponent object

3.1.2 Displaying the number of sheets

3.1.3 Displaying the names of all the tabs

3.1.4 Renaming a sheet

3.1.5 Creating a new tab at the end of the tabs

3.1.6 Creating 12 sheets from December to December

3.1.7 Deleting a Sheet

3.1.8 Activating a sheet

3.1.9 Hiding a sheet

3.2 The UNO project services

3.2.1 Bolding a group of cells

3.2.2 Some UNO formatting expressions

3.2.3 Selecting a sheet before applying formatting

3.2.4 Finding RGB color codes

3.3 Copying and selecting services

3.3.1 Copying and pasting a range of cells

3.3.2 Special copy and paste

3.3.3 Copying cells from one sheet to another

3.3.4 UNO parameters for moving and selecting

3.3.5 Hiding a column and two rows

3.3.6 Copying two rows

3.4 Examples of selecting and moving

3.4.1 Selecting a range of cells vertically

3.4.2 Selecting an entire range of cells horizontally

3.5 Searching for a value

3.5.1 Ctrl F key simulation

3.5.2 Finding the last row

3.6 Managing spreadsheets

3.6.1 Saving a current spreadsheet

3.6.2 Saving with another name

3.6.3 Opening a document ODS

3.6.4 Creating a document ODS

3.6.5 Closing a document

3.6.6 Activating a spreadsheet

3.6.7 Enumeration of documents

3.6.8 Retrieving data from multiple spreadsheets

3.6.9 Running a macro at startup

Chapter 4 Dialogs

4.1 Creating a dialog box

4.2 Manipulating Controls

4.2.1 Inserting a title

4.2.2 The test mode On/Off

4.2.3 Inserting a text box

4.2.4 Inserting buttons

4.3 A database in the spreadsheet

4.3.1 Inserting a call button

4.3.2 The dialog calling procedure

4.3.3 The libraries

4.3.4 Linking the button to the calling procedure

4.4 Button programming

4.4.1 The close button

4.4.2 The Add button

4.4.3 The Search button

4.4.4 The Delete button

4.5 Other controls and properties

INTRODUCTION

INTRODUCTION

Welcome to OpenOffice, the office suite that you can download at the address: https://www.openoffice.org. The software is available on many platforms. At the time of writing this book (under Mac), the latest stable version is 4.1.13. As changes appear from time to time, do not forget to check the current version.

OpenOffice contains modules such as the spreadsheet CALC, the word processing WRITER, the database management BASE, the presentation management IMPRESS and the programming BASIC language with which you can program macros and customized applications. Because the software is free, you can install it on many computers.

This book is intended for people who want to create macros with CALC. A macro is a record of your manual tasks, but the result is a BASIC procedure that you can modify in a specific programming environment.

This book is divided into 4 chapters.

Chapter 1 shows how to create a macro, record it, run it then modify it.

Chapter 2 outlines the fundamentals of the Basic language such as procedures, variables, control structures and functions.

Chapter 3 covers the programming of CALC objects. You will learn about the API and UNO application programming interfaces that enable you to manipulate cells, values, sheets, properties and other objects in the spreadsheet.

Chapter 4 explains how to create dialogue boxes. You will manipulate and program text boxes, push buttons, as well as the many properties needed to create dialogues.

I hope that reading this book will interest you and enable you to create customized applications.

Do not hesitate to contact me at [email protected] if you have any comments or questions about this book.

I will be sure to answer you.

Enjoy the book.

The author

Chapter 1 Macros with CALC

This chapter deals with macros inside the CALC spreadsheet. You will learn how to create, save and execute them to make your manual actions automatic. A macro is also a BASIC procedure that you can modify in a programming environment. You will study the language and its various features.

1.1 General information about macros

A macro or macro command is a sequence of keystrokes that performs an operation on one or more parts of the spreadsheet.

For instance, to hide a column, you place the pointer in a column and you perform the actions Format / Column / Hide. If you consider that this operation is often repeated in your daily work, you can memorize this sequence of manual actions using a macro.

Another example is the printing of a part of a table. If you have to print a specific group of rows every month, you can record the whole manual process.

The macro feature makes it easier to perform repetitive tasks. You can record as many macros as you like.

With OpenOffice, a recorded macro automatically creates a suite of code in the BASIC language.

Figure 1.1 shows an INCOME.ODS document containing some accounting data and formulas.

Figure 1.1 : Formulas inside a document

Workshop : Creating a macro to format the whole table with a Calibri font and a size of 10.

1.1.1 Recording a macro

The following shows how to create a macro:

Select the cells.Tools / MacrosRecord Macro

Figure 1.2 : Recording the tasks and waiting to stop

Perform all the operations manually to record the macro.Click on the Stop Recording button to stop the macro.

Figure 1.3 shows the dialogue box that displays the existing macros, their locations and the different buttons than can help you to manage macros.

Figure 1.3 : The macros management

Click on the New Module button.Enter a module name then finish with the OK button.Close the dialogue box. The creation of the macro is completed.Enter the name of the macro in the Macro name box then finish with the Save button.

A macro is always stored in a module that is a macros container.

If a module already exists, it is not necessary to create a new module. Select the module you are interested in before saving the new macro.

After stopping the macro, OpenOffice will display the OpenOffice BASIC Macros dialogue box in which you specify a macro name and a particular location.

You can save a macro in several places:

In the My Macros library. Here, you can use the macros for other documents.In the OpenOffice Macros library. These pre-existing macros are used by OpenOffice.In the INCOMES.ODS document. The macros saved here are only available in this document.

In brief, you can store macros in a public container, a system container or a local container.

1.1.2 Running a macro

Once the macros have been created, you can run it with the following:

Tools / Macros / Run macroSelect the library.Choose the macro name then click on the Run button.

1.1.3 Modules and procedures

A procedure is the structure that is automatically created when you record a macro. It starts with the keyword Sub and ends with the keyword End Sub. A procedure is always stored in a module, i.e. a container of procedures. When the macro is completed, OpenOffice will save it in a procedure called main if you don't rename the macro.

You can always edit a macro with the following:

Tools / Macros / Organize MacrosOpenOffice BASIC

Figure 1.4 : Editing a macro

Select the macro to edit.Edit

OpenOffice displays the BASIC programming environment immediately.

Figure 1.5 : Programming environment

By default, the name of the procedure is Main. If you want to rename this name, do not put spaces in it. Spaces are also forbidden in the module names.

At any moment, you can create new personal libraries that will contain modules or dialogues (userforms).

Note. A macro is a procedure written in BASIC. Once it is written, you can run it (Tools / Macros / Run Macro).

If more than one macro has been recorded, all of them will be placed underneath each other. Each macro begins with Sub and ends with End Sub.

1.1.4 The local variables window