Code for pasting the content of the clipboard (calc macro)

English
, ,
#1

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_QZymK7F5U and https://extensions.libreoffice.org/extensions/mri-uno-object-inspection-tool but wonder if this works for my purposes? And if it is secure?

#2

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.

#3

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 …

#4

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

#5

When included in my bigger macro I see no effect.

#6

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.

#7

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

#8

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 …

#9

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.

#10

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.

#11

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 …

#12

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”.

#13

After closing LO and restarting it the macros work. I had to add dim … as object for your variables.

Me or other people who will use this offline bookmark manager will have the URL from the file they want to save already in the clipboard and then click the button with the macro to add it automatically in the next empty line. The macro let them come to the name-cell to put in a name or description for the bookmark if they want.

Thanks for your help.

#14

This is would I needed to add to have it running inside my macro.
dim theDoc as object
dim myDispH as object
dim theCtrl as object
dim theSelection as object

#15

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.