[Solved] Copy and Paste Syntax

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

[Solved] Copy and Paste Syntax

Post by Tobin »

I'm sure a simple "Clipboard" Copy and Paste from one cell to another must look something like the following, but, I'm obviously missing something (besides brains).

REM ***** BASIC *****
Sub Main

Dim Cell1, Cell2 As Object
Dim Sheet As Object

Sheet = ThisComponent.Sheets(0)

Cell1 = Sheet.getCellByPosition(1,1)
Cell2 = Sheet.getCellByPosition(2,2)
Cell1.Copy
Cell2.Paste

End Sub

Can someone help me please?

Thanks
Last edited by Tobin on Thu Mar 12, 2009 3:26 pm, edited 1 time in total.
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Copy and Paste Syntax

Post by FJCC »

Here is one way to do this.

Code: Select all

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(0,0)
Cell2 = Sheet.getCellByPosition(0,1)
Sheet.copyRange(Cell2.CellAddress,Cell.RangeAddress)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Copy and Paste Syntax

Post by Tobin »

Thanks FJCC,
Interesting structure, I would have never guessed :-) .
This looks like it would work for a "Range" Copy and Paste as well. But -

REM ***** BASIC *****
Sub Main

Dim Cell1, Cell2 As Object
Dim Sheet As Object

Sheet = ThisComponent.Sheets(0)

'Cell1 = Sheet.getCellByPosition(1,1)
'Cell2 = Sheet.getCellByPosition(2,2)
'Sheet.copyRange(Cell2.CellAddress,Cell1.RangeAddress)

Cell1 = Sheet.getCellRangeByPosition(0,0,0,2)
Cell2 = Sheet.getCellRangeByPosition(1,0,1,1)
Sheet.copyRange(Cell1.CellAddress,Cell2.RangeAddress)

End Sub

- causes a "property or method not found" error.

Could you show other simple Copy and Paste structures?

Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Copy and Paste Syntax

Post by Tobin »

OOPS!
I should have looked at the Global Index first ;)

Cell1 = Sheet.getCellRangeByPosition(0,0,0,2)
Cell2 = Sheet.getCellByPosition(1,0)
Sheet.copyRange(Cell2.CellAddress,Cell1.RangeAddress)

I'm still interested in other structures.

Thanks
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy and Paste Syntax

Post by Villeroy »

Tobin wrote:- causes a "property or method not found" error.
Hint for easy debugging through better coding style:

Code: Select all

Cell1 = Sheet.getCellRangeByPosition(0,0,0,2)
addrTarget = Cell1.getCellAddress()
Cell2 = Sheet.getCellRangeByPosition(1,0,1,1)
addr.Source = Cell2.getRangeAddress()
Sheet.copyRange(addrTarget,addrSource)
Now you get the same error in the second line of above snippet, which tells you that the cell-address of Cell1 could not be retrieved. What is the cell-address of A1:A3? There is none because it is not a single cell.
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Copy and Paste Syntax

Post by FJCC »

The method I showed does work for cell ranges. The source is then a CellRange as you would expect, the destination is still a cell address, which is the upper left cell of the range into which the source will be copied. So the statement is

Sheet.copyRange(TargetCell.CellAddress,SourceCellRange.RangeAddress)

You can also use dispatcher calls. If you record a macro to copy from the current cell to cell A2, you will get code like this.

Code: Select all

dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$A$2"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
Finally, for carefully controlled situations you can use

Cell2.Value = Cell1.Value

or
Cell2.String = Cell1.String

This is prone to errors. If Cell1 contains text and you assigns its value to Cell2, then Cell2 has the value zero.


I see others posted while I was typing! Here are some other methods.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Tobin
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: [Solved] Copy and Paste Syntax

Post by Tobin »

Howdy,

Thank you both for replying.
<Cell2.String = Cell1.String> was what I was looking for - DA. Of course now that I'm looking at it, it seems so obvious. CopyRange is valuable to me as well - so I you gave me more than I expected :D .

Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
dech
Posts: 21
Joined: Wed Sep 01, 2010 12:32 am

Re: [Solved] Copy and Paste Syntax

Post by dech »

Hello guys,
Im looking for almost exact code, but I want it (copy and) paste all properties but not objects, sometimes not values ect. It means setting some flags for "copyrange()". How that code woudl be looking? I dont want to use macro recorder(UNO) because I need it work in hidden sheet.
Im ended up with this:

Code: Select all

sub copy_range_and_paste_it_to_another_cell

oSheet = thisComponent.Sheets.getByIndex(0)
oSheet1 = thisComponent.Sheets.getByIndex(1)

source = oSheet.getCellRangeByName("A1:z100").getRangeAddress()
destination =  oSheet1.getCellByPosition(0,0).getCellAddress()

oSheet.copyRange( destination , source )
End sub
but this code copies/paste ALL properties (values,text,formats,formulas,objects....) and it would be nice to let it paste only some of them.
Thank you.
LO 5.2.7.2
Jan_J
Posts: 167
Joined: Wed Apr 29, 2009 1:42 pm
Location: Poland

Re: [Solved] Copy and Paste Syntax

Post by Jan_J »

Można. Próbka kodu jak to zrobić:

Code: Select all

sub test()
    doc = thisComponent
    ark1 = doc.Sheets(0)
    ark2 = doc.Sheets(1)
    kopiujObszarWInneMiejsce(ark1, "C3:G9", ark2, "B4")
end sub

sub kopiujObszarWInneMiejsce(ark1, zZakresu, ark2, doKomorki)
    r1 = ark1.getCellRangeByName(zZakresu)
    r2 = ark2.getCellRangeByName(doKomorki)
    ark1.copyRange(r2.CellAddress, r1.RangeAddress)
end sub
JJ ∙ https://forum.openoffice.org/pl/
LO (7.6) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Copy and Paste Syntax

Post by Villeroy »

The API does not have any paste-special equivalent.

rgTarget.setDataArray(rgSource.getDataArray()) dumps raw values of type double, integer or (empty) string. All strings will be taken as literal strings.

rgTarget.setFormulaArray(rgSource.getFormulaArray()) dumps all formulas of type (empty) string. A formula can be a plain integer or double as string ("3.14159") with decimal point and digits only, an ISO date/time ("yyyy-mm-dd hh:mm:ss"), an English boolean ("true", "false"), possibly some other numeric expression in English notation (don't know), an "ordinary string", a numeric string with leading apostrophe or an evaluated formula expression with leading =.

Both DataArrays and FormulaArrays are arrays within an array like the following array for 3 rows and 4 columns: ((A1,B1,C1,D1),(A2,B2,C2,D2),(A3,B3,C3,D3))

An array formula requires no equal sign nor braces: rgTarget.setArrayFormula("OFFSET(A1;1;2;3;4)")

As a decent programmer you work with templates and styles, just like you always do when operating an office suite by the GUI:
rgTarget.CellStyle = "MySophisticatedCellStyleWithLotsOfAttributes" saves a lot of work if you loaded the right template with your predefined styles.

Copying hard formatted attributes, conditional formats, merging is complicated but possible if you learn how to use http://www.openoffice.org/api/docs/comm ... anges.html and property sets.

Transfer annotations programmatically one by one.

Things become really hard with text cells having multiple formatted chunks of text or hyperlink fields. Cells with http://www.openoffice.org/api/docs/comm ... #FORMATTED
See http://www.oooforum.org/forum/viewtopic.phtml?t=47340 which copies such contents from Calc to Writer.
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
ddavis1086
Posts: 10
Joined: Sun Aug 21, 2016 1:24 am

Re: [Solved] Copy and Paste Syntax

Post by ddavis1086 »

Hello. I am in need of some help please. Being new here and not really a programmer, what I want to do I think is really simple.
Really don't understand why it does not work.

In Calc, I have 12 sheets.

The source sheet is sheet 1 (with the first sheet being 0 as I have learned) that has columns from A to K

There are 10 destination sheets with the first one starting at sheet 2. They contain no data.

What I need to do is copy rows starting in column B from the source (sheet 1) into the destination sheet (sheet 2) and then repeat until rows in column B though column K are copied into
the sheets 2 to 12. The column destination will always remain in the same place, column 1.

I can kinda get this to work but have run into an error that does not make sense to me.

Here is the code (sorry I do not know how to paste it the code area):

Code: Select all

sub copy_range_and_paste_it_to_another_cell

oSheet = thisComponent.Sheets.getByIndex(1)
oSheet1 = thisComponent.Sheets.getByIndex(2)

source = oSheet.getCellRangeByPosition(1,0,0,100).getRangeAddress()
destination =  oSheet1.getCellByPosition(1,0).getCellAddress()

oSheet.copyRange( destination , source )
End sub
The issue is this. When I change the getCellRangeByPosition from 0 to 1, which I think would be the next column to get my source from, I get this error:

IndexOutOfBoundsException

If I leave it a 0 then it copies column 0 into my source sheet. Why cannot I set the source to be column 1 from the source sheet, there is data there.
Please be so kind to help me.

Thank you,

Dennis
OpenOffice 4.1.2 on CentOS
Post Reply