[Solved] Copy cell above macro to be refined

Discuss the spreadsheet application

[Solved] Copy cell above macro to be refined

Postby Gingalone » Tue Mar 24, 2020 8:38 pm

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   Expand viewCollapse view
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
Last edited by robleyd on Wed Mar 25, 2020 1:49 am, edited 2 times in total.
Reason: Add green tick
Libre Office 6.0.7.3 on Ubuntu 18.04
Gingalone
 
Posts: 8
Joined: Fri Feb 06, 2009 8:27 pm
Location: Italy

Re: copy cell above macro to be refined

Postby Villeroy » Tue Mar 24, 2020 9:01 pm

Either you record macro and accept limitations (your problem is a cosmetic one) or you program true macros.
Code: Select all   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy cell above macro to be refined

Postby Gingalone » Wed Mar 25, 2020 1:06 am

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.
Libre Office 6.0.7.3 on Ubuntu 18.04
Gingalone
 
Posts: 8
Joined: Fri Feb 06, 2009 8:27 pm
Location: Italy

Re: Copy cell above macro to be refined

Postby robleyd » Wed Mar 25, 2020 1:12 am

Please consider updating your signature
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3393
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Copy cell above macro to be refined

Postby MrProgrammer » Wed Mar 25, 2020 1:33 am

Gingalone wrote: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.

Gingalone wrote:I found the code of the recorded macros was really complicated, almost unreadable.
Who cares? Don't to read ithe macro; just use it.

Villeroy wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3982
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Copy cell above macro to be refined

Postby Lupp » Wed Mar 25, 2020 2:00 am

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?
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2896
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Copy cell above macro to be refined

Postby Zizi64 » Wed Mar 25, 2020 8:36 am

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.)
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9431
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Copy cell above macro to be refined

Postby Gingalone » Wed Mar 25, 2020 3:12 pm

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.
robleyd wrote:Please consider updating your signature
Done!
Libre Office 6.0.7.3 on Ubuntu 18.04
Gingalone
 
Posts: 8
Joined: Fri Feb 06, 2009 8:27 pm
Location: Italy

Re: [Solved] Copy cell above macro to be refined

Postby MrProgrammer » Wed Mar 25, 2020 5:03 pm

Lupp wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3982
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Majestic-12 [Bot] and 5 guests