Ask Your Question
0

Record macro

asked 2018-05-12 18:17:36 +0100

mitduffy gravatar image

updated 2018-05-19 15:23:09 +0100

Ratslinger gravatar image

Objective- Practice using “record Macro” Plan for practice macro Transfer five digits from one file = “stored numbers” To a file named = “deposit numbers” Problem: Works correctly the first time it is run. Transfers the number from “stored number” to “deposit numbers”

If I then change the original number in “stored numbers” and run the macro a second time it does not insert the new number. Instead the original numbers remain displayed.

Recorded Macro

sub Storednumbers
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 ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:OpenFromCalc", "", 0, Array())

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

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

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

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$D$4"

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

end sub

Any help appreciated

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2018-05-12 19:14:24 +0100

peterwt gravatar image

updated 2018-05-14 19:16:38 +0100

You seem to be missing the Copy action. When I record a macro to copy from one cell, A1, to another cell, C1, I get -


sub MyCop2
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 = "ToPoint"
args1(0).Value = "$A$1"

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

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

rem ----------------------------------------------------------------------
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())

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

end sub

EDITED 14/05/2018

@mitduffy Are the files “Stored numbers” and “Deposited numbers” Calc files? Where have you saved the macro? Looking at the macro code in your posting, the line

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

is used to open a file but it has no PropertyValue arguments preceding it, such as the file name. You are only opening one file yet you have 2. You then move to cell "$G$2" and paste into it but you have not copied anything to paste. You then go to cell "$D$4" and then end the macro. Are there parts of the macro you have not shown in your posting?

edit flag offensive delete link more
0

answered 2018-05-13 18:18:41 +0100

mitduffy gravatar image

Appreciate your response. Apologize for the delayed response -visit from a virus.

I am a newbie. May not have made an understandable explanation of my objective and the problem.

I have a file named “Stored numbers” that contains a short list of numbers. I have another file named “Deposited numbers” where I want to paste these numbers into. I realize I can do this without a macro, but I would like to learn how to do this with a macro.

So I want my practice macro to copy the numbers in “Stored numbers” and paste them in “Deposited numbers”.

The Recorded macro does that fine on the first “Go round”.

However if I then go back and change the numbers in “Stored Numbers”and the rerun the macro the numbers in “Deposited numbers” are not the new number but are the original numbers, unchanged.

That seems to suggest that during the second running of the macro it did not find or use the modified “Stored numbers” file as the revised source for the numbers to be copied and pasted in “Deposited numbers”.

Again appreciate your help.

edit flag offensive delete link more

Comments

Please use add a comment when responding to an answer - located lower left of answer you are responding to. Answers should be used for response to original question only.

If space is a concern, edit your original question and add the information noting which portion was the edit.

Ratslinger gravatar imageRatslinger ( 2018-05-19 15:46:53 +0100 )edit
0

answered 2018-05-19 15:54:08 +0100

Lupp gravatar image

updated 2018-05-19 15:55:17 +0100

The macro recorder is not a logger for KeyPress or MouseMove or any ButtonSomewhatt actions the user takes.
Any stuff obtained this way is passed to the created Sub as a TextConstant.
Never a recorded macro will stop to prompt you for an action or an input in one of these ways.

That's one of the many reasons recorded macros are mostly useless if not you study the fundamentals of programming macros in Basic to at least be able to rework the recorded ones including some input commands.

The macro recorder is emphasized to be an 'Optional Feature' under 'Advanced' options. You shoul take this for true.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-12 18:17:36 +0100

Seen: 132 times

Last updated: May 19 '18