[Solved] Copy and Paste Syntax
[Solved] Copy and Paste Syntax
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
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
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Re: Copy and Paste Syntax
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Copy and Paste Syntax
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
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
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Re: Copy and Paste Syntax
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
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
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Re: Copy and Paste Syntax
Hint for easy debugging through better coding style:Tobin wrote:- causes a "property or method not found" error.
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)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Copy and Paste Syntax
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.
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.
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())
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [Solved] Copy and Paste Syntax
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 .
Thanks Again
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 .
Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Re: [Solved] Copy and Paste Syntax
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:
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.
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
Thank you.
LO 5.2.7.2
Re: [Solved] Copy and Paste Syntax
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)
LO (7.6) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
Re: [Solved] Copy and Paste Syntax
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 10
- Joined: Sun Aug 21, 2016 1:24 am
Re: [Solved] Copy and Paste Syntax
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):
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
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
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