Ask Your Question
0

"Record Macro"-Can't use "Record Macro" to open file.

asked 2018-05-30 13:32:36 +0200

mitduffy gravatar image

updated 2018-05-30 17:54:43 +0200

Ratslinger gravatar image

Problem: Seems it is not possible to create a Macro using “Record Macro” in a file named “Deposited Numbers” to Open another file name “Stored Numbers”. (Want to copy and use data from “Stored Numbers” in “Deposited Numbers”. )

Details: I am recording a macro in a file named “Deposit Numbers”. As one of the steps in this macro I proceed to open a file named “Stored Numbers”. (a source of numbers that will be used in the file “Deposit Numbers”.)

As might be expected, when the “Stored Numbers” file opens the “Deposit Numbers” file containing the macro is set aside, disappears from the screen.

After copying some numbers from the “Stored Numbers “file I need to get back to the “Deposit Numbers” File to complete the macro and access the “Record/Stop Macro” window.

After restoring the appearance of the “Deposit Numbers” file I select “Stop Recording”.

Problem is that when I run the macro it is evident, from the results that the Macro does not open the “Stored Numbers” file.
(Tests show that Saved number changes in “Stored Numbers” file do not appear in follow-up, reruns of the macro)

Here then the Recorded Macro

sub Macro_29
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 = "$F$1"

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

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

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

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

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

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
end sub

Appreciate any suggestions

edit retag flag offensive close merge delete

Comments

@mitduffy Please learn how to use formatting tools available from toolbar. There is also a Markdown Basics guide in right column when asking question.

Ratslinger gravatar imageRatslinger ( 2018-05-30 17:57:40 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-05-30 20:22:22 +0200

Jim K gravatar image

Recording macros is limited in many ways. It is also considered an experimental feature.

Instead of recorded dispatcher code, write API code, which is easier to read and in most cases more powerful. The command to open a document is loadComponentFromURL.

A good place to start learning about LO Basic is http://www.pitonyak.org/oo.php.

Alternatively, many people prefer to write LO macros in Python. See https://wiki.openoffice.org/wiki/Pyth....

edit flag offensive delete link more
0

answered 2018-05-31 03:46:48 +0200

Use a referencia diretamente na célula

Use the reference directly in the cell

='file:///C:/Users/Gustavo/Desktop/Teste_Macros.ods'#$Aba1.B5
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-30 13:32:36 +0200

Seen: 85 times

Last updated: May 31 '18