[Solved] Copy cell above macro to be refined

[Gingalone]

I recorded the following macro with the purpose to copy the content of the cell which is above the selected one and then to go to the cell at right of the pasted one (I already saw in the forum a macro that copy the cell above but without the subsequent step at right...). The macro works well but the copied cell remain selected by dotted lines around (as if the content of that cell should be pasted somewhere else).
Question: how to get rid of that (the dotted lines)? (Not having to press the ESC key after the macro execution). Sorry it looks like a silly question but I tried in many, many ways to solve this simple problem, with no avail... Thank you for help

Code: Select all

sub copyabovecell
rem ------------------hot key: Ctrl-s ------------------------------------
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(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "By"
args1(0).Value = 1
args1(1).Name = "Sel"
args1(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:TerminateInplaceActivation", "", 0, Array())
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:Cancel", "", 0, Array())
rem ----------------------------------------------------------------------
dim args7(1) as new com.sun.star.beans.PropertyValue
args7(0).Name = "By"
args7(0).Value = 1
args7(1).Name = "Sel"
args7(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoRight", "", 0, args7())
rem --nxt line introduced by Gingus 22X2018 (copied fr. above) ---does it work??? useless!!!
dispatcher.executeDispatch(document, ".uno:TerminateInplaceActivation", "", 0, args7())
rem dispatcher.executeDispatch(document, ".uno:Cancel", "", 0, Array())
end sub

[Villeroy]

Either you record macro and accept limitations (your problem is a cosmetic one) or you program true macros.

Code: Select all

sub copyabovecell
view = ThisComponent.getCurrentController()
sheet = view.getActiveSheet()
cell = view.getSelection()
CellAddr = cell.getCellAddress()
RangeAddr = cell.getRangeAddress()
RangeAddr.StartRow = CellAddr.Row -1
RangeAddr.EndRow = CellAddr.Row -1
sheet.copyRange(CellAddr, RangeAddr)
cell = sheet.getCellByPosition(CellAddr.Column +1, CellAddr.Row)
view.select(cell)
end sub

OOo 3.0.X on Ubuntu 8.x

Why are you lying about the software you are using? There is no need to.

[Gingalone]

Thank you Villeroy, that works OK! Very clean and effective piece of code...
Long ago I used to write VBA macros in Excel, but once converted to Linux and Open Office, I saw that OO VBA was way different, so tried using the recorder and then I found the code of the recorded macros was really complicated, almost unreadable; so I gave up coding macros and occasionally recorded some calc one.
And I didn't lie about my OS and Calc: that was my condition when I joined this forum (2009)! Sorry I have not updated my profile. Now I work on Libre Office 6.0.7.3 on Ubuntu 18.04.

[robleyd]

Please consider updating your signature

[MrProgrammer]

Question: how to get rid of that (the dotted lines)?

Your recorded macro works fine for me — no dotted lines — using OpenOffice 4.1.7. I think this is a LibreOffice-only issue. You are using copy/paste. Instead, one can use Edit → Fill → Down. This may prevent the dotted lines you don't like, and it also prevents overwriting the value on the clipboard when the macro is called. See the Duplicate macro in [Tutorial] Favorite Recorded Calc Macros.

I found the code of the recorded macros was really complicated, almost unreadable.

Who cares? You don't to read ithe macro; just use it.

Either you record macro and accept limitations (your problem is a cosmetic one) or you program true macros.

Agreed, however non-recorded macros are difficult enough that most users will not spend the time to learn this skill. The user interface allows one to do many things. The macro interface can accomplish far more. Recorded macros can bridge some, though not all, of this gap and I encourage people to explore this feature. My tutorial describes several simple but helpful recorded macros which I use nearly every day.

[Lupp]

Isn't it a hard limitation to a recorded macro in a case like the one under discussion here that it always will start at the cell or range that was selected when it was recorded while the user will expect it to start at the cell or range selected when it commences running?

[Zizi64]

I saw that OO VBA was way different

There is not any "VBA"; not exist such thing in the Apache OpenOffice and in the LibreOffice.

You must WRITE your macros if you want work efficiently with the macros, because the macro recorder has a very limited capability.

And you must write your macros by calling the API functions and procedures of the AOO/LO. (API: Applicarion Programming interface)
You can call them from the built-in StarBasic or from an another supported programming language.

(The macro recorder uses the Dispatcher feature for everything, but this makes the recorded macro inflexible. You'll have more freedom if you write your macros. Most of the controlling procedures are feasible without using of the Dispatcher.)

[Gingalone]

Thank you everybody, I have a better knowledge on AOO/LO macros now, I will have a look on the promising Tutorial suggested by MrProgrammer.

Please consider updating your signature

Done!

[MrProgrammer]

Isn't it a hard limitation to a recorded macro in a case like the one under discussion here that it always will start at the cell or range that was selected when it was recorded while the user will expect it to start at the cell or range selected when it commences running?

No, the macro uses the cell/range which was selected when it was called. All of the macros in my tutorial make use of that. The macro could move the selection to the cell that was selected when it was recorded, if the user records that action.