[Solved] Macro for copying and pasting ranges with cellformats in Calc with Basic

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
havi
Posts: 9
Joined: Mon Oct 25, 2021 6:54 pm

[Solved] Macro for copying and pasting ranges with cellformats in Calc with Basic

Post by havi »

Hi
What I want to do is to copy a set of cells, with data and format, from one range in a sheet to another range in another or the same sheet. All sheets are visible. I have a procedure for copying data, but i lack one for copying cellformat as well.

First of all I know theres some examples on this stuff. Among others I've read Andrew Pitonyaks Useful Macro Information at https://www.pitonyak.org/ wich I find very useful.

The examples wich I have read and tried all use the dispatcher, and I cant get them to be stable when using them. Sometime they work and sometimes not. I have attached a sheet with code from those I have reffered down under and hope someone will have a look at those after reading all the post. (By the way, do anyone know if the api has an format-arry like there is a data-array?)

An example of what I have tried is this snippet by schiavinatto at
https://ask.libreoffice.org/t/macro-to- ... e/45980/9
which I first felt was excellent, but after several tests it did not work properly.

I have also tried Pitonyaks example for copying. His example which I have used is at page 75 in Useful Macro Information, BUT I have done some changes for to customize it for my use... and of course my solution fails (his solution works fine all the time, but it's just an example and I can not use like that.)

I'll bee away some days from now, but thanks to anyone who posts an answer :-)
Attachments
copyTest.ods
(28.91 KiB) Downloaded 106 times
Last edited by havi on Thu Jun 02, 2022 11:36 am, edited 1 time in total.
Regards, Harald
Libre Office 7.2.7.2 on Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro for copying and pasting ranges with cellformats in Calc with Basic

Post by JeJe »

Code: Select all

'write some code to make a selection or do it manually
'the following makes a copy of the current selection in the trans variable
trans =thiscomponent.currentcontroller.gettransferable
'write some code to make another selection the same size where you want trans inserted
'the following inserts what was copied
thiscomponent.currentcontroller.inserttransferable trans

Or make a selection manually and call the getransferable sub
then make a selection the same size where you want it copied and call settransferable sub

Code: Select all

global trans

Sub getransferable
trans =thiscomponent.currentcontroller.gettransferable
end sub

sub settransferable
thiscomponent.currentcontroller.inserttransferable trans
end sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
havi
Posts: 9
Joined: Mon Oct 25, 2021 6:54 pm

Re: Macro for copying and pasting ranges with cellformats in Calc with Basic

Post by havi »

JeJe wrote: Mon May 23, 2022 12:30 am ....

Code: Select all

global trans

Sub getransferable
trans =thiscomponent.currentcontroller.gettransferable
end sub

sub settransferable
thiscomponent.currentcontroller.inserttransferable trans
end sub
Thanks. It works. Harald
Regards, Harald
Libre Office 7.2.7.2 on Windows 10
Post Reply