Page 1 of 1

Macro Loop

PostPosted: Fri Nov 23, 2018 4:34 pm
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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!!

Re: MACRO Loop

PostPosted: Fri Nov 23, 2018 5:53 pm
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.

Re: MACRO Loop

PostPosted: Fri Nov 23, 2018 6:18 pm
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 :(

Re: MACRO Loop

PostPosted: Fri Nov 23, 2018 6:25 pm
by RoryOF
Why bother with the dropdown selections at all? Why not automate so that the ten values are processed sequentially?

Re: MACRO Loop

PostPosted: Fri Nov 23, 2018 6:56 pm
by ivanhorvat
I agree but I don't know how to do it :( and what will happen with copy paste?

Re: MACRO Loop

PostPosted: Fri Nov 23, 2018 6:57 pm
by JeJe
Whether or not its the best way, a loop would be something like this:

Code: Select all   Expand viewCollapse view

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



Re: Macro Loop

PostPosted: Fri Dec 07, 2018 11:57 am
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).