Macro Loop

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ivanhorvat
Posts: 3
Joined: Fri Nov 23, 2018 4:20 pm

Macro Loop

Post by ivanhorvat »

Hello everyone!

Can somebody help with the following problem, as I'm a noob and don't know how to do BASIC programming ... sorry in advance if someone can help I would really appreciate. This is my code:

Code: Select all

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


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 = "$C$2"

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

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$C$3"

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

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$4"

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

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$C$5"

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

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$C$6"

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

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = "$D$6"

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

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = "ToPoint"
args7(0).Value = "$C$7"

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

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$C$8"

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

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = "ToPoint"
args9(0).Value = "$C$9"

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

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = "ToPoint"
args10(0).Value = "$A$11:$G$20"

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "Nr"
args12(0).Value = 13

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$A$1"

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())


end sub
I would need to loop this part from $C$2 - 1-10

Code: Select all

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$2"  ---> this should contain values 1-10

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
and the result of copy-paste should be copied to sheet13 but with every iteration by 10 rows further, so I can have all results in that sheet13 for all iterations from 1-10 (i asumme that whole macro should be in a loop) :crazy:

Code: Select all

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = "Nr"
args12(0).Value = 13

dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args12())

rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name = "ToPoint"
args13(0).Value = "$A$1" ----> This part should iterate like this $A$1, $A$11, $A$21, $A$30 ...

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
Thanks in advance for every help

Best regards!!
Apache OpenOffice 4.1.6 - Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: MACRO Loop

Post by FJCC »

Using recorded macros to learn how to program OpenOffice is a bad idea. The Application Programming Interface is completely different than the dispatcher calls used in the recorded macros. If you explain exactly what you want the macro to do, someone can help you out. I tried to puzzle through the recorded macro but I still don't understand the goal in detail.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
ivanhorvat
Posts: 3
Joined: Fri Nov 23, 2018 4:20 pm

Re: MACRO Loop

Post by ivanhorvat »

So I have a drop-down list on "first" sheet where when I select number 1 some calculation happens and I have results displayed on that sheet in the table. When I select number 2 from a drop-down calculation generates new numbers/results in that table. So my goal is to pass through all options from the drop-down and copy paste result in the next sheet to have all 10 results one after another. hope this helps in explanation. And regarding learning yeah I totally agree but unfortunately, I need this so much so the easiest way for me "to do something" was recording :(
Apache OpenOffice 4.1.6 - Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: MACRO Loop

Post by RoryOF »

Why bother with the dropdown selections at all? Why not automate so that the ten values are processed sequentially?
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
ivanhorvat
Posts: 3
Joined: Fri Nov 23, 2018 4:20 pm

Re: MACRO Loop

Post by ivanhorvat »

I agree but I don't know how to do it :( and what will happen with copy paste?
Apache OpenOffice 4.1.6 - Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: MACRO Loop

Post by JeJe »

Whether or not its the best way, a loop would be something like this:

Code: Select all


dim i as long

	for i = 1 to 91 step 10 

    args13(0).Value ="$A$" & i '"$A$1" ----> This part should iterate like this $A$1, $A$11, $A$21, $A$30 ...

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

    rem ----------------------------------------------------------------------
    dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
    next


Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JulianR
Posts: 26
Joined: Mon Mar 12, 2018 9:41 am

Re: Macro Loop

Post by JulianR »

I am not sure what you want to achieve, but it sounds like Multiple Operations could be the answer to your problem. I have used them to have multiple results from a single formula with variable input (a 2d price table depending on height and width).
Apache OpenOffice 4.1.1 / LibreOffice 5.3 / LibreOffice 6.0 / LibreOffice 6.2 on Windows 7
Post Reply