Macro Loop

Creating a macro - Writing a Script - Using the API

Macro Loop

Postby ivanhorvat » Fri Nov 23, 2018 4:34 pm

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!!
Apache OpenOffice 4.1.6 - Windows 10
ivanhorvat
 
Posts: 3
Joined: Fri Nov 23, 2018 4:20 pm

Re: MACRO Loop

Postby FJCC » Fri Nov 23, 2018 5:53 pm

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.
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: 7789
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: MACRO Loop

Postby ivanhorvat » Fri Nov 23, 2018 6:18 pm

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
ivanhorvat
 
Posts: 3
Joined: Fri Nov 23, 2018 4:20 pm

Re: MACRO Loop

Postby RoryOF » Fri Nov 23, 2018 6:25 pm

Why bother with the dropdown selections at all? Why not automate so that the ten values are processed sequentially?
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31543
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: MACRO Loop

Postby ivanhorvat » Fri Nov 23, 2018 6:56 pm

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
ivanhorvat
 
Posts: 3
Joined: Fri Nov 23, 2018 4:20 pm

Re: MACRO Loop

Postby JeJe » Fri Nov 23, 2018 6:57 pm

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


Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1192
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro Loop

Postby JulianR » Fri Dec 07, 2018 11:57 am

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
JulianR
 
Posts: 26
Joined: Mon Mar 12, 2018 9:41 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests