Relative cell references in Calc macros

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
remembertc
Posts: 2
Joined: Wed May 24, 2017 10:05 pm

Relative cell references in Calc macros

Post by remembertc »

Hello,
I am a beginner in using macros.

I am working on a macro that searches for a flag in a column (denoted as "flag" in column C in the picture), then inserts a row below where it finds "flag". I have that part figured out.

After I insert this new row, I want to add strings that reference the preceding row. I know how to do this with formulas manually, but I need to automate it.

If I did it manually, in the Result Example:
A4 ="added "&A3
B4="adding to "&B3&" also"

What do I write in the macro to cause it to reference the preceding row's cells when it creates the data for the new row?

Thank you very much for the help.
Attachments
ooexample.png
OpenOffice 4.1.3 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Relative cell references in Calc macros

Post by Zizi64 »

I am working on a macro that searches for a flag in a column (denoted as "flag" in column C in the picture), then inserts a row below where it finds "flag". I have that part figured out.
Please upload your example .ods file with the embedded macro code here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
remembertc
Posts: 2
Joined: Wed May 24, 2017 10:05 pm

Re: Relative cell references in Calc macros

Post by remembertc »

I used the Record Macro function so I'm sure the code is not as concise as it could be.


Code: Select all

sub abc
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(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 0
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "flag"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = ""
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 0

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

rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 1
args2(1).Name = "Sel"
args2(1).Value = false

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

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:InsertRows", "", 0, Array())
end sub
Attachments
examplerelative.ods
(10.22 KiB) Downloaded 163 times
Last edited by RoryOF on Mon May 29, 2017 7:43 pm, edited 1 time in total.
Reason: Added [code] tags {RoryOF, Moderator]
OpenOffice 4.1.3 on Windows 10
Post Reply