Advice on learning OOo Basic

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Advice on learning OOo Basic

Post by mike phillips »

I have a reasonable amount of coding experience, having used C, Nokia QT and VBA. I am finding the whole experience of 'learning' to write macros in OO quite frustrating. I have downloaded inumerable pdfs and docs to try and help, but none give simple guidance on things like opening a calc sheet and making it active. Most of the Google hits on the topics seem to take me to forum posts where the topic is far more complicated than I need. I cannot believe it should be this difficult! I really should not be having to come to a forum like this, helpful, indeed, as it is, to find out. (I would, incidentally, like to find out how to open the worksheet 'not visible').

Can anyone recommend a good guide to macro writing please?

In the mean time, after a few hours of fruitless research where many of the code examples I found gave me Basic 'syntax errors, I found how to open a calc sheet using Doc = starDeskTop.loadComponentFromUrl (Url, "_Blank", 1, Array()). That should have been so easy to find!

Now I wish to work on the opened sheet, and I do understand that unlike VBA, OO requires I activate the sheet EACH time I need to do so - but can I find how to make a sheet active? No.

VBA Code:

Code: Select all

Workbooks(openfile).Activate
Worksheets(sheetname).Activate
OO?

While I am posting, please, I need to autorun a macro which will populate a listbox 'Listbox1', on a Dialog 'Dialog1' with an array

Code: Select all

MyArray2 = Array("WHOLE YEAR", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER")
- any pointers please?
Last edited by mike phillips on Sat Feb 18, 2012 3:13 pm, edited 1 time in total.
OpenOffice 4 on Windows 7
User avatar
floris v
Volunteer
Posts: 4408
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Advice on learning OO Basic

Post by floris v »

I have some experience with MS Word Basic, Delphi and more but I soon gave up on OOo programming. I don't want to know what the developers were smoking when they invented this, but a macro language should be easy to learn so that the average user can automate some of his/her work. That's after al what macros are for.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Advice on learning OO Basic

Post by Villeroy »

Downloading numerous pdf documents does not help. You need to read and understand only the relevant ones explaining the fundamental stuff (services, interfaces, types, model, view, frame). Then you need some object inspector to navigate through the hierarchy of services and interfaces. I use to recommend the MRI extension.

Search for anything by DannyB on oooforum.org, for instance: http://www.oooforum.org/forum/viewtopic ... ller+model

If you want your office suite to be a development environment I would recommend MS Office at any time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OO Basic

Post by mike phillips »

Downloading numerous pdf documents does not help. You need to read and understand only the relevant ones explaining the fundamental stuff (services, interfaces, types, model, view, frame). Then you need some object inspector to navigate through the hierarchy of services and interfaces. I use to recommend the MRI extension.
No! as floris says above, macros are designed to help automate procedures and functions and should not require a pipe-full of hash or whatever you folks seem to smoke to learn. MRI extension? Great stuff - needs some sort of 'python binding' - not into tying up snakes, I'm afraid.
Search for anything by DannyB on oooforum.org, for instance: http://www.oooforum.org/forum/viewtopic ... ller+model.


Oh yes! Been there (one of the many things I have downloaded), and every single page is a whole page of green writing which actually explains very little. Not the 'help' I seek. I am sure he is a very clever man, though
If you want your office suite to be a development environment I would recommend MS Office at any time.


No again - I would not be here sir, trying to understand this concept, if it were that simple to solve!That was indeed the start of my journey into OO, and I had hoped to move along the route, not duck straight back out

Now, has anyone got an easy to understand guide, preferably with code snippets, to HELP solve my queries? Is this thing SO impenetrable?
OpenOffice 4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Advice on learning OO Basic

Post by Villeroy »

Sir, Yes Sir, it is SO impenetrable. It is very close to the internal API used by the source code. There is almost no sugar coating.
Having a default installation of this suite you don't have to bother about Python bindings. Python is one of the integrated programming languages for macros and extensions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OO Basic

Post by mike phillips »

All noted. I will wait in the hope that someone who does not find it so impenetrable comes along.
OpenOffice 4 on Windows 7
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Advice on learning OO Basic

Post by RoryOF »

The definitive guide to Macro Programming for OpenOffice is
http://www.pitonyak.org/oo.php
Make your way up around that site, it is full of information, but may tell you more than you wish to know. There is also
http://wiki.services.openoffice.org/wik ... ASIC_Guide
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Advice on learning OO Basic

Post by rudolfo »

If you really don't want to deal with Python or if you have de-selected the Python bindings when installing OOo (I did the same initially and also did not install the JRE, but I changed my mind later on) you can use
Xray from Bernard Marcelly's website instead. It can do nearly the same as MRI, but uses the BASIC language. Installation might be a bit more difficult because Xray is not bundled as an OpenOffice extension.

And yes, the UNO interface (with its services and structures) is difficult to understand for someone that is used to MS VBA. But this is mostly due to the point that UNO can be used by several scripting languages and not only by Basic.

Coming back to one of your initial questions: Macro processing usually does not need to activate or de-activate something. Macro just get hold of some objects/controls/dialogs by means of an object reference and call methods of that particular object to modify it. Because this is by concept an automated process, it is hidden and only maybe at the end of the macro something may be activated, in order to help the user to continue the workflow.

Code: Select all

oSheet = ThisComponent.getSheets().getByIndex(0)
oCell = oSheet.getCellByPosition(0,3)  ' That's Cell A4, A1 is (0,0)
' manipulate the Cell's content
oCell.Value = 24.8
oCell.String = "aString"
' Check if it is empty ...
If oCell.getType() <> com.sun.star.table.CellContentType.EMPTY Then
 ...
End If
All this can be found with Xray/MRI and it is probably also in some of the common Tutorials (browse through the sticky thread at the top of this macro forum). Okay, the com.sun.star.table.CellContentType.EMPTY needs some extra effort.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Advice on learning OOo Basic

Post by Villeroy »

rudolfo wrote:Coming back to one of your initial questions: Macro processing usually does not need to activate or de-activate something.
But this is exactly what the programming style of the VBA macro recorder. And this is what most of the "VBA experts" out there try to replicate.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OOo Basic

Post by mike phillips »

Thanks Rory and Rudolfo.

For rudolfo:

I am ok with reading and writing from an opened sheet. What I am doing is to open a new calc sheet using a macro in the mother sheet, and then do the read/write the new opened sheet. All I succeed in doing is reading/writing to the mother sheet! Whether or not I need to activate or not is what I am trying to establish. I have understood from the OO guide 'porting from VBA etc'

"One general note on the difference between VBA and SB. In VBA, when an Excel object is
referenced, such as a range of cells, unless explicitly coded, the cell range is assumed to be in
the currently active Excel container, such as the workbook (ActiveWorkbook) and worksheet
(ActiveSheet). In SB, on the other hand, no such assumption is made, so each reference to a
Calc object must be fully qualified. In other words, you have to specify the workbook
[spreadsheet] and worksheet [sheet]."
"

and this is what I need to learn. In essence HOW do I 'specify'? I cannot find any guide to the code for this. Here's the simple block:

Code: Select all

Dim Url as String
Url = convertToUrl("myfile")
Dim Doc
Doc = starDeskTop.loadComponentFromUrl (Url, "_blank", 0, Array())

Dim oSheet
oSheet = thisComponent.Sheets(0)
oSheet.getCellRangeByName("A1").String = "Input Data"
all very nice but in the wrong spreadsheet :D In vba it would have gone into the newly opened worksheet myfile.
OpenOffice 4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Advice on learning OOo Basic

Post by Villeroy »

url= convertToUrl("C:\Documents and Settings\Mike\My Files\myfile.ods")
print url
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OOo Basic

Post by mike phillips »

That is not the problem.............

S'ok - managed it on my own - not really 'impenetrable', but surprised some were unable to help. Code now reads:

Code: Select all

Dim Url as String
Url = convertToUrl("myfile")
Dim Doc
Doc = starDeskTop.loadComponentFromUrl (Url, "_blank", 0, Array())

Dim oSheet
oSheet = Doc.Sheets(0)
oSheet.getCellRangeByName("A1").String = "Input Data"
and it works - simples. On I go. Now - to fill a listbox?
OpenOffice 4 on Windows 7
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Advice on learning OO Basic

Post by rudolfo »

mike phillips wrote:S'ok - managed it on my own - not really 'impenetrable', but surprised some were unable to help. Code now reads:

Code: Select all

Dim Doc
Doc = starDeskTop.loadComponentFromUrl (Url, "_blank", 0, Array())
Compare your code that you found as a solution to my statement from above:
rudolfo wrote:Macro just get hold of some objects/controls/dialogs by means of an object reference and call methods of that particular object to modify it.
Now why do you declare a Dim Doc variable? Why don't you simply use:
StarDesktop.loadComponentFromUrl( ... ) without assigning it to the Doc variable?
Because the assignment creates a reference to the newly opened Calc object in this variable and because you want to modify this reference to the Calc document. How should we guess that you want to modify the newly opened document if your previous code was using ThisComponent in the following lines? So that's for explaining the lack of help that you are seeing. And as a second thought "help is not always writing down a solution for someone", but more often to open his mind. And after all you have found a solution, so this thread was helpful.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Advice on learning OOo Basic

Post by Villeroy »

mike phillips wrote:I have a reasonable amount of coding experience, having used C, Nokia QT and VBA.
Unbelievable. VBA coders are no programmers at all when they rely on default objects, code completion and macro recorder.
VBA degrades to cargo cult programming.
http://user.services.openoffice.org/en/ ... 20&t=46549
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OOo Basic

Post by mike phillips »

-Now why do you declare a Dim Doc variable? Why don't you simply use:
StarDesktop.loadComponentFromUrl( ... ) without assigning it to the Doc variable?
Because the assignment creates a reference to the newly opened Calc object in this variable and because you want to modify this reference to the Calc document. How should we guess that you want to modify the newly opened document if your previous code was using ThisComponent in the following lines?
- rudolfo - ignoring the preceding rant, the answer to the first is simply because I could not see how to recall that very document to work on it later, and assigning it to a variable sorted that: and to the second - the use of 'ThisComponent' to which you refer was taken from your own code sample (not mine) at 5:28/18/2/2012, not mine, so I don't know the answer to that!
OpenOffice 4 on Windows 7
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Advice on learning OOo Basic

Post by kingfisher »

'ThisComponent' refers to the active document. To apply your code to the intended document you should have continued using the variable previously defined. I believe you fixed that issue but perhaps it is necessary to clarify it.
Apache OpenOffice 4.1.9 on Linux
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OOo Basic

Post by mike phillips »

Thanks, kf - I assume therefore that with 3 or 4 workbooks in play I allocate then to Doc1/Doc2 etc or similar?
OpenOffice 4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Advice on learning OOo Basic

Post by Villeroy »

kingfisher wrote:'ThisComponent' refers to the active document.
But if he spent so many hours downloading and reading documentation, how can ThisComponent be unclear by any means.I'm sure he does not even know how to run step mode and observe a variable, not in the StarBasic editor and in the VBA neither. How many of VBA experts do we want to talk through this way?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OOo Basic

Post by mike phillips »

Villeroy - I think you probably need stronger glasses? Please note 'ThisComponent' was NOT my input into this thread and I have NEVER expressed any 'doubts' over the function. Need any help with QT?
OpenOffice 4 on Windows 7
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Advice on learning OOo Basic

Post by kingfisher »

I've just thought of a related point. Placing the line below at the top of a module achieves two things (or perhaps more). First, you will get an error message if you use a variable not previously declared. Secondly, you will receive an error message if you try to define a variable which is already defined. To do that I recall you can use 'Redim'. I always use a new variable.

You posted while I was typing this.

Yes, use different variables or 'Redim' (although I'm relying on memory for that).

If you want to use variables in more than one macro, you can use, instead of 'Dim', 'Private', 'Public' and 'Global'. Memory serves me badly on those subjects as well. I mention them in case you want something like that; you will know what to look for.

EDIT: Of course 'ThisComponent' is not a variable.
Last edited by kingfisher on Sun Feb 19, 2012 12:28 pm, edited 1 time in total.
Apache OpenOffice 4.1.9 on Linux
mike phillips
Posts: 118
Joined: Wed Feb 15, 2012 9:35 pm

Re: Advice on learning OOo Basic

Post by mike phillips »

Thanks again, kf - the variable scopes for decs are common I think to most O O languages. I assume it is 'Option Explicit' to which you refer? An invaluable crutch indeed.
OpenOffice 4 on Windows 7
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: Advice on learning OOo Basic

Post by kingfisher »

Yes, thanks. I meant 'Option Explicit'.
Apache OpenOffice 4.1.9 on Linux
Post Reply