Are some actions disallowed in CALC Macros?

English
,
#1

I’m now experimenting with Macros.
A simple one first - just pasting the copy buffer from a Google Sheet and then localising the decimal separator from a stop “.” to a comma “,”.
In the macro, for each of the operations, the toolbar buttons have been identified as opposed to using CTRL+ALT+V & CTRL+H.
The paste special functions as expected but then it doesn’t progress to the find & replace action.
It will accept the keyboard shortcut for paste special but I replaced that to eliminate any inconsistency as my first attempt demonstrated that it doesn’t move on to the find & replace when I used the F&R keyboard shortcut.
Is this because F&R always “forgets” the replacement character, thereby making it ineligible for a macro, or have I just created another Noobie :man_facepalming: special?
EDIT: I’ve just created a “standalone” localisation macro which works as expected. However, the first macro expects me to confirm the “paste” whereas the localisation just “does it”.
EDIT 2: The first macro operates where I place the cursor but the localisation macro actively moves to the original cell.
Version: 7.2.2.2 (x64) / LibreOffice Community
Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56
JRE 1.8.0_311 with macro recording enabled (may be limited)

#2

If you expect help you should append your macros to your post.
If you try paste the macro here you should include it in triple quotes to prevent typographic alteration of used quotes ( even if some people may like the visual appearance) - instructions here:

#3

Initially, I was just hoping for clarification as to whether the combinations I was using were invalid.
The macros were created by recording a simple cell focus and paste special then a simple cell selection and find/replace so i was reasonably confident that LO could record those fatrly accurately. I’m going to append the LO generated macros below - hopefully.

#4

Is that a double and single quote “‘like this’” or a special character I’ll hopefully find somewhere in all the references provided?

#5

especially for code you should place three backtics ``` on its own line before and after the code_block

#6

I can’t seem to find those on my keyboard - but I have mastered cut and paste so, two code blocks below.
The first is the attempt to paste special and find&replace, the second just the find&Replace

#7 

REM ***** BASIC *****

Sub Main

End Sub

sub Macro1

rem ----------------------------------------------------------------------

rem define variables

dim document as object

dim dispatcher as object

rem ----------------------------------------------------------------------

rem get access to the document

document = ThisComponent.CurrentController.Frame

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------

dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = "SelectedFormat"

args1(0).Value = 1

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

rem ----------------------------------------------------------------------

dim args2(0) as new com.sun.star.beans.PropertyValue

args2(0).Name = "ToPoint"

args2(0).Value = "$G$239:$G$246"

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

rem ----------------------------------------------------------------------

dim args3(20) as new com.sun.star.beans.PropertyValue

args3(0).Name = "SearchItem.StyleFamily"

args3(0).Value = 2

args3(1).Name = "SearchItem.CellType"

args3(1).Value = 0

args3(2).Name = "SearchItem.RowDirection"

args3(2).Value = true

args3(3).Name = "SearchItem.AllTables"

args3(3).Value = false

args3(4).Name = "SearchItem.SearchFiltered"

args3(4).Value = false

args3(5).Name = "SearchItem.Backward"

args3(5).Value = false

args3(6).Name = "SearchItem.Pattern"

args3(6).Value = false

args3(7).Name = "SearchItem.Content"

args3(7).Value = false

args3(8).Name = "SearchItem.AsianOptions"

args3(8).Value = false

args3(9).Name = "SearchItem.AlgorithmType"

args3(9).Value = 0

args3(10).Name = "SearchItem.SearchFlags"

args3(10).Value = 71680

args3(11).Name = "SearchItem.SearchString"

args3(11).Value = "."

args3(12).Name = "SearchItem.ReplaceString"

args3(12).Value = ","

args3(13).Name = "SearchItem.Locale"

args3(13).Value = 255

args3(14).Name = "SearchItem.ChangedChars"

args3(14).Value = 2

args3(15).Name = "SearchItem.DeletedChars"

args3(15).Value = 2

args3(16).Name = "SearchItem.InsertedChars"

args3(16).Value = 2

args3(17).Name = "SearchItem.TransliterateFlags"

args3(17).Value = 1280

args3(18).Name = "SearchItem.Command"

args3(18).Value = 3

args3(19).Name = "SearchItem.SearchFormatted"

args3(19).Value = false

args3(20).Name = "SearchItem.AlgorithmType2"

args3(20).Value = 1

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args3())

rem ----------------------------------------------------------------------

dim args4(0) as new com.sun.star.beans.PropertyValue

args4(0).Name = "Visible"

args4(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args4())

end sub



sub Main

rem ----------------------------------------------------------------------

rem define variables

dim document as object

dim dispatcher as object

rem ----------------------------------------------------------------------

rem get access to the document

document = ThisComponent.CurrentController.Frame

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------

dim args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = "ToPoint"

args1(0).Value = "$G$239:$G$246"

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

rem ----------------------------------------------------------------------

dim args2(20) as new com.sun.star.beans.PropertyValue

args2(0).Name = "SearchItem.StyleFamily"

args2(0).Value = 2

args2(1).Name = "SearchItem.CellType"

args2(1).Value = 0

args2(2).Name = "SearchItem.RowDirection"

args2(2).Value = true

args2(3).Name = "SearchItem.AllTables"

args2(3).Value = false

args2(4).Name = "SearchItem.SearchFiltered"

args2(4).Value = false

args2(5).Name = "SearchItem.Backward"

args2(5).Value = false

args2(6).Name = "SearchItem.Pattern"

args2(6).Value = false

args2(7).Name = "SearchItem.Content"

args2(7).Value = false

args2(8).Name = "SearchItem.AsianOptions"

args2(8).Value = false

args2(9).Name = "SearchItem.AlgorithmType"

args2(9).Value = 0

args2(10).Name = "SearchItem.SearchFlags"

args2(10).Value = 71680

args2(11).Name = "SearchItem.SearchString"

args2(11).Value = "."

args2(12).Name = "SearchItem.ReplaceString"

args2(12).Value = ","

args2(13).Name = "SearchItem.Locale"

args2(13).Value = 255

args2(14).Name = "SearchItem.ChangedChars"

args2(14).Value = 2

args2(15).Name = "SearchItem.DeletedChars"

args2(15).Value = 2

args2(16).Name = "SearchItem.InsertedChars"

args2(16).Value = 2

args2(17).Name = "SearchItem.TransliterateFlags"

args2(17).Value = 1280

args2(18).Name = "SearchItem.Command"

args2(18).Value = 3

args2(19).Name = "SearchItem.SearchFormatted"

args2(19).Value = false

args2(20).Name = "SearchItem.AlgorithmType2"

args2(20).Value = 1

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

rem ----------------------------------------------------------------------

dim args3(0) as new com.sun.star.beans.PropertyValue

args3(0).Name = "Visible"

args3(0).Value = false

dispatcher.executeDispatch(document, ".uno:SearchResultsDialog", "", 0, args3())

end sub
#8

I understand the implications of that so what I want probably can’t be done - but why didn’t it operate when combined with the paste special but did operate in isolation?

#9

If I’m not mistaken those two blocks appear to be identical and I think it’s just the find and replace. I am convinced I pasted the two separate macros into LO writer, added the triple quotes and then pasted the two items here.
Please bear with me. First foray into macros and I had trouble figuring out where they went, how and why.
I thought LO would “hold my hand” as I was just using the recording function.

#10

Noobie!
They are two different functions but because I collected them from Macro Management they came out in alphabetical order so the first half appears to be the second macro - F&R.

#11

Fwiw, the CT2N - Convert Text To Number (and dates) extension does all that, and more.

1 Like
#12

You might also consider that if you are writing macros (or learning to), you don’t really write most macros the way the recorder does. The recorder is based on UNO dispatching directly. A lot of that is hidden in modern LO BASIC macros. Read lots of newer macros online. It’s not quite like recording macros for Excel in VBA, which was a little more one-to-one. That said, the action macros–true ‘macro’ shortcuts–like you are showing here still DO tend to be dispatch-based. Sometimes instead of mimicking a user action, though, you can ask “where do the data I’m manipulating here fit into the LO API model?” and use a more direct (syntactically) approach. I.e. change what you want to change rather than effectively call up the dialog box that changes it. Andrew’s book is a little dated on this, dare I say, although a great resource nonetheless.

#13

So it does, thanks.
Fortunately, I will always be doing it in small volumes on a regular basis because I could see the wheels turning when I fed it 3500 cells to play with. It’s probably my data structure though because all the cells are 3 colour conditional formatted. Any thoughts on the subject?

#14

Dare I ask who Andrew may be?

#15

Found it or not - Scandinavian keyboard, I get § or ½ so it’s perfect if I want to write new legislation for parliament.
I guess I’ll just have to paste @karolus tip somewhere I won’t forget it - or not post code :thinking:

#16

I guess Andrew Pitonyak:
https://www.pitonyak.org/oo.php

#17

Yes, sorry…distracted. Had intended to provide a link. Another pretty good landing page is from Prahlad Yeri, Ten useful LibreOffice Macro Recipes - Prahlad Yeri.

That page also links to LibreOffice 7.2 SDK - Examples, which usually links to pretty ‘hip’ macros.

#18

@erAck Are there any thoughts on why the recorded macro only processed the paste special and completely ignored the find & replace? is it as I suspected, the second parameter of the wizard defaults to null even though I set it in my recording? Clearly, my macro would be redundant anyway because the CT2N extension copes admirably - but I can’t learn if I have to guess at the outcome.

#19

Recorded macros (recorded user interface dispatches) act as a makeshift until someone writes a true macro recorder. IF (and only if) you are familiar with the application itself and with the user interface, you can achieve amazing things with dispatches.
[Solved] Request for comments: Favorite Recorded Calc Macros
However, most “Excel experts” start recording macros whenever a grid appears on screen without knowing much about the thing they hack upon.

Macros in general, but dispatches in particular, are very capable of destroying your document when you run the code against the wrong sheet or document.

1 Like
#20

You can inspect the CT2N extension’s source code to learn how to do things, it’s written in Basic.

1 Like

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