Macro Loops

Creating a macro - Writing a Script - Using the API

Macro Loops

Postby FalkirkFC » Sat Jun 22, 2019 12:42 am

Hello,

I'm looking to copy and paste values from cells from one worksheet to the other using a macro.

I can do this successfully using the macro recorder, however I'm need a bit of logic where if the cell (say b2) is populated it pastes the value into b3 instead.

Here's my existing 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 = "Nr"
args1(0).Value = 3

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

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

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

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

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

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

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

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

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

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

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


end sub


If anyone can help I would be much appreciated.

Thanks
:D
Open Office 3.1
Linux Mint
FalkirkFC
 
Posts: 1
Joined: Sat Jun 22, 2019 12:35 am

Re: Macro Loops

Postby Zizi64 » Sat Jun 22, 2019 6:41 am

The macro recorder has a limited capability. WRITE your macros instead of the recording them. You can write your code based on the API functions of the AOO/LO.
API: Application Programming Interface

You can get/set cell values by your macro more efficiently without any "jumping". You can address a cell/cellrange by its reference-name, or its coordinate values, or its textual name (if it is a named cell/cellrange).
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
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: 8768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Macro Loops

Postby Villeroy » Sat Jun 22, 2019 9:53 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27727
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests