Programming resources

[Jimster2012]

I am looking for online macro programming resources to Openoffice and Libreoffice. I am familiar with Basic programming in general but am getting very lost when trying to learn openoffice macro programming. In particular spreadsheet macro programming at the moment. At the moment I am trying to do something that should be very simple and that is when I open the spreadsheet I want it to go to a particular cell and then allow me to input numeric data into some into fields or maybe even a dialog box macro would suffice and then add the totals of the numeric values and enter them into the particular cell and then close the cell. I have purchased a book called OpenOffice Spreadsheet programming and OpenOffie.org Macros Explained by Andrew Pitonyak. Book books get to a certain point about UNO services and Interfaces and this is where I get lost. The middle of each book seems to have gotten lost someplace. They seem to go from where I can follow them to just jumping by leaps to the totally complex. I do understand that I have to create some sort of UNO service like Dim oDoc as Object and then somehow create a UNO service like createUnoService ("com.sun.star.table") and then somewhere issue a function like getCellByPosition (11,15). Also, things a variable like Dim oDoc as Object but then in the reference propgramming tutorial it will say oSheet = oDoc.sheets(0). Why is the .sheets needed and what is that for as that I cannot find explained anywhere?

Frustrating because I know I could learn this if there was adequate training somewhere for this. Either free or an online course I could pay for. I can't find anything classes anywhere on openoffice or libreoffice. With the popularity of openoffice I just find it hard to believe that nobody has tapped into a fee based programming market for either openoffice or libreoffice.

[mriosv]

Welcome to the forum Jimster2012,
Have you looked in the Macros and UNO API in this forum?
viewforum.php?f=20

[kingfisher]

Here are a few links to get you started.

Andrew Pitonyak's OpenOffice page I recommend the pdf versions of documents. The documents are more up to date than his book.
Basic Guide on the wiki
MRI - UNO Object Inspection Tool
StarBasic Guide

I still use Bernard Marcelly's Xray tool. Andrew Pitonyak also has a macro for inspecting 'objects'. Try the MRI extension. That can generate code as explain here.

[RPG]

Hello

What you describe is really the most important part of understanding the API. It is not a problem of the programminglanguage you use. A good understanding of the API did cost me several years but I think for most persons it will cost two years.

The books do not describe that part. They learn you how you can use services and interfaces and then use most of the time events. Those events export services and or interfaces and most of the time when you define an event then you knew I need that service. BUt in the beginning this part was really difficult for me to understand. When you understand this then it is also more easy to work with it. My idea was that the macros are mucht more easy.

I have the idea you can better start with designing a database. Do also learn what you can do with OOo before starting macros. A good understanding about OOo make writing macro much more easy and most of the time you can do it without macros.

Romke

[rudolfo]

I don't know about the exact details in the books that you mentioned. But in general for doing basic things in Calc you will not need frightening CreateUnoService("com.sun.star.something") calls. You will mostly start with an instance of your spreadsheet document
oDoc = ThisComponent
and then work with the child objects that your document has: Sheets that are available: oDoc.getSheets(), and most other objects can then be accessed through a getByName or a getByPosition methods. All the available methods and properties can be shown by MRI or Xray as explained in the previous posts.

[Jimster2012]

rudolfo, I am glad to hear that I do not maybe need a thorough understanding createunoservice calls right from the start but the books I have seem to go there pretty fast. So my question is where do I learn about "ThisComponent" and so forth and does oDoc need to be the necessary name of a Doc object or is the "o" before Doc just a reminder that you are working with an object"?

[Jimster2012]

mriosv, I have taken a look at the "macros and UNO" section but I think it would take me a while to figure out what most of the example programs are about or how they work. I can learn easier if I start learning from scratch on what I am trying to do. Do you have any suggestions on how to use this topic as a teaching aid for myself.

The spreadsheet I am trying to create is a cash register reconciliation report for balancing the register totals for a mom/pop retail store. So on part of the spreadsheet I want to enter the totals from the daily cash register receipt tape. This is the easy part as you just enter in the amounts from the register tape into the corresponding cells on the spreadsheet. It is in the "actual" cash and check counts that I want to program the macro for. My vision is sort of like a what a bank teller does and that is when you first go into the spreadsheet it will take you into the "actual" cash count cell and then a box will open asking you how many of each type of bill denomination you have and then, for example it will ask how many twenties do you have and you answer like 10 and then it will put $200.00 in a field, then how many tens, fives and so on and then total the cash and input that into the case sale on its' own. Then do the same thing with checks and so forth. I have the spreadsheet done but I want to further automate it the best I can, impress my supervisors, get a raise and live happily ever after, lol.

I know this is something most here could do in their sleep. I just need a push in the right direction as to how to get the learning processes flowing for this. Not asking for anybody to do this for me as I want to do as part of my learning process and I think this would be fairly simple. I thought about doing it all in BASIC but then I would actually be programming a spreadsheet program and that wouldn't make sense since the openoffice and libreoffice have done this for me and I designed the layout of the spreadsheet from the form we currently fill out by hand.

[Jimster2012]

Romke,

I also want plan on doing a database but as far as I can tell right now it wouldn't need any sort of macro programming. I guess I could do the whole spreadsheet thing with a database, I just thought that doing a spreadsheet made more sense for this task and my whole idea here is to learn something. Are yyou saying that I do it with the database first and then learn how it i building the code somehow?

[Jimster2012]

Kingfisher, should I download both MRI and Xray? Your post makes it sound to me like MRI is an extension to the Xray tool.

[kingfisher]

I use both. MRI refers to the api site on the internet whereas the Xray tool uses an SDK installation on your computer.

I am still getting used to MRI. One clear advantage of Xray (or so it seems thus far) is that you can use it in a macro to inspect a variable that you have defined. You can also assign menu commands, icons and keyboard bindings to Xray commands as with other macros.

[rudolfo]

Jimster, you can take it easy. You don't need to explicitly reply to all of the volunteers directly. It is usually good enough if you state where you got so far with the help of the previous posts.
Regarding your question about ThisComponent and oDoc: ThisComponent is an internal variable that is (a reference of) the current open document. Either a spreadsheet or a Writer document (or a Impress presentation, ...). Typically programming style is to store this object variable into a user defined variable oDoc where the o stands for object and the Doc for Document. The reason to do so is that ThisComponent might change throughout the macro if your macro opens other documents, that will get the active focus. In that case you would loose the reference to the initial document and hence you store it in your own variable first to be on the safe side. Of course the variable name is completely your own choice, but mostly it helps to follow coding standards.

Coming to MRI and Xray, you will need only one of them. Xray has to be started explicitly in your macro code with a line Xray a_UNO_object, where a_UNO_object might be ThisComponent or any other UNO object. MRI can inspect what you have currently selected in your OpenOffice Application, a cell or a CellRange on a Calc Sheet or a paragraph or word in a Writer document. And it also has the option to generate Code for you (this is a bit hidden, but this thread mentioned by kingfisher explains how to get hold of it).

Building a database is not a simple task, it needs quite a lot of knowledge. But it may be more appropriate
for your case because you have the same type of data every day. Database force the data to be more structured then spreadsheets. You can start with a spreadsheet and a few macros. But if you find yourself starting to code a lot of macro code with several workarounds this is surely an indication that you should reconsider to use a database.

[Jimster2012]

Thanks for the advice. I have built relational databases before but have never had to actually program one with macros. I know you have to take the time to set them up properly and design correctly. I guess I was just wanting to learn to program a little bit for myself. And maybe the database is what I may end up doing or never get done what I am trying to do with the spreadsheet.

Still somewhat confused though as to Xray and MRI. How does Xray know I need to use ThisComponent if I do not tell it or is it somehow supposed to tell me I need to use ThisCompnent somehow? MRI sounds like if I selected a range of cells it would program the macro for this? This sounds weird because if I have that part of the spreadsheet built already why would I need MRI to build the macro for it. Are there any examples that you know of that would show me what these tools can do? In the link I went to about MRI it doesn't explain what really MRI does, just how to access other screens on it.

I know I am totally too much of a beginner to be in the forum. I find I learn better from books and it is still seems such a shame that some sort of online course isn't offered. I know there is a lot of online sources for me to read through and the learning is there to be found, just searching for the right place to start that makes sense to me.

I do appreciate everyone's responses to me.

[kingfisher]

I have added these to the Standard library module 1:

Code: Select all   Expand viewCollapse view

Sub LoadingLibraries

BasicLibraries.LoadLibrary( "XrayTool" )

End Sub

Sub InspectDesktop

Xray StarDesktop

End Sub

Sub InspectDocument

Xray ThisComponent

End Sub

I have linked the second and third to menu commands in the Tools menu tree. I have also linked the third to a tool bar icon. Use Tools > Customise > Menus | Tool bars.

A typical case where you might use it in a sub you are in the process of building might be:

Code: Select all   Expand viewCollapse view

Option Explicit

Sub somethingRows
Dim oDoc as object, oSelect as object, oRows as object
oDoc = ThisComponent : oSelect = oDoc.CurrentSelection : oRows = oSelect.Rows
Xray oRows
End Sub

In practice, you would not need to use special code to Xray something so simple because you can step (with Xray) from one element to the next starting with ThisComponent. I've just used it to illustrate how you can Xray something specific.

[rudolfo]

Please go through that other thread that Kingfisher and I have referenced above. The crucial statement there is:

OK, you can not record any actions in the office GUI, but your actions in the MRI tool gets recorded.

This means you start MRI from the Tools menu, the lowest item and then click on any property or method and MRI will open this property or execute this method and records it. So it is good to walk through the hierachy of UNO objects (for example get from a cell to the spreadsheet containing this cell and from there to the overall document and to the second spreadsheet of this document). Also it is often overlooked that MRI installs a complete additional section on how to use and what to do with MRI into the online help of OpenOffice. In general macro programming consists of two parts:
1) Find the right (UNO) object (a cell, a CellRange, spreadsheet, ...)
2) Do the appropriate thing with that object: MRI and Xray show you the available methods (and those methods are linked to their API documentation if you need to have some details and the name is not self-explaining)

Well, and sometimes you will have to modify a cell, but the method to use is not a method of the cell itself, but it is a method of the parent object. Deleting a cell is typically not done as a method of the cell, but as a method of the sheet that contains that cell.

[Jimster2012]

OK, I start Libreoffice then I go to tools. MRI isn't listed unless I select addons, then there is both a listing that says MRI and MRI <- selection. If I select MRI it brings up a windows of properties, methods and so forth but that is about as far as I can work it. I get no explanation of anything and it doesn't seem to be recording anything or show what it does. I will read the documentation further to fully understand how to work it. Reading the documentation and it says that I select the target somehow. When I try and select something like a method the selection windows keeps opening and says void. It doesn't seem to be working like you explained.

I will keep at it though.

[kingfisher]

If you hover over the line below the horizontal scroll bar, the mouse pointer will change to a + type of cross. You can then move the top pane upwards. The panel then uncovered records selections you make. For example, double-clicking on 'CurrentSelection' will read the properties etc of the current selection and write this in the covered panel:

Code: Select all   Expand viewCollapse view

Sub Snippet
  Dim oCurrentSelection As Object

  oCurrentSelection = ThisComponent.CurrentSelection
End Sub

Double-clicking on the method 'createTextCursor' changes the code to this:

Code: Select all   Expand viewCollapse view

Sub Snippet
  Dim oCurrentSelection As Object
  Dim oObj_1 As Object

  oCurrentSelection = ThisComponent.CurrentSelection
  oObj_1 = oCurrentSelection.createTextCursor()
End Sub

Reading a 'method' often produces no information. That is why I use the 'method' (in a sub I call tryThis) to create an object which I can then xray. I have a link to the sub in the Tools > Macros menu.

I think I only started to get the hang of this when I began writing code. It was extremely frustrating. Tools like MRI and Xray help a great deal but you probably need to grasp the basics (no pun intended) first. The introductory parts of Andrew's macro documents and the other guides mentioned are requisite reading probably before you can begin. In Andrew's main macro document have a look at '6.5.3. Select a Cell'.

[rudolfo]

The online help of MRI explains how you can run MRI from within your macro code:

Code: Select all   Expand viewCollapse view

Globalscope.BasicLibraries.LoadLibrary( "MRILib" )

Dim oMRI As Object
oMRI = CreateUnoService( "mytools.Mri" )
oMRI.inspect( MriTargetObject )

I haven't used Xray recently but I remember that there is a significant difference: If Xray is called from a macro the execution of the macro is stopped at that point while with MRI only the MRI window pops up and the macro will continue to execute until it ends.

If you are only interested in the properties of an object you can also "simply" use the OOo Basic Debugger to inspect UNO objects. See the attached screenshot on how to do this. You have to set a breakpoint in your code and also have to specify a variable (typically an UNO object variable) that you want to inspect in the Watch field. In my case this is oComp. If you have reached the breakpoint you can click on the [+] before the oComp to see its properties.

OOo Basic Debugger with a watchpoint on an UNO object

[kingfisher]

Thanks rudolfo. I will obviously have to scrutinise MRI properly. Its library loads on start up (with the correct command) and it will inspect the desktop.

[rudolfo]

One of the advantages that I have seen in MRI (apart from the code generation) was the ability to get it working without the need to write a macro through the menu commands in Tools -> Add-Ons. I was assuming a lot of magic behind this, but thanks to Kingfisher's short Xray macros linked to toolbar icons and menu commands i have learned that this is quite easy to arrange for Xray, as well. It actually makes me think that the two MRI menu entries are implemented in a similar way. And because MRI is an extension this macros and menu commands are setup automatically during installation of the extension. Nice for the novice user, but quite frankly the target group of Xray and MRI are rather power users or macro programmers, so they should be able to create their custom menu commands rather quickly.

[Villeroy]

One of the advantages that I have seen in MRI (apart from the code generation) was the ability to get it working without the need to write a macro through the menu commands in Tools -> Add-Ons.

It prompts for the exact item when you call getByName and getByIndex. Getting items by name or by index in XRay requires a few lines of code.

MRI it brings up a windows of properties, methods and so forth but that is about as far as I can work it. I get no explanation of anything

A double click does the trick.

When I try and select something like a method the selection windows keeps opening and says void.

If the method returns nothing then it is "void".
Trying to learn programming within the context of this office suite (or any similar program) is like taking the first flight lessons on Airliners. All this is incomprehensible unless you have some expertise and experience in object oriented programming. The Basic language makes things even worse. Basic is like a folding chair in the cockpit of that airliner.

[hanya]

Nice for the novice user, but quite frankly the target group of Xray and MRI are rather power users or macro programmers, so they should be able to create their custom menu commands rather quickly.

Most people use MRI on their environment which is used on daily use also. So I do not want to add any entries for MRI on main menu or toolbars. I know addon's configuration for menu entries and toolbar entries can be provided in other package, so it might be good idea for me.
A custom command needs its target object and it have to be get from a document object or created with service manager of the office, means some code required to get it. MRI has already code generation, so it seems to prepare a code to get inspected target. But is it really required?
I use BookmarksMenu extension to add a menu entry which is used some time but temporally one.
I introduced macro function for MRI on version 1.0 and it can do something which can be done by MRI itself. I wrote interactive console as a macro and now I do not need to reopen the MRI for method execution after any complicated method call.

[rudolfo]

Hi hanya,

I am happy with the two menu entries of MRI. My main point in my post was that it is not really difficult to do something similar with Xray -- at least not for the power user. Which is a bit contradicted by my own OpenOffice history: It took me 2 years and the hint from Kingfisher to figure this out. Well, maybe I am not a power user
Anyway as I have the chance right now, I'll want to express my thanks to you for building and supporting the MRI extension. It saved my day several times.

[hanya]

My main point in my post was that it is not really difficult to do something similar with Xray

I'm sorry, its my misunderstanding.

I will finish to extend MRI extension and will it release to somewhere with explicit license like Apache License. My task has been almost finished. Recent world of OpenOffice.org has some dramatic change. I have some problem to support some variation of OpenOffice.org, largest problem is in the API of the UI. The grid control of LibreOffice 3.4 take the part of update of the grid from OOo 3.4 in, the part of grid API has incompatibility. And also, the tab API of LibreOffice is completely broken but it has another implementation of tab control which is also problematic.

I want to say, the inspector should be implemented in C++. I have met some problems about type of UNO in Python, there is no way to get correct type of value coming from UNO. But only C++ implementation of UNO binding has one by one type mapping between C++ and UNO and the value of any has type information of the wrapped value.

I can said to people who ask difference between inspectors, use favorite one.

[Jimster2012]

I am getting lost as to still what MRI does. Looks like to me you still have to understand all of the interfaces and so forth methods and so on to be able to use MRI. Am I correct that MRI cannot be used as a teaching aid? I would hate to have to purchase Microsoft Office to get started on programming learning but it seems like that is where all of the learning tutorials are. The MRI documentation isn't very good unless you already have a thorough understanding of all of this. I was hoping that the MRI program would explain all of the services, methods and what they do but it looks like it assumes you already know what to do and what services you need to use and them explains them to you in your code which doesn't make sense at all to me because if you already know what interfaces and so forth you need, then you wouldn't need MRI or x-ray. lol. I think I will go ahead and just do it as a database but as far as learning any programming side of this I think I will have to eventually purchase MS Office as it seems like OpenOffice and LibreOffice are geared to professionals programmers already in the know.

[kingfisher]

MRI is a tool. Don't get hung up on it. The references I gave earlier tell you at least most of what you will need to know. After reading those you will be able to create macros and have a better idea of how to use MRI.

Scan through the table of contents in Andrew Pitonyak's main macro document. You will almost certainly find some relevant examples there. If you need a particular 'interface' or 'service' you can deal with it when you come to it. I have found it necessary in a minority of cases.

It would help to post specific questions as they arise. If Base does what you need without programming, use it.

[hanya]

Am I correct that MRI cannot be used as a teaching aid? I would hate to have to purchase Microsoft Office to get started on programming learning but it seems like that is where all of the learning tutorials are.

If someone write a full documentation for all objects of the office, it would be the more readable documentation, and you can use MRI to get list of method or properties from objects. But the office has extendablity by extensions and they can be provide their own API and the default implementation of the office might be replaced with the objects provided by them. For example, I wanted to print page number like 001, 002, ... which can not be done by the default implementation of the numbering service. I made a wrapper for the default numbering service and provider a numbering format I wanted to use. This was a simple situation but some part of the office can be done like that except for core implementation.

The MRI documentation isn't very good unless you already have a thorough understanding of all of this.

I think so too, but there is the document about UNO: http://wiki.services.openoffice.org/wik ... sional_UNO

I was hoping that the MRI program would explain all of the services, methods and what they do but it looks like it assumes you already know what to do and what services you need to use and them explains them to you in your code which doesn't make sense at all to me because if you already know what interfaces and so forth you need, then you wouldn't need MRI or x-ray. lol.

The API of the office provide introspection mechanism to get information of an object itself and MRI and X-Ray use it, but they do not have any built-in documentation. If someone write documentation for each objects, they can link to them.

I think I will go ahead and just do it as a database but as far as learning any programming side of this I think I will have to eventually purchase MS Office as it seems like OpenOffice and LibreOffice are geared to professionals programmers already in the know.

I could say for this kind of open source project, it was not prepared. You can find File - Output entries on the menu of the MRI window, these menu write informations about object to permanent file. I add that kind of feature to documentation purpose but I need some writer. The UNO is now large implementation which has over 4000 files to define.

[RPG]

Hello

It seems to me we are on the same point as when this discussion starts. How to understand the API. I think start reading here
http://wiki.services.openoffice.org/wik ... /API_Intro

There you can learn that understanding of the idea of services and the interfaces of the API is real important. You learn there the basic idea. But for real understanding you must get an idea in your head of the API. Maybe the same is true for the API as for learning music. Some learn it fast and other have to learn it step by step by hard study. I did first understand the different services and interfaces. Later I did understand how they make the API and also how the API is structured. Also I did understand how one service starts with the using a document. Better is maybe first understanding how the API is build and then understanding the services and interfaces. Understanding so far is not enough there you ask this in the forum for OpenOffice.org. This means you want use this API in OOo. So you have also to understand OOo complete at least that part you want use. And now you can see starting with macros in OOo without understanding OOo and without the API is not real good and is real time consuming.

I think the best way is: work with OOo until you understand all details; then when you discover some part what are real difficult to do without macros then try to do it with macros. Now you must start understanding the API and then it can be your macros are not so difficult.

Romke

[Villeroy]

I don't understand what VBA programmers mean when they say that they are familiar with VB or VBA. Are they familiar with code completion and macro recorder? If they are really familiar with that application model and the language tailored tightly around it, how is it possible that they can not apply a simplified version of the same language to another software model?

And why should this software (unpacked ~350 MB including 5 macro languages) be able to run a proprietary applications code whereas MS spent years to port their own VBA runtime to the Mac platform?
Does anybody know some application which runs another applications macro code? Can Corel Draw run Photoshop macros? Are there any video editors or CAD programs able to run custom code that has been written against another vendor's API?
All the macro programming is about objects with methods and properties, simple data types and composed objects and structs. The StarBasic language is almost identical to VB but with less runtime functions and no class modules. What do they mean when they say they are familiar with VB but In all those years since 2004 I've never noticed a single complaint about missing class modules in StarBasic?

What do they mean when they ask for help writing a snippet against this API? After all they have not written a single line of code and we won't see them again until they need another human macro recorder to solve their next problem.

This API with its MVC architecture is very well documented, thousands of google hits refer to help pages, tutorials, snippets and online books. Why do we have to waste all this time to spoon feed each and every VBA programmer unable to use the internet and unable to program anything but MS Office? Apart from this, many of these VBA coders do not understand Microsofts office suite. They hide away their incompetence behind walls of (recorded) VBA which happens to work against the application logic with one particular document structure and only if the alleged user does not do unexpected things.

[kingfisher]

It seems to me that some people regard MS office software as the benchmark (perish the thought) and expect the open source software to mimic MS Office. They want to change horses but only if the new horse performs the same way.

[rudolfo]

There is one point in what Jimster is saying: In MS Office you can start the macro recorder and you get finally some meaningful and readable results. Surely not the best code, but at least many novices of the software know how to work with it (and finally get the job done that they want to see done). Lets not forget here that a macro is about getting the job done. It is not primarily about creating nice and maintainable code that can be re-used in other occasions. Allthough there is surely a break-even where it is worth to have re-usable code compared to writing/recording macros all over again from scratch.
Now I put it this way: OpenOffice cares mainly about those that are beyond this point of break-even while Microsoft cares for those that are on the macro recording side of the break-even.
But back to the point that Jimster observed very well in my eyes. To produce a good macro comparible to MS Office you can use MRI, but you can't use what you know (click on this button in the Office GUI, select this part, ...) to drive MRI. Instead you first have to translate your GUI experience into something that is understood by MRI. What you see in MRI is .getColumn, .getString, ..., which tells me enough to make my descisions as I have several years of programming experience, but for normal users it might be like russian language and they feel like getting lost.
I still think (double) clicking your way through MRI on those lines that sound familiar should still do a good job together with some trial and error. And yes, why should you document something extensively, if you can instead do a simple click on a method and check what result you get in MRI. And the documentation is not that bad at all, simply select the method with the mouse and click on IDL reference and your browser is direct in the right section of the API documentation at http://www.openoffice.org/api/docs/comm ... le-ix.html.

The other question is: Why do you need to understand macro code, if the main point of a macro is to avoid repeated key strokes and get the job done more quickly. Record your repeated task to a macro and replay it. For me and for most users it doesn't matter if these are UNO dispatcher calls that are executed in the background and not methods of UNO objects as long as they do the right thing (and get the job done).