Running a recorded macro gives strange results

English
, ,
#1

Hi,

I have to do a very repetitive task in 50+ spreadsheets, so I thought: “Why not record a macro?”
So I enabled macro recording (had to install JRE and enable the use in LO) and went to do the tasks in the spreadsheet, and then stopped recording and saving the macro.
Then when closing and re-opening the same file to try the new macro, the results are very strange: it does not ‘do’ what I recorded.
So I tried again, with a new file, recorded just some basic steps to see what the issue is. So this is what my spreadsheet looks like:

19 columns and 3 rows of text and some dates.

What I did:

Tools->Macros → Record Macro
Right-click column C ->Insert colums Left
Right-click column H ->Insert colums Left
Left-click row 1 → Click Bold
Click Stop Recording

Now I save the macro and close the spreadsheet, not saving changes (because I want to test the new macro).
Then I open the spreadsheet again and then:

Tools->Macros->Run macro
I select my saved macro and click ‘Run’

What happens:

2 columns are added at the left side of the spreadsheet, so column 1 and column 2 are added.
Row 1 is not made bold.

When I edit the macro the code is 2849 lines long, which seems a bit excessive to me. I can post it here but I don’t think it will help my case as it probably is too long for anyone to look at.

Clearly I am doing something wrong. I am saving the macro in ‘My macros’->‘Standard’-‘projectname’.

I would be very happy if anyone can tell me what I am doing wrong or what I can check to determine what is going wrong.

Thank you!

#2

Hello @fritsje25

Macro recorder has a very limited functionality and is of a little use even with such a simple tasks as yours. You can use a code like this to insert columns and set font to Bold.

Sub TestMacro

Rem Declare variables
Dim oDoc as Object
Dim oSheets as Object
Dim oSheet as Object
Dim oFirstRow as Object

oDoc = ThisComponent 'Get document container
oSheets = oDoc.Sheets 'Get all sheets in the document
oSheet = oSheets.GetByName("Sheet1") 'Get sheet with defined name
oSheet.Columns.insertByIndex(2,1) 'Get sheet's columns and insert 1 column at index 2, indexes start with 0
oSheet.Columns.insertByIndex(6,1) 'Get sheet's columns and insert 1 column at index 6, indexes start with 0
oFirstRow = oSheet.Rows.GetByIndex(0) 'Get first row
oFirstRow.CharWeight = 150 'Set font to Bold
End Sub

Regarding this:

When I edit the macro the code is 2849
lines long

Retried your steps while recording macro - it resulted in 20 lines of code, I can not understand why the result (in normal conditions) could be so long. The macro records Dispatcher command InsertColumnsBefore but Macro Recorder does not supply Dispatcher with the information about the currently selected column, so that is the reason why columns are inserted at the beginning. The same goes with first row - Dispatcher gets no information about the selected row, so it just applies Bold to nothing. My advice is not to use Macro Recorder, but spend some time to learn ‘common’ programming. The code is much more cleaner, scalable, readable and error proof.

— Edit 2019/01/14 19:20 —

Quoting comment :

Is it possible for you to change the
code as posted by SM_Riga

You just change the line oSheet = oSheets.GetByName("Sheet1") 'Get sheet with defined name with any of two lines from @Lupp comment:

will always choose the first sheet
(internal index 0)

oSheet = oDoc.Sheets(0)

or

would choose the active sheet

oSheet = ThisComponent.CurrentCotroller.ActiveSheet
#3

Thank you!

The tasks that I have to perform are rather extensive and involves combining colums (using CONCATENATE function), moving comlums, changing font type and size, calculating an age (using DATEDIF-function) but the macro recorder failed miserably so I started out by just inserting columns and setting first row to Bold - and the macro recorded failed at that too, so this is why I posted it here.

Is it true that the macro recorder has trouble recording just inserting rows? It would seem like a really basic function to me to be honest. I wonder what it -is- good at, maybe I can use it to record those things?

#4

This code will only work if there is a sheet named “Sheet1”, and in this case it will work on this sheet even if it is actually in place 111.
oSheet = oDoc.Sheets(0) will always choose the first sheet (internal index 0), and
oSheet = ThisComponent.CurrentCotroller.ActiveSheet would choose the active sheet.

#5

The macro recorder creates a macro that can insert a row. That experimental tool misses, however, to first perform a .uno:GoToCell if you directly call the context menu of a column. The “.uno:InsertColumnsBefore” command then works with the previously focused cell’s column. In the same way selecting a row by clicking its row label is not recorded as a GoTo.
Don’t expect you can solve “complicated tasks” by recorded macros.
Best do it the “Spreadsheet-Do” which is without any macros at all.
If this is definitely impossible, either
move to a database or
write user code from scratch.
The latter doesn’t exclude the usahge of the dispatcher if it provides efficient commands. Just prepare this with explicit assignments to the needed properties.

#6

Generally (an expression of opinion!): The belief that spreadsheet solutions should best be macro-driven is encouraged by commercial competitors as a part of their fight against free and open solutions because macros can perfectly defeat general compatibility / interoperability.

#7

Thank you.

My thought was so simple: every month I need to get 50+ spreadsheets from a CRM system. The organization cannot/does not do what I need and that is to organize the extracted output (spreadsheet) in a way that is workable for another system.
So it is my task to transform these spreadsheets, every month, to the adjusted format.
I was thinking: if I can simple run a macro to insert colums where I need them, change font type and size, move columns and do the calculations then I can do the deleting of some rows and speed things up considerably.

But yeah: I guess you are right. I think I am going to try to do it by manually coding. Is it possible for you to change the code as posted by SM_Riga to work with oSheet = oDoc.Sheets(0) and Sheet = ThisComponent.CurrentCotroller.ActiveSheet as you commented?
Then I can use that to add the insertion of more columns and go from there.

I tried to change the code myself but it failed of course: I have no idea what to replace.

Thank you!

#8

While I do fully agree with @Lupp about less code = better spreadsheet statement, sometimes you need to code some script/subroutine to make life easier. I have a pretty similar task in a daily routine - to restructure data from several csv and spreadsheet sources and fit them to the predefined template for further proceeding - here user defined code is really handy. All the tasked mentioned by @fritsje25 are possible to implement using macros. To begin some learning, you can refer to OOO Macros Explained by Andrew Pitonyak (still this requires some basic/intermediate understanding what programming is), OpenOffice Wiki and surely MRI or Xray libraries installed to examine your code.

#9

There was one line of code I commented on: oSheet = oSheets.GetByName("Sheet1"). This line you need to replace with one or another alternative if it wasn’t as you needed it.
I hope you are aware of the fact that the insertion of columns “first left of C, then left of H” ends up with a different reslut than “first left of H, then left of C”. Concerning any further advice you will need to not just tell a story, but to specify very precisely what you want to achieve. If you are completely unnexperienced with programming for LibreOffice -or with programming at all- you shouldn’t start with a task needed “in production” but with learning the basics.

#10

Edited an answer to explain how to adjust Sheet selection in the sample code

#11

@SM_Riga: I agree. There sometimes are proceedings that can very efficiently be automated by applying user code.
The first an most grave job for a user going towards automation is to unmistakably and precisely specify what he wants to achieve. This is the basis for getting reliable advice and for a kind of goal-oriented learning as well.

#12

Thanks, I will dive into this.
I do have a little programming experience and am also familiar with HTML-markup, so I do ‘understand’ what is done by some of the lines of code, but this is very far from being able to start a full routine from scratch.
I will probably end up outsourcing the task of making the code to do what I want, because from what I can tell so far it is far from straighforward and easy to learn. I will prepare a ‘this is the starting spreadsheet’ and ‘this is what it needs to be’ so someone can make the code for me, I figure this is the best way to go at it.

I highly appreciate you all take the time to answer to my inexperienced questions and incoherent ramblings - Thank you!

#13

Quoting @fritsje25: “…‘this is the starting spreadsheet’ and ‘this is what it needs to be’…”
That’s a good approch. More detailed:
Sheet1: what I get
Sheet2: after the reorganisation of columns
Sheet3: after the creation of calculatable (redundant) values
Sheet4: after the introduction of formatting
Sheet5: what I need to export to csv again
Sheet6: PrettyPrint branch
?..

#14

Suggestion:

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

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:SelectColumn", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertRowsAfter", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:InsertRowsAfter", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Bold"
args2(0).Value = true

dispatcher.executeDispatch(document, ".uno:Bold", "", 0, args2())

( Slightly edited for code formatting by @Lupp )

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.