Ask Your Question
1

Calc: how can I use a macro to copy a range of cell addresses to the clipboard?

asked 2020-05-21 13:39:06 +0100

GeoS gravatar image

Hello, I've just started learning to use Calc and have no experience using macros so I am, in effect, a potato when it comes to spreadsheet programming.

My aim is to copy an array of cell addresses to the system clipboard so that I can paste them into a text file for regex manipulation.

I have managed to get the output to be printed in a dialogue box with the following:

Sub GetSelectedCellAddresses
    Dim myController as Object, myRange as Object

    myController = ThisComponent.getCurrentController()
    myRange = myController.getSelection()

    print myRange.AbsoluteName
End Sub

I am interested in getting the printed range of cell addresses copied directly to the system clipboard with a success notification.

Using this helpful resource, I've cobbled together this disaster:

Sub GetSelectedCellAddresses
    Dim myController as Object, myRange as Object, document as Object, dispatcher as Object

    myController = ThisComponent.getCurrentController()
    myRange = myController.getSelection()

    Dim args1() as String
    args1() = myRange.AbsoluteName
    dispatcher.executeDispatch(document, ".uno:Copy", "", 0, args1())

    msgbox "Cell addresses copied to the clipboard"

End Sub

Unsurprisingly, it doesn't work. Could anyone help me? Thanks in advance.

edit retag flag offensive close merge delete

Comments

This problem has a solution.

JohnSUN gravatar imageJohnSUN ( 2020-05-21 15:03:51 +0100 )edit

Thank you for pointing me in the right direction!

GeoS gravatar imageGeoS ( 2020-05-22 00:46:37 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2020-05-22 13:22:02 +0100

GeoS gravatar image

In addition to the solution mentioned by JohnSUN in the comments, I decided on a simple solution which negates the need to copy the range to the clipboard at all.

Rather than getting the printed range of cell addresses copied directly to the system clipboard, the following macro writes the cell addresses directly to a specified file:

Sub GetSelectedCellAddresses
    Dim myController as Object
    Dim myRange as Object

    myController = ThisComponent.getCurrentController()
    myRange = myController.getSelection()

    Dim FileNo As Integer
    Dim CurrentLine As String
    Dim Filename As String

    Filename = "~/Downloads/CellAddresses.txt"
    FileNo = FreeFile

    Open Filename For Output As #FileNo
    Print #FileNo, myRange.AbsoluteName
    Close #FileNo

    msgbox "Done. File CellAddresses.txt created in ~/Downloads directory."
End Sub

It creates the file and writes to it automatically, thereby eliminating the need to copy to the clipboard then paste into a empty text file. Alter the filename variable according to your Operating System and filesystem.

edit flag offensive delete link more

Comments

The direct solution is simple and quick. Good job!

JohnSUN gravatar imageJohnSUN ( 2020-05-22 13:31:08 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-05-21 13:39:06 +0100

Seen: 132 times

Last updated: May 22