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.
Relative cell references in Calc macros
-
- Posts: 2
- Joined: Wed May 24, 2017 10:05 pm
Re: Relative cell references in Calc macros
Please upload your example .ods file with the embedded macro code here.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.
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.
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.
-
- Posts: 2
- Joined: Wed May 24, 2017 10:05 pm
Re: Relative cell references in Calc macros
I used the Record Macro function so I'm sure the code is not as concise as it could be.
end sub
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())
- 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]
Reason: Added [code] tags {RoryOF, Moderator]
OpenOffice 4.1.3 on Windows 10