[Solved] Assigning shortcut keys Only for present Calc file

Creating a macro - Writing a Script - Using the API
Post Reply
Muthuvel
Posts: 12
Joined: Sat Jun 20, 2020 7:42 pm

[Solved] Assigning shortcut keys Only for present Calc file

Post by Muthuvel »

I have written a macro to paste the values only in calc. I want to assign it to the shotcut key Ctrl+V.
That is, when i press control+v, the values must be pasted but not the formats. I am able to assign the Ctrl+V key to the macro.
The problem is, this changes the functioning of the Ctrl+V for all the calc documents. Other users get confused. When they try to paste the values with formats, it pastes the value only.
Is there a way to assign this control+V key to my macro for the particular calc document only.
I have given the code below (The code works perfectly.)

Code: Select all

Sub PasteValueOnly
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oFrame = ThisComponent.CurrentController.Frame
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVD"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = false
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4
oDispatcher.executeDispatch(oFrame, ".uno:InsertContents", "", 0, args7())
End Sub
Last edited by Muthuvel on Tue Jun 23, 2020 3:47 am, edited 2 times in total.
OpenOffice 4.1 on Windows 10
User avatar
MrProgrammer
Moderator
Posts: 4289
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Assigning shotcut keys to macros(Only for present calc d

Post by MrProgrammer »

Muthuvel wrote:I have written a macro to paste the values only in calc. I want to assign it to the shotcut key Ctrl+V.
I recommend not changing any of the fundamental shortcut keys:
(Mac) ⌘A (other pltforms) Ctrl-A (Select All)
(Mac) ⌘C (other pltforms) Ctrl-C (Copy)
(Mac) ⌘F (other pltforms) Ctrl-F (Find)
(Mac) ⌘N (other pltforms) Ctrl-N (New)
(Mac) ⌘O (other pltforms) Ctrl-O (Open)
(Mac) ⌘P (other pltforms) Ctrl-P (Print)
(Mac) ⌘S (other pltforms) Ctrl-S (Save)
(Mac) ⌘V (other pltforms) Ctrl-V (Paste)
(Mac) ⌘X (other pltforms) Ctrl-X (Cut)
(Mac) ⌘Z (other pltforms) Ctrl-Z (Undo)
and probably:
(Mac) ⌘B (other pltforms) Ctrl-B (Bold)
(Mac) ⌘I (other pltforms) Ctrl-I (Italic)
(Mac) ⌘U (other pltforms) Ctrl-U (Underline)
Muthuvel wrote:I am able to assign the Ctrl+V key to the macro. The problem is, this changes the functioning of the Ctrl+V for all the calc documents. Other users get confused.
Other users should have different accounts. Then they will use a different OpenOffice profile and will not be affected by changes you make for yourself.

Nonetheless, the macro you have created is very useful and I suggest that you use a new shortcut so it is available to all of your documents. It is one of my Favorite Recorded Calc Macros, along with three other Paste macros which you should consider.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Zizi64
Volunteer
Posts: 10432
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Zizi64 »

Is there a way to assign this control+V key to my macro for the particular calc document only.
There is not regular (built-in) method for this task.

You can assign a new Toolbar, Toolbar icons, new Menus, Menu items for a specific document, but you can not assign a Shortcut keys to a document only. The shortcut keys have a global scope: Some shortcuts are used in the Operating system, others are used in the office suite only. It is not a good idea to change the functionality of a common shortcut key.
You can choose from two of scopes in this case:
- OpenOffice (or LibreOffice)
- Calc (or other) application

(And you can assign new floating menu items to a Document in the LibreOffice - but you can not do it in the Apache OpenOffice.)

You have three options:
- use a toolbar icon assigned to a specific document,
- use a menu item (even a floating menu assigned to the LO) assigned a specific document,
- use an unused shortcut key assigned to the whole application or to the whole office suite (or you can modify a used one, but it will be your risk).
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Muthuvel
Posts: 12
Joined: Sat Jun 20, 2020 7:42 pm

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Muthuvel »

Mr.Programmer i am re-writing an existing VBA application in open office basic. I am referring your tutorials and articles very often. They are useful.
OpenOffice 4.1 on Windows 10
Muthuvel
Posts: 12
Joined: Sat Jun 20, 2020 7:42 pm

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Muthuvel »

Thank you . Now I understand that that control+v hot key is linked to the to the Global application. I cannot set it to individual documents.

I have already written a huge macro with 600 lines that prints some 200 pages of documents. Excel is used as front end for receiving the data. The data is injected into word. Then word prints selected pages of document based on business logic. It is written in VBA. I am re-writing the application in OpenOffice Basic. In Excel there is an option to assign macros to control+V in document level. It will not affect other documents. This application is already being used in 2500 different machines. My company plans to shift to Apache open office.

Users including me generally use control+v very often, unconsciously. Asking all the 2500 users to use a different menu option for copy paste is not feasible.
In calc control+v destroys the validation text in a cell. Also the validation error messages are removed. I am using calc as front end for receiving data. I need to display validation error messages. Also i need to stop users from entering wrong data. So, it is imperative for my application to disable control+v. Otherwise I have to tweak the control+v to paste values only.
I am giving a link below

viewtopic.php?f=25&t=94770

In this link Mr.Villeroy is suggesting a way. Following his idea, I assigned a macro to tweak control+v to paste values only. I saved the settings. Changed the file extension to zip. Then I opened the file with WinRAR . I separated the current.xml part. I transfered this current XML to to new file. But the new file behaves in the normal way. That is, the control+v destroys the validations and all the formatting. please explore if there is a way to help me. This is a nice forum of wise people.
Last edited by Muthuvel on Wed Jun 24, 2020 5:12 am, edited 3 times in total.
OpenOffice 4.1 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 30612
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Villeroy »

Excel is used as front end for receiving the data. The data is injected into word. Then word prints selected pages of document based on business logic.
Are you aware that this office suite comes with a database frontend? If Excel receives data from a standard SQL database, It is fairly easy to receive record sets and inject them into fairly professional report layouts.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Muthuvel
Posts: 12
Joined: Sat Jun 20, 2020 7:42 pm

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Muthuvel »

I have almost finished the application. Now only i realise this problem. My requirement is that, system must print different set of documents for different loan types. I have to implement this logic in open office base. It will require complete rewrite. I will try.
What about your idea to transfer the current.xml to the new file. Will it not work in my case?
OpenOffice 4.1 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 30612
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Villeroy »

Base is not a database. It is a frontend to databases. If you can connect a Base document to your original database, this might be just as good as a "Base database"
What most people refer to as "Base database" is a HSQL database wrapped into a Base document so it appears as a database in a single file. This can be a fairly good solution for your private DVD collection but does not hold any professional requirement. It is not necessary to set up such hybrid if you already have some connectable database.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Muthuvel
Posts: 12
Joined: Sat Jun 20, 2020 7:42 pm

Re: Assigning shotcut keys to macros(Only for present calc d

Post by Muthuvel »

Hi friends I found the solution.
Previously i tried as follows.
1.Renamed the calc document file and added the extension ".zip" at the end. (mycalcdocument.ods.zip)
2.right cliked the calc document fille and opened with windows explorer (Windows explorer opens zip files as normal folders)
3.moved into the zip file "\mycalcdocument.ods.zip\Configurations2\accelerator\current.xml" (This file is used in document level)
4.The file was empty
since the current.xml was empty, my settings for the key ctrl+v is not working. (from open office 3.1 versions, this file is empty and a .xcu file s used. It is in the user folder, maintained for a particular user)
I found another way. I got the idea from the link https://wiki.openoffice.org/wiki/Docume ... ation_File
I opened my notepad.
Entered the following lines and saved using saveas. Entered the file name as "current.xml" with quotes.

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE accel:acceleratorlist PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "accelerator.dtd">
<accel:acceleratorlist xmlns:accel="http://openoffice.org/2001/accel" xmlns:xlink="http://www.w3.org/1999/xlink">
 <accel:item accel:code="KEY_V" xlink:href=".uno:ClipboardFormatItems?SelectedFormat:short=1"  accel:mod1="true"/>
</accel:acceleratorlist>
Dragged this current.xml file into the accelerator folder. Closed the windows exploer "\mycalcdocument.ods.zip\Configurations2\accelerator\current.xml"
renamed the document file from mycalcdocument.ods.zip to mycalcdocument.ods
Opened mycalcdocument.ods.

Now the ctrl+V key works as i wanted. It pastes unformated text (Not any formats/validation). Other calc document and open office files behave normally.
The uno command in the current xml file (".uno:ClipboardFormatItems?SelectedFormat:short=1") does my work.
OpenOffice 4.1 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 10432
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [solved] Assigning shotcut keys Only for present calc do

Post by Zizi64 »

:super:
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply