Trying to create a macro that will copy a cell and paste it to notepad

[Masatowolfe]

Hi All,

I am trying to create a macro that will allow me to copy a cell to notepad (or an external program) so I don’t have to ctrl+c it each time. When i have recorded the macro it works fine, but after clearing the copied cell selection (for example selecting a different cell, entering data and pressing enter). The macro is no longer working.

A copy of the macro below:

sub CopyAdj

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 = "$I$6"

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 = "$H$2"

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


end sub

Currently using LibreOffice Version: 5.4.7.2 (x64)

Any assistance would be greatly appreciated.

(Edited for better readability by @Lupp )

[Xoristzatziki1]

The above code just copies content from $I$6 to $H$2. Does not copy any cell neither to Notepad nor to any other external program. So what exactly means it works when recording?

[Lupp]

Recorded “macros” will never prompt you for any action (input, selection of a cell …) of a kind as you did during the recording. The address of a cell you select or a sequence of keystrokes you do during the ‘Record Macro’ process always is recorded as a string constant.

To get a Sub allowing for any interaction you need to write it (or to add code to a recorded macro) using functions like ‘InputBox’ or finding the cell currently having the focus.

If you also want to automate the pasting into a different document under a different application (‘Notepad’ e.g.) you need to know how to pass the respective command to that application - if there is a way at all. There is a way to paste to a ‘Writer’ document (newly created e.g.) and finally to save it to a plain-text file, but how to do so is not described with a few words.

[Masatowolfe]

Hi Lupp,

I am not sure how to create a input box or the coding involved with it. If you can point me where to look for this information it would be greatly appreciated. I am just trying to set up a copy cell so that I can ctrl+v in notepad or another program as needed.

[Lupp]

I feel baffled now. To just copy a cell (or a range) there is the Edit > Copy action, by default linked to the shortcut Ctrl+C.
The copy goes to the clipboard then, and any different application can accept one (or more) of the formats provided by the clipboard for pasting.
What else do you want if no further automation is intended?

[Masatowolfe]

The cell I am trying to copy has formulas in it, which will result in a note. For example Hello there 123 Hows things? I am trying to avoid other people from messing with that formula by hiding it under a button and move the cell cursor to H2 for example.

[Lupp]

If not the protection of a sheet prohibits it, you can always select a cell by entering its address into the ‘NameBox’. Having done so, you can Ctrl+C its contents. This is even true for cells hidden by the enlargement of the area of another cell caused by so-called “merging”.
(Sorry. I don’t understand your rationale.)