[Solved] I'd like to write simple macro in OpenOffice Basic

[FrogFan]

Hello all,

Apologies if I'm in the wrong forum or section. My difficulties seem similar to those reported in this section, so I thought I'd post here. Please advise if I need to post elsewhere.

At the moment, I'm interested in writing a simple macro that un-protects a sheet, sorts the data in the sheet, and then re-protects the sheet. I tried recording a macro, including a click of the "OK" button on the protect dialog box to indicate that I don't want to set a password. When I run the macro, it insists on displaying that dialog box. I don't want that box displayed, because the person who will use the spreadsheet I'm preparing would be confused by it. So, I decided I needed to investigate writing a macro for myself, in which I'd simply re-protect the sheet directly when I was finished sorting.

The first thing I tried was to look at the code in the recorded macro. It was not helpful because it used the dispatcher function, which seems to be undocumented. Taking a cue I learned from Pitonyak's book, I decided I needed to "... get the internal UNO objects and manipulate them". So I started trying to find out how to get them. Two days later, I've decided I can't find them. I've looked at a great deal of documentation that is referenced here and elsewhere over the past two days.

I've got plenty of time available to learn and I have a preference for using OpenOffice Calc instead of Brand X for this project. Can anyone tell me where I can find documentation that will show me how to write my simple macro? I'd like to know how to do it, because I'm sure other macros will be needed in my project.

I'm not a programmer, but I've hacked a lot of code over the years, so I think I can learn.

Thanks for any help.

[RoryOF]

Pitonyak deals with UNO objects in section 10 (et seq) of "OpenOffice.org Macros explained".
and in section 3.6 (et seq) of "Useful Macro Information for OpenOffice.org"
You may also find
https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide
useful.

Do some Internet searches for code to open/close a file, then for code to protect/unprotect it, then to retrieve/rewrite data to the file. Simple then to expand that into a sort function.

Best to work with a small (almost trivial) sample file, and work up to your full project.

[FJCC]

There is an example of sorting here and an example of protecting a sheet here. As you might guess, the oSheet object in that last example also has an unprotect() method

Code: Select all

oSheet.unprotect("MyPassword")

[FrogFan]

Thanks for the quick replies, RoryOF and FJCC! I'm running with what you've given me.

I read through section 10 of "OpenOffice.org Macros explained" yesterday but didn't get anything out of it -- my own fault, I'm sure. I was looking for methods, objects, etc., that were available to me to simply use in my macro and got lost when he started discussing "obtaining a service". I'll try reading this more carefully again.

I missed the "Useful Macro Information" document in my search. I've found it and will consult it.

Finally, I never thought of just focusing on Calc Macros people have developed to see what I could find. I guess I was looking for something more generic. I'll refocus on using specific examples to gather general concepts for my project.

[RoryOF]

Frequently, in OO, the generic actions are customised to a specific application by passing or obtaining application specific parameters to or from the underlying UNO functions.

[Villeroy]

[Tutorial] Introduction into object inspection with MRI

[Zizi64]

Or you can install and use the another object inspection tool, named: XrayTool.

viewtopic.php?f=20&t=54217

[FrogFan]

Thanks again, everyone.

If I intend to use OpenOrg Basic, then if I should install Xray, not MRI. Is that correct?

[RoryOF]

MRI will work with OO BASIC.

[Zizi64]

If I intend to use OpenOrg Basic, then if I should install Xray, not MRI. Is that correct?

It is your choice... I am using the XrayTool, but you can install each of two tools for your Apache Openoffice and/or LibreOffice suite.

[Villeroy]

The thing you want to learn about is independent from any programming language. MRI comes with a menu entry in the tools menu from where you can start inspecting the current component or the current selection without any macro environment. It translates your actions into Basic, Python, Java and JavaScript code so it can be used similar to a true macro recorder. It is easier to use than XRay while having a lot more features.
XRay can be used with any langauge as well because this API does not depend on any language. The only advantage of XRay is that it works with LibreOffice. MRI does not work with recent versions of LibreOffice.

[Zizi64]

MRI does not work with recent versions of LibreOffice.

It is a new information for me. Thanks.

[FrogFan]

You guys are really helpful. Thanks! I'm going to install MRI and see where it takes me.

[FrogFan]

Hi all,

I've been able to build a macro to accomplish my objective based on the information you've given me here. Thank you for your help. I guess that means this thread is solved. I think I read somewhere that I'm supposed to indicate this by editing the subject line, so I did that.

Basically, I copied macros I found that were developed by others for protecting and unprotecting sheets and for sorting, and made some fairly trivial modifications to adapt them to my purpose. I ended up with four macros: ProtectAllSheets, UnprotectAllSheets, SortRange, and SortProtected. The SortProtected macro consists of calls to UnprotectAllSheets, SortRange, and ProtectAllSheets, and it works fine.

Then, in this thread, viewtopic.php?f=21&t=2350, Villeroy convinced me that I shouldn't be using protection at all <smiles>, so I used data validity to get rid of it and still accomplish my objective. That way, I can use Villeroy's addListrows and removeListrows and a simpler sort macro without having to remove and reapply protection. It works great.

I'm a bit disappointed that I didn't learn much from this exercise. I was hoping to figure out how to use MRI to understand the methods, data, objects, etc. that are available to me when writing a macro, and then how to make use of them. It's clear I don't yet know enough about how things are organized to make effective use of MRI. I'll keep trying to figure it out.

Anyway, thanks for the help. I'm off and running!

[Villeroy]

For an experienced programmer all this is nothing special. Just another complicated API to control another complicated software. First you need to learn programming in general. Then you can work with complicated APIs. Copying macros is like copying poems and adjust the rimes at the end of the lines. It may "work" to some extent but it is not something you would perform in public. Today's computer users are too far away from abstract knowledge. Without studying books (yes, more than one book), without learning a little bit of theory and a lot of good practice you babble incomprehensible magic code lines like a parrot.
Regarding office macros in general, I observe that another group of users do have the programming skills while being more or less ignorant to their office suite which leads to very absurd solutions to problems that would not exist for somebody who uses the office suite as intended. Consequent usage of templates and styles plus a dozend of keyboard tricks could eliminate huge libraries of silly VBA code that has been copied and pasted for decades without compatibility to any product other than MS Excel. I know quite well how to write office macros and how to "translate" VBA code but I also learned how to get rid of misguided technology.

[FrogFan]

Hi Villeroy,

I appreciate your insight and wit. Thank you!

I'm certainly not an experienced programmer, but I do know something about programming in general. As a graduate student and faculty member years ago, I wrote lots of pretty decent programs in Fortran (!). I also learned, and taught to graduate students, a simulation language called Simscript. In retrospect, it seems like Simscript was an early "Object Oriented" language, but they didn't call it that then; Object Orientation was a term that got introduced years later. I stopped writing programs over 30 years ago, and I haven't kept up with the details since then, so I'm way behind.

I'm not sure what books to read, or, frankly, whether it's worth it for me to do so <smiles>. Regarding OpenOffice macros in particular, I'm trying to figure out where to look for stuff. For example, suppose I wanted to set a print area based on the values of some variables I calculate. Try as I might, I can't find any reference to "print area" anywhere in MRI. I'm sure I'm not looking in the right place, but nevertheless I can't find it. Can you point me somewhere that will help me know where to look for stuff like this?

Anyway, I'm in good shape on this project, and I appreciate your help here and elsewhere on this site.

Thanks again!

[Villeroy]

The developers guide includes some 200 PDF-pages where fundamental concepts are introduced. Services, interfaces, structs, enumerations, simple data types and the holy trinity of controller, frame and model. The other 800 pages describe the gory details in these terms. There is also a chapter on Basic and its simplistic IDE. For anybody who is familiar with any scripting language, the F1-help describes everything you need to know about this particular Basic dialect. The F1-help is the full language documentation and it is fairly sufficient. You may notice that this language has almost no functionality other than accessing the UNO-API. It is a structural language in a highly object oriented environment with too limited string and array functionality.
If you understand how UNO-objects are instanciated, how to access properties and how to call methods with the right types of arguments, then you should be able to use the very same API with Basic, Python, JavaScript, Java, Java Beans (scripted Java) or even C++. You always talk to the same thing in the language of your choice and if you are familiar with Python you can use Python just as well as Basic plus all the useful libraries and objects provided by a mature programming language. Recently an LO developer added some promising improvements to the Python bridge in order to make Python programming easier than Basic.

Without an object inspector you are lost anyway. I use to recommend MRI with AOO and XRay with LO.

[hanya]

MRI does not work with recent versions of LibreOffice.

Do you mean on LibreOffice 5.2.X? I can use MRI 1.3.3 on LibreOffice 5.2.0.4 with Xubuntu 16.04.1.

[Villeroy]

MRI does not work with recent versions of LibreOffice.

Do you mean on LibreOffice 5.2.X? I can use MRI 1.3.3 on LibreOffice 5.2.0.4 with Xubuntu 16.04.1.

I did not notice that you published a new version on the AOO site. Indeed, MRI 1.3.3 downloaded from extensions.openoffice.org can be added to LO 5. Right now I run LO 5.1.4 on Windows with MRI 1.3.3. Thank you very much.