Macro Writing Help Wanted - OOo 3.3 Writer

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

Please could somebody help me through a macro creation process, the recorder doesn't do what I need so I have to write code :o

Here's what I want to do:

Let's presume I have some plain text in the paste buffer (it will come from a different file; whether that can also be automated is something I can look at later).

1. Paste the text, then select it and convert text to table.
2. For the new table, set the column widths to specific values (four columns).
3. For the new table, set the spacing to contents to a specific value.
4. For the new table, set the line thickness.
5. For all the cells in the new table, set a paragraph style.

Any help appreciated, having been educated in the days of procedural programming this object-oriented stuff makes me glaze over.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

No replies at all? Would I be better off using an external macro in Windows (which I can program as a series of mouse clicks and/or keyboard short-cuts)??
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by RPG »

Hello

I think:
a) Forget macros
b) learn what you can do with OOo.
c) store your data in a kind of a database
d) Make a registrated database for point C
e) Open a writer document
f) press F4
g) Select the datasource
h) drag in the data as table
i) Use the table wizard for formatting

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

  • Give a certain name to your text file in a certain directory, say .../textfiles/current.csv
  • Connect a Base document to the textfiles folder which constitutes a database of text files. Specify all the import settings for this particular flavour of text files.
  • The database document shows all .csv files in directory textfiles as a collection of database tables.
  • In the database document you may define some query to reshape your table data, pulling only relevant rows and columns in some order of rows and columns.
  • Create a simple report from your table or query. A report is a Writer table, dynamically filled with current table data (coming from your current.csv) every time you open the report. You can edit the layout and formatting of that table independently from the data.
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

Thanks guys, but I think you have missed the point. I want to automate a process I repeat on a monthly basis, and the "scripts" you have suggested are almost as manual as the one I was using (I admit the live import of data from a database query might have legs, but it doesn't answer the major question...).

Surely the macro language can implement the sequence of commands I currently use, otherwise what's the point of it? Where I get lost is understanding the scope of the commands - it is not at all obvious how to "select" the current table for example (which manually is simply a case of Table... Select... Table or Alt-A, S, T). I could use Auto-It and set up a Windows macro which injects keystrokes and mouse clicks into the Writer window, but I was really hoping to create something embedded in my document template so it is portable.

Having used MS Word professionally, I know what can be done with the macro language in that (although I didn't have the skills to create anything more than a simple one from scratch). Are you saying the OOo macro programming language is pointless crap?
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

You open a document (a report) and see the table with current data in a predefined layout and formatting. This covers all the points of your initial posting without a single line of code. Of course you may want to write a shell script to organize your text files. Plain text files do not require a 400MB office suite with highly complex macro scripting.
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
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by RoryOF »

The OpenOffice macro language is very powerful and very complex; it can interface to several programming languages but It takes much time and study to master. If you need an easier path, use MS Office and VBA.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

Great, so is somebody willing to help me work it out that way?
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

Put the source file in a dedicated directory.
File>New>Database...
[X] Connect to existing... type: Text
Specify the directory, the name suffix (*.csv), the delimiter, encoding etc.
[X] Register the database if you need those data in arbitrary documents.
Save the database.
Go to the tables section, check if the table content makse sense.
Right-click the table and create a report from it.
Close the report and right-click>Edit...
In this Writer document you see a place holder table with 1 row for the column labels and one for the data. Do whatever you want with that table without deleting it nor changing the count of rows and columns. If anything goes wrong, you can create a new report within seconds.
The report document is saved within the database document. You have to save both.
Once you set up everything to your liking, you never need to edit anything again.
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

Villeroy wrote:Once you set up everything to your liking, you never need to edit anything again.
It would be nice if that were the case, unfortunately hand adjustments need making month by month because of the compromise between the number of days in the month and spacing things out nicely. I wonder how this is going to work with a variable number of rows in the table.

I will try the database method, but when I said "great" it was in response to the post that indicated the macro language should be powerful enough to do anything. This is, after all, posted in the macro language section of the forum and is what I am particularly interested in getting to grips with.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

The table grows and shrinks dynamically according to the amount of data.
Unless you are a (semi-) professional programmer you won't be able to handle this API.
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

Villeroy wrote:Unless you are a (semi-) professional programmer you won't be able to handle this API.
Thanks, that's what I needed to know. Not a lot of use then.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

Mohawk wrote:
Villeroy wrote:Unless you are a (semi-) professional programmer you won't be able to handle this API.
Thanks, that's what I needed to know. Not a lot of use then.
But you can use the components of this software as they are intended to be used. That means more configuration instead of programming.
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

I have come back to this again (18 months down the line) to review the advice I was given, and frankly it is disturbing to see how blinkered the advice is. I use the process (described in post 1) monthly, and what I have done is write down the details which I then get out every month and run by hand.

Despite the "if I were you I wouldn't be doing it that way" kind of comments, it illustrates significant limitations in the applicability of the OO macro system for the "normal' user. In MSOffice I would have been able to simply record what I was doing and replay it when necessary. In OO I can "record" the process, but then when replayed, key steps are missing from the recording. This is the gap that needs to be plugged; simply ensuring that every manual command has an equivalent in the macro programming system, would plug it.

I have not tried AutoIt yet, but having had another reason to download and use AutoIt recently I am certain it will provide an alternative macro system which does what I need, and the resulting macros can be compiled as stand alone executables and thereby distributed to co-workers without them needing AutoIt itself installed. Naturally AutoIt only interfaces with the target by the user interface and not by the API, so is not capable of the sophistication of hand-coded macros, but simply because of the inadequacies of the OO macro system it can do what I, as a normal user rather than a programmer, needs - when the OO macro system can't.

But that should not be necessary. If OO pretends to be a serious alternative to MSOffice, here is an area where it lets itself down.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

If you want to operate an office suite by means of recorded macros then MS Office is the one and only solution. There is not a single alternative.
The OpenOffice API is just like any other application programming interface. It is a tool for professional programmers but not "normal end users".
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

If you will allow me to continue the debate, I have to ask: why not? What is the point of having a record option if some (or many) of the actions the user might take while recording don't get recorded? Could it be that OO has been conceived by programmers rather than users?

Would it actually be all that difficult to ensure every menu option has an equivalent line of API code? I (naively) assume the menu system interacts with the core by means of the API anyway??
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

Mohawk wrote:Would it actually be all that difficult to ensure every menu option has an equivalent line of API code? I (naively) assume the menu system interacts with the core by means of the API anyway??
VBA is a second office suite for hobby programmers. You can install MS Office without VBA which saves hundreds of Megabytes disc space. The whole OpenOffice suite weighs 350 MB, including 4 macros languages Basic, JavaScript, Java Beans and Python.
The download size of OpenOffice is the same as the 4 Microsoft viewers mentioned here: viewtopic.php?f=74&t=77089
The Microsoft viewers can display but not edit rtf and doc(x), xls(x), ppt(x) without executing any macros at all.
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

In case anybody comes across this: I managed to get a macro working in Calc (I'm on OOo 4.1.10 now) using a mixture of techniques. First I recorded the macro from mouse and keyboard actions, then I went into the macro code and adjusted it to what I actually wanted. Recording the actions provided the structure which would have been difficult to write from scratch.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by RoryOF »

The Macro Recorder can give an outline; it is its main use.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
MOoo
Posts: 2
Joined: Thu Aug 12, 2021 1:28 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by MOoo »

Mohawk wrote:In case anybody comes across this: I managed to get a macro working in Calc (I'm on OOo 4.1.10 now) using a mixture of techniques. First I recorded the macro from mouse and keyboard actions, then I went into the macro code and adjusted it to what I actually wanted. Recording the actions provided the structure which would have been difficult to write from scratch.

Congratulations Mohawk, Your success encourages me as I have a similar problem; I want to do something relatively simple with a macro but have no experience in basic macros. "We will get there - we know we will"
4.1.10 OpenOffice 4.1.10 on Ubuntu 20.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

MOoo wrote:"We will get there - we know we will"
Sorry, I don't think so.

This is how I interprete this posting and hundreds of similar ones:
1. You never read any books on software, let alone programming books.
2. You don't know much about this office suite you want to hack upon.
3. You never learned programming in any language or framework.

In the end, all these postings end with an unwritten demand: "Please write me some code to overcome my difficulties operating this software!"
If we link to the usual resources (books, tutorials, example code) we never hear again from you which clearly indicates that you are not working on it.
If we start writing code for you, it becomes a never ending project (waste of time), particularly because you do not accept any ways to solve problems without code (office suites are made for non-programmers in the first place) and because you try to learn coding by copy&paste.
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
User avatar
Mohawk
Posts: 83
Joined: Tue May 31, 2011 8:16 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Mohawk »

Villeroy wrote:Sorry, I don't think so.
That's unnecessarily pessimistic and cynical.

It's all very well people steeped in software saying everyone should learn everything you need to know to be competent at coding, but that presumes a. we have the time, and b. we have the necessary youth. Those under (say) 40 years of age fail to appreciate how unretentive the memory becomes as you get older. It becomes difficult to learn new things. You might struggle and crack it, but then if you don't use that knowledge daily it vanishes again and you have to start from scratch each time.

I leaned to code in pre-OO days, when programming was strictly procedural. I can sort-of remember the syntax for BASIC, and then I backed Modula2 (a variety of Pascal, which fizzled out). I can, however, take code in almost any procedural language and adapt it without explicit knowledge of the syntax because the syntax is there on the page, in the existing code.

And that's all my approach is to creating a macro. Take what's there (recorded as a macro) and then edit it instead of writing from scratch. The OO (object orientation) is a bit mind-boggling to somebody of my generation, and it might take some trial and error, but it gets the job done.

No, you can't expect everyone to invest the time and energy to become experts - that might be your life, but we have other lives to lead and we're doing what we do for personal reasons not commercial. Therefore the pragmatic solution is the best one. If we have to ask a question from time to time... that's what forums are for, and (at least some) people are willing to share their knowledge.
OpenOffice 4.1.2 on Win7/64, 4.1.10 Linux Mint 20.3
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by John_Ha »

See OpenOffice.org Macro Information and his excellent OpenOffice.org Macro document which has hundreds of examples.

Buy OpenOffice.org Macros Explained

Search this forum and the Code Snippets and Extensions forums for countless examples which you can hack.

I have DevelopersGuide_OOo3.1.0.pdf, macros_intro_v1.pdf, Getting Started - CH 13 - Macros.pdf (from the user guide) and Pitonyak's Useful Macro Information For OpenOffice.org.pdf. Search for them or send me a pm with your email ID.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Zizi64 »

Villeroy wrote:
Sorry, I don't think so.
That's unnecessarily pessimistic and cynical
It is not pessimistic, but (unfortunately) this is a realist approach...
I can say it based on the experiences of this forum and other forums.

Many-many user want to use the macros, but they do not want study the API of the AOO/LO.
(Maybe you are an exception of the users...)

API: Aplication Programming Interface.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
JohnSUN-Pensioner
Volunteer
Posts: 876
Joined: Fri Jan 14, 2011 1:21 pm
Location: Kyiv, Ukraine

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by JohnSUN-Pensioner »

Mohawk wrote:Those under (say) 40 years of age fail to appreciate how unretentive the memory becomes as you get older. It becomes difficult to learn new things.
(After these words, the forum veterans looked at each other and shrugged their shoulders. The most cynical of them whispered "At least we are still breathing...")

Please reread these two messages from Villeroy, point by point. And just do it. Try it at least. These thirty clicks will not take you much time (actually less, but at first you will make mistakes and make extra clicks). Just try it. Please!
I may not have a lot to give but what I got I'll give to you...
Apache OpenOffice 4.1.5, LibreOffice 6.4.4.2 (x64) on Windows 7
If you think that I did not answer your question, make allowances for my imperfect English
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

Those under (say) 40 years of age fail to appreciate how unretentive the memory becomes as you get older. It becomes difficult to learn new things.
I'm 60 and learned some programming basics at the age of 35 by working through books. All software used to be associated with some floppies and thick books in those days. Every grocery store sold books on Excel, Word and Windows for users who understood that these tools need some education to be productive. I read some of those too. Books are still among the most important resources for young programmers. This is NOT about memory. I do not remember all those expressions. I look them up. I work them out. I test, query, play with them untill they tell me what I want to know. Read, write, run, debug (observe results), read error messages sometimes for hours. In the end there is no problem memorizing what you have actually worked out. Most complainers here can not even deal with the primitive Basic IDE which does not involve much more than keys F5, F7, F8 and F9. How can they ever find an error in their code, let alone memorize anything from wild guessing and mindless copy&paste? Some members on this forum struggle since years with silly Basic (often needless effort) and it turns out, they still can't work with MRI. MRI is essential. You can not handle this monster of an API without MRI (or XRay for this purpose). And no, mastering MRI is still not enough. There are also some essential theoretical concepts which can be learned in a weekend or two before you are going to solve your ad-hoc problem. Programming is a profession of high skilled individuals and artists. You can not mimic this work by copy&paste with some guessing.
P.S. I'm sure that some of the most valued contributors to this forum are older than me.
P.P.S.: MrProgrammer for example is a real programmer by his trade. I've never seen him publishing any macros here. He always points out how this software is intended to be used by normal users who are not programmers. The only exception is his tutorial on how to record macros properly if you can't write them.
Last edited by Villeroy on Sat Aug 14, 2021 4:23 pm, edited 1 time in total.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

Mohawk wrote:1. Paste the text, then select it and convert text to table.
This is where the trouble begins. What kind of text exactly? The exact components of text determine the methods how text can be converted into a table. There is no artificial intelligence nor mind reading in macro programming.
If you can do it with the built-in menu command, writing a macro would be inefficient. How much text could you convert to tables in the same time where this topic has been written? Programming this step is a massive waste of time, particularly when you copy&paste the code without understanding it.

As I understand the problem, you actually want to import text data into a database with a report. A report is a printable table layout for database data.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by Villeroy »

--- instead of macros ---------------
Put your text file in a dedicated directory.
menu:File>New>Database...
[X] Connect to existing database
Type: Text
Specify the database directory
Specify delimiters, encoding etc.
Save the database document.
Have a look at the table, if it is split into the right columns. If not, menu:Edit>Database>Properties...
Create a report.
Open the report in design mode, adjust layout and formatting, save the report, save the containing database.
---------------------------------------------
Next time you receive another text file, copy it to the database directory replacing the existing table.
Open the database and the contained report.
What you see is the new data in predefined layout and formatting.
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
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by John_Ha »

Villeroy wrote:This is NOT about memory. I do not remember all those expressions. I look them up. I work them out.
Never remember something you can look up in a book. Albert Einstein (who, when questioned, was unable to say how many feet were in one mile).
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
MOoo
Posts: 2
Joined: Thu Aug 12, 2021 1:28 pm

Re: Macro Writing Help Wanted - OOo 3.3 Writer

Post by MOoo »

Villeroy wrote:
MOoo wrote:"We will get there - we know we will"

Sorry, I don't think so.

This is how I interprete this posting and hundreds of similar ones:
1. You never read any books on software, let alone programming books.
2. You don't know much about this office suite you want to hack upon.
3. You never learned programming in any language or framework.
Egh?
Points 1 & 3 you extrapolated from my reply are wildly inaccurate. I don't know so much about OO/LO programming, hence my interest in this forum.
Last edited by MOoo on Tue Sep 14, 2021 4:19 pm, edited 1 time in total.
4.1.10 OpenOffice 4.1.10 on Ubuntu 20.04
Locked