We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Code for pasting the content of the clipboard (calc macro) [closed]

asked 2017-05-08 12:25:58 +0200

inJesus gravatar image

updated 2017-05-08 12:37:39 +0200

That is what I cannot achieve by recording a macro and using ctrl shift v or ctrl v. Would be great if someone posts the code.

It must fit in my macro which is attached to the spreadsheet-file. And run on libreoffice without having people installed special addons, packages or need to change any settings or options.

I found https://www.youtube.com/watch?v=E_QZy... and https://extensions.libreoffice.org/ex... but wonder if this works for my purposes? And if it is secure?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-03 16:13:14.060001

1 Answer

Sort by » oldest newest most voted

answered 2017-05-08 13:57:18 +0200

Lupp gravatar image

updated 2017-05-08 15:06:30 +0200

Did you study the famous texts by Andew Pitonyak? You can get them here.

Based on what I learnd mainly from this source:

Sub PasteClipboard
    Dim noArg(4) As New com.sun.star.beans.PropertyValue
theDoc       = ThisComponent
myDispH      = createUnoService("com.sun.star.frame.DispatchHelper")
theCtrl      = theDoc.CurrentController
theSelection = theCtrl.GetSelection()
If NOT theSelection.SupportsService("com.sun.star.sheet.SheetCellRange") Then
    Exit Sub
End If
myDispH.executeDispatch(theCtrl, ".uno:Paste", "", 0, noArg())
End Sub

Pasting something you do not know in detail in advance may b dangerous in different ways.

Editing with respect to the comments:
Here my test document. Just pasted a few cell contents in there using the Sub.

edit flag offensive delete link more


Well did you tried it yourself? Do you think it could be dangerous? You know the code. Should I not trust you? You helped me. Why would you want to harm me ...

inJesus gravatar imageinJesus ( 2017-05-08 14:25:39 +0200 )edit

Hm. When trying it LO says: For security reasons you can't run this macro. For more information check the security settings.

inJesus gravatar imageinJesus ( 2017-05-08 14:35:21 +0200 )edit

When included in my bigger macro I see no effect.

inJesus gravatar imageinJesus ( 2017-05-08 14:43:31 +0200 )edit

Yes, I tested the code and it worked. Of course, the clipboard must have pastable content in advance. As target the sub uses the current selection (a cell mostly, I think). If you want to copy first that's another task.
You can run "macros" contained in documents only if you reduce he security level in your settings. In this simple case the code is obviously not "malign". However, pasting unknown content into your sheet may cause security issues. I'm not an expert in this field.

Lupp gravatar imageLupp ( 2017-05-08 15:00:25 +0200 )edit

I have not studied the 600+ pages of Andrew yet.

inJesus gravatar imageinJesus ( 2017-05-08 15:04:22 +0200 )edit

OK. Clipboard is filled, no copying needed in the macro. I reduced to low level macro security (from high) but still the problem. I already have some other macro codes running with high security level which work. Wonder why this one does not ...

inJesus gravatar imageinJesus ( 2017-05-08 15:07:04 +0200 )edit

I also never did read "the complete Pitonyak". Nonetheless I often find easily something I can use.
A general advice: Using LibreOffice you should try to avoid "macro" programming wherever possible. If you want to exchange documents with other users, this is essential. If there is one in your scope using Excel LibO macros simply won't work for him.

Lupp gravatar imageLupp ( 2017-05-08 15:10:43 +0200 )edit

The code works for me.
Do not set 'Macro Security' to 'Low'. 'Medium' is the adequate setting. It will ask once for macro permission when opening a document.

Lupp gravatar imageLupp ( 2017-05-08 15:13:24 +0200 )edit

Yes, agree. Thank you, Lupp. Well, this is the only spreadsheet I want to create right now which needs macros.

Do you know what I could do so the macros also work from within the file? Shall I make a new question as it is not part of the topic?

The paste macro runs in your test file also here. Strange ...

inJesus gravatar imageinJesus ( 2017-05-08 15:32:32 +0200 )edit

If "macros" are permitted for the file they will work. If not, there is a problem with the installed LibO.
Including some code from one "macro" into another may cause issues. To consider this in a specific case requires to know the surroundings.
A "macro" for pasting and nothing else is of no use, imo. Ctrl+V or Shift+Ctrl+V should always be simpler than calling a "macro".

Lupp gravatar imageLupp ( 2017-05-08 16:12:35 +0200 )edit

Question Tools

1 follower


Asked: 2017-05-08 12:25:58 +0200

Seen: 1,486 times

Last updated: May 08 '17