Need to automate a process which must take the content of a highlighted range and copy it to an area where a set of calcs will produce some values. Not knowing too much about spreadsheets I was hoping to use macros that depend on the range definition facility - the macro would modify a definition with each execution and use the newly defined area to control the copy and pasting. Manually there's no issue, I can drop the current instance of a range definition, and then recreate it with the currently highlighted range, then (using navigator to move between ranges) take the range, copy it to another defined range and then copy the resulting calculated values I need back to another defined range. The dropping and recreating of the defined range works fine when done manually, but macro recording will just not pick it up - the previous definition of the range always remains in place.
Anyone have any suggestions? I'd be quite happy to do it another way - I simply need the content of a highlighted range of cells, within a much larger range of date values in a column, to be picked up so that a set of formulae can be applied to it. The start and end values of the range influences the outcome of the calculations.
thanks
Macro recording and defined ranges
Re: Macro recording and defined ranges
Select the range of cells to be exchanged.
Tools>Scenarios...
Replace he range's content
Tools>Scenarios...
Use the "list box" on the sheet to switch between both scenarios. Use the navigator's scenario-tool to edit properties of existing scenarios like color, comment and title.
Tools>Scenarios...
Replace he range's content
Tools>Scenarios...
Use the "list box" on the sheet to switch between both scenarios. Use the navigator's scenario-tool to edit properties of existing scenarios like color, comment and title.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro recording and defined ranges
thanks but I don't think scenarios will help - I really need to copy any random subset of values from a column that has thousands of dates in it. Along with this I need to be able to move between sheets and back to the original starting point, so I need to name ranges and be able to move between them. The issue is that the macro-recording doesn't appear to pick up on the modification of named ranges; similarly defined database ranges cause the same issue.
Re: Macro recording and defined ranges
Unlike Excel scenarios, Calc scenarios are not restricted in size. You can define entire sheets as scenarios. With scenarios you can use the very same range name and switch the underlying data of that range.
If you can not use the built-in feature set nor databases for what you try to do then I would recommend to stay with Excel. Rewriting all VBA macros is not an option in most cases. Once you decided to write a macro-"application" you are married with one particular software.
If you can not use the built-in feature set nor databases for what you try to do then I would recommend to stay with Excel. Rewriting all VBA macros is not an option in most cases. Once you decided to write a macro-"application" you are married with one particular software.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Macro recording and defined ranges
I've noticed that when recording the macro, while all movement between ranges and copying from one range to another seems to be trapped perfectly, the commands for defining a range aren't; that portion of the macro is saved as a commented command -
rem dispatcher.executeDispatch(document, ".uno:DefineName", "", 0, Array()).
I'm wondering if this means that this UNO command just isn't available at all or whether the macro recorder is just not able to produce it properly yet. If the command is available does anyone know what input args it needs to define a range.
rem dispatcher.executeDispatch(document, ".uno:DefineName", "", 0, Array()).
I'm wondering if this means that this UNO command just isn't available at all or whether the macro recorder is just not able to produce it properly yet. If the command is available does anyone know what input args it needs to define a range.
Re: Macro recording and defined ranges
I can't see how to change the range for a defined scenario, or how to reference the scenario's range in a formula - can you point me at relevant documentation or can you outline it for me?Villeroy wrote: With scenarios you can use the very same range name and switch the underlying data of that range.
Re: Macro recording and defined ranges
Have a look at the attached file. No, it does not change the scenario range. It exchanges the values of the same range.
- Attachments
-
- scenario.ods
- (13.42 KiB) Downloaded 496 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice