Hello, @libremacrouser
To copy and paste only cell value, you need to access Cell/CellRange DataArray property. Example that copies value from $Sheet1.A1
to $Sheet2.A2
:
oCopyCell = ThisComponent.Sheets.GetByName("Sheet1").GetCellRangeByName("A1")
oPasteCell = ThisComponent.Sheets.GetByName("Sheet2").GetCellRangeByName("A2")
oPasteCell.DataArray = oCopyCell.DataArray
If $Sheet1.A1
contains formula, only formula result will be copied.
Adopted for your example file:
oCopyCell = ThisComponent.Sheets.GetByName("Çizelge1").GetCellRangeByName("B2:B7")
oPasteCell = ThisComponent.Sheets.GetByName("Çizelge1").GetCellRangeByName("D2:D7")
oPasteCell.DataArray = oCopyCell.DataArray
— Edit 28.11.2018 —
Your new code is mixing (and messing) object properties and methods together. I will try to explain step by step.
Firsly, com.sun.star.table.CellRangeAddress
structure contains information about range coordinates, which includes 5 properties: Sheet
, StartColumn
, StartRow
, EndColumn
and EndRow
. Values of these properties define which sheet contains this range (Sheet
value), from which cell range starts (StartColumn
and StartRow
values) and at which cell ends (EndColumn
and EndRow
values). If StartColumn
= EndColumn
and StartRow
= EndRow
, then range starts and ends in the same column and in the same row, that means in the same cell. That is why com.sun.star.table.CellAddress
structure, which is used to define one single cell coordinates, contains only 3 properties: Sheet
, Column
and Row
, cause there is no need to separately define start and end row/column, they are the same. What all this means for you? If you want to iterate cells one by one, you don’t need to define range, you can use only CellAddress
, which should be easier to understand.
Next. As said earlier, CellAddress contains 3 properties. To define and access these properties values, you shall use their names. From your first sample:
cA = CreateUnoStruct("com.sun.star.table.CellAddress")
With cA
.Sheet = 0
.Column = 3
.Row = iCount6
End With
With these code you have created CellAdress structure whis defines cell position in spreadsheet. Now you can use cA.Sheet
, cA.Column
and cA.Row
to access values (notice, you can’t use indexes like cA(1), cA(2) to do this).
Next. Now we know what is CellAddress and how to access it’s properties. Lets take a look at your first colde line ir For loop:
ThisComponent.Sheets(0).GetCellByPosition(rA.1, rA.iCount6).DataArray
ThisComponent.Sheets(0) selects sheet with index 0, that is first sheet, named Çizelge1
in your sample file. GetCellByPosition(rA.1, rA.iCount6)
gives you and error because of rA.1
- this is incorrect syntax, variable names in Basic can not start with numbers. So, to correctly select cell object and assign it to variable we should use code like this:
SourceCell = ThisComponent.Sheets(cA.Sheet).GetCellByPosition(cA.Column, cA.Row)
This code selects sheet with index cA.Sheet
, which is 0, gets cell with position: column = cA.Column
, row = cA.Row
(that is column = 3, row = iCount6) and assigns this cell to SourceCell
variable.
Now lets copy only values to the next column. To do this, firstly we need to define cell position, where we want to put values. Next column has index cA.Column + 1
, row stays the same cA.Row
, so our code to get cell to paste values in is:
PasteCell = ThisComponent.Sheets(cA.Sheet).GetCellByPosition(cA.Column+1, cA.Row)
Now we have defined both source and destination cells. Lets copy data only:
PasteCell.DataArray = SourceCell.DataArray
So now PasteCell contains value from SourceCell. And here comes the final code with looping through rows with indexes 1 to 6, you can use this in your sample file:
Sub CopyPaste
cA = CreateUnoStruct("com.sun.star.table.CellAddress") 'defines Cell address structure
With cA 'assigns values to cA properties
.Sheet = 0
.Column = 1
.Row = iCount6
End With
For iCount6 = 1 to 6 'loops through rows 1 to 6
cA.Row = iCount6
SourceCell = ThisComponent.Sheets(cA.Sheet).GetCellByPosition(cA.Column, cA.Row)
PasteCell = ThisComponent.Sheets(cA.Sheet).GetCellByPosition(cA.Column+1, cA.Row)
PasteCell.DataArray = SourceCell.DataArray
Next iCount6
End Sub