Ask Your Question
0

Absolute References when using a macro

asked 2018-05-17 14:55:20 +0200

Ding Duck gravatar image

updated 2018-05-17 15:24:06 +0200

Lupp gravatar image

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

rem get access to the document
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 flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2018-05-17 15:38:43 +0200

Lupp gravatar image

updated 2018-05-17 17:53:44 +0200

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
edit flag offensive delete link more
0

answered 2018-05-17 23:48:25 +0200

Ding Duck gravatar image

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

edit flag offensive delete link more

Comments

@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.

Ratslinger gravatar imageRatslinger ( 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.

Ratslinger gravatar imageRatslinger ( 2018-05-18 00:07:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-17 14:55:20 +0200

Seen: 45 times

Last updated: May 17