# Absolute References when using a macro

Could anyone assist in being able to use Absolute References when using macros's? I need to insert a new Column into a sheet and populate this with a formulae. EG if we have COL1 COL2 COL 3 I wish to insert a new Column (COL1.1) to the left of COL2, irrespective of where the Active cell is currently.
Below is the macro recorded, but will only insert a column relative to where the cursor is currently on the sheet.

sub TEST_INSERT_C
rem define variables
dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

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

dispatcher.executeDispatch(document, ".uno:InsertColumnsBefore", "", 0, Array())

dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$1"

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

dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "StringName"
args4(0).Value = "Test Member Value"

dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args4())

dispatcher.executeDispatch(document, ".uno:JumpToNextCell", "", 0, Array())

end sub


Any suggestions would be appreciated
Cheers

(Edited for better readability by @Lupp )

edit retag close merge delete

Sort by » oldest newest most voted

Based on the "macro" you recorded the reworked version below may do what you want. It is, imo, not fully consistent with the text of your question. You may need to replace the "$C$1" with "$B$1" e.g.

Be aware of the fact that recorded macros without some shaping and adding-in cannot be used for multi-step tasks that need detailed control. Now the code:

sub TEST_INSERT_C
rem define variables
dim document   as object
dim dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args0(0) as new com.sun.star.beans.PropertyValue
args0(0).Name = "ToPoint"
args0(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args0())
dispatcher.executeDispatch(document, ".uno:InsertColumnsBefore", "", 0, Array())
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "StringName"
args1(0).Value = "Test Member Value"
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args1())

end sub

more

Thank you for your reply. That did the trick! Would you know of any documents that explain the coding of Macro's. Mainly to understand the structure behind them. Thanks again for your reply Cheers

more

@Ding Duck Please do not respond to answers with an answer. Instead use the add a comment under the respective answer. Answers should be used for responding to original question.

If you have been given an answer your question, please tick the ✔ (upper left area of answer accepted). It helps others to know there was an accepted answer.

( 2018-05-18 00:06:10 +0200 )edit

Finally, for the most coverage in macro please refer to Open Office Macros Explained by Andrew Pitonyak. The PDF link is -> OOME.

( 2018-05-18 00:07:53 +0200 )edit