Ask Your Question
0

Why don't recorded macros include the parameters and is there any way round this?

asked 2016-10-14 12:20:35 +0200

martink gravatar image

I want to do something which is really straightforward in Excel (and I have Excel 97, so it's been straightforward for 20 years!) but which seems to be impossible with Libreoffice Calc: record a macro and play it back. And a very simple macro too: just hide three columns (so, I record the following actions: select columns B & C, then right-click and "Hide") I can record the macro fine, but when I play it back it does nothing: looking at the macro code I can see this:

dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())

Presumably I could put the appropriate column names somewhere in the parameters there, but I cannot anywhere find some straightforward documentation which tells me what these parameters should look like. I'ld be grateful if someone could tell me either (preferably) how to get parameters into recorded macros, or (alternatively) where to find some simple documentation that would show me what these parameters should look like.

Incredible that Libreoffice is incapable of doing something simple that Excel could do 20 years ago, no?

edit retag flag offensive close merge delete

Comments

This seems to work for me in LO5.2.2 under Ubuntu 16.04. The macro requires no parameters, because the columns are selected. What version/os?

Pansmanser gravatar imagePansmanser ( 2016-10-14 16:47:08 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2016-10-14 18:42:45 +0200

peterwt gravatar image

Recording a macro to hide cells works for me and the macro hides the cells. A sample is here Hide.ods

You should be aware though that the cells will not be hidden until you protect the sheet. It states in the Cell Protection Tab in Format Cells that "Cell Protection is only effective after the current sheet has been protected".

If you run the macro in my sample the cells are not hidden. When you protect the sheet they become hidden.
The macro in my sample was recorded and no modifications made.

edit flag offensive delete link more

Comments

@peterwt: Recording a macro to select cells does work, but recording a macro to select columns does not. Columns need to be selected before columns can be hidden. @martink was trying to created a macro to hide columns which was why I suggested workaround to edit recorded macro to change from cell selection to column selection.

mark_t gravatar imagemark_t ( 2016-10-14 19:41:28 +0200 )edit
0

answered 2016-10-14 17:26:25 +0200

mark_t gravatar image

Recorded macro does not seem to include selection of columns that were selected while the macro was being recorded. If you record a macro of selecting cells then you can edit that recorded macro to select the two columns and then join the recorded macro with the hide columns macro.

Recording selection of two cells gives the following

sub Main

dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$L$1:$M$1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

end sub

Then Edit to change "$L$1:$M$1" to "$L:$M". And Add the line to hide the columns selected as follows:

sub Main

dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$L:$M"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())

end sub

Recording macros is not always effective. Excel is better than LibreOffice for this but still the code generated is very inefficient and almost always requires manual edit to make the macro more general in its application. If you intend creating macros I'd recommend taking a look at Andrew Pitonyak's book, Open Office Macros Explained.

OOME_3_0.pdf

Also maybe refer to his site Book Site Link

edit flag offensive delete link more

Comments

Note I'm currently using LibreOffice 5.2.2.2 64 bit on Windows 8.1.

mark_t gravatar imagemark_t ( 2016-10-14 17:30:21 +0200 )edit
-1

answered 2019-06-18 12:02:24 +0200

Wooflibre gravatar image

Yup, incredible that LibreOffice does not record in a macro what a PERSON, meaning a human being, WANTS to record in that macro. Why does this not bother the programmers of this program is beyond me.
Is the main point of LibreOffice to keep as many people as possible from using LibreOffice?

Why is it that programmers can no longer think like humans?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-14 12:20:35 +0200

Seen: 219 times

Last updated: Jun 18