[Solved] Macro help

Creating a macro - Writing a Script - Using the API

[Solved] Macro help

Postby j68r » Fri Jun 12, 2020 3:36 pm

Apologies, I am very green when it comes to all things macro.

That said, I would like an elegant solution if possible that allows a macro to be called from the active sheet, that performs simple operation(s) on another specified cell in another specified sheet without changing focus from the current cell/sheet.

For example

call this macro from somewhere within sheet_1 (or any other cell/sheet)

that then cuts and pastes the content of cell_A1 in sheet 2

but in doing so doesn't move the focus from the active cell in sheet_1 (or any other cell/sheet) prior to the macro call being made.

TIA
Last edited by Hagar Delest on Sat Jun 13, 2020 10:27 am, edited 1 time in total.
Reason: tagged solved
AOO416m1(Build:9790) - Rev. 1844436 on Windows 10 Home
User avatar
j68r
 
Posts: 32
Joined: Sat Sep 12, 2015 8:57 pm

Re: Macro help

Postby FJCC » Fri Jun 12, 2020 5:24 pm

Do I understand correctly that you want to copy the content of Sheet2.A1 into whatever cell is currently active and clear Sheet2.A1?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro help

Postby j68r » Fri Jun 12, 2020 7:06 pm

No, I should have made that much clearer.

Let's say I'm in Sheet1.A1, I then call a macro via a button that cuts the contents in Sheet2.A1 and pastes the contents back in sheet2.A1

I want to remain in Sheet1.A1, a cut paste macro would leave me in sheet2.A1

I can write a macro that points me back to a specific cell in a specific sheet after the cut paste function completes but what I want is the focus to remain on whatever sheet/cell I was in prior to calling the macro, without having to write several macros with specific redirections to be used in several sheets.

Hope that's a little clearer. TIA
AOO416m1(Build:9790) - Rev. 1844436 on Windows 10 Home
User avatar
j68r
 
Posts: 32
Joined: Sat Sep 12, 2015 8:57 pm

Re: Macro help

Postby FJCC » Fri Jun 12, 2020 7:23 pm

j68r wrote:Let's say I'm in Sheet1.A1, I then call a macro via a button that cuts the contents in Sheet2.A1 and pastes the contents back in sheet2.A1

That says you cut from Sheet2.A1 paste back in the same cell. I assume there is a typo somewhere.

When you say "cut", I take that to mean that the source cell is left blank. Is that what you mean?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro help

Postby j68r » Fri Jun 12, 2020 7:38 pm

Yes, removing the contents of the sheet2.A1 cell to cache and then restoring back in to the sheet2.A1 cell.

That part of the macro isn't the issue, it's stopping that process (or any other) when called, making sheet2.A1 the new focus (active cell).
AOO416m1(Build:9790) - Rev. 1844436 on Windows 10 Home
User avatar
j68r
 
Posts: 32
Joined: Sat Sep 12, 2015 8:57 pm

Re: Macro help

Postby FJCC » Fri Jun 12, 2020 8:06 pm

Since cutting from and pasting into the same cell changes nothing, I assume there is some side effect of this process that you are interested in. Can you explain what the ultimate goal is? There may be a simpler way to do it.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro help

Postby Zizi64 » Fri Jun 12, 2020 9:05 pm

Hope that's a little clearer.

Not, really.

You can jump to an another sheet, and then you can jump back to the original sheet by a recorded or a written macro.

And you can get/set the cell values in the whole spreadsheet document without jumping from/to any cells of any sheets by calling some API function from your macro.

What do you want to achieve really?
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: 9429
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro help

Postby UnklDonald418 » Fri Jun 12, 2020 9:10 pm

If I understand correctly try something like
Code: Select all   Expand viewCollapse view
'save original cell
oOriginalCell = ThisComponent.getCurrentSelection()
'Pasting to another location will change focus, nothing can be done about that
'but after doing the other operations
' you can return to the original cell
ThisComponent.CurrentController.Select(oOriginalCell )
Last edited by UnklDonald418 on Sat Jun 13, 2020 3:11 am, edited 1 time in total.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1322
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Macro help

Postby j68r » Sat Jun 13, 2020 2:20 am

Thank you, that works a treat UnkDonald418

I had to remove the double dot in .getcurrentselection()

I appreciate it's a strange request but the result of adding these lines to the existing code is that it now behaves exactly as required.

thanks again.
AOO416m1(Build:9790) - Rev. 1844436 on Windows 10 Home
User avatar
j68r
 
Posts: 32
Joined: Sat Sep 12, 2015 8:57 pm

Re: Macro help

Postby Lupp » Sat Jun 13, 2020 1:38 pm

j68r wrote:Thank you, that works a treat UnkDonald418 ...
... the result of adding these lines to the existing code is that it now behaves exactly as required.
...
How would the existing code behave if the Sub is called while not only a single CellRange is selected?
The "added lines" seem to take the single-cell-case for assured, but will also work if the selection is a SheetCellRange or even if a SheetCellRanges selection was made.
UnklDonald418 wrote:Pasting to another location will change focus, nothing can be done about that
This is true if the pasting is done by a DispatchHelper command (".uno:Paste") which requires anyway to define the target by selecting a cell in advance ("uno.GotoCell").
Copying contents elsewhere by
Code: Select all   Expand viewCollapse view
anySheet.copyRange(targetCell.CellAddress, sourceRange.RangeAddress)
doesn't afflict the selection.
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


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests