[SOLVED] To Copy Paste without Formulas

Hello!

I have a problem. I work with libre office macros and I want to copy a cell without its formulas. I only want to copy its number of value.

How can I do it?

In addition I added an example like my problem. In my example, I couldn’t copy paste without formulas with only data.

Thanks for your help!

Copy-Paste Example.ods

Editted for code:

I added my code here:

Sub CopyPaste

rA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
cA = CreateUnoStruct("com.sun.star.table.CellAddress")

For iCount6 = 1 to 5
	
With rA
  .Sheet       = 0
  .StartColumn = 1
  .StartRow    = iCount6
  .EndColumn   = 1
  .EndRow      = iCount6
 
End With
With cA
  .Sheet       = 0
  .Column      = 3
  .Row         = iCount6
End With

ThisComponent.Sheets(0).CopyRange(cA, rA)

next iCount6

End Sub

I added my new code here:

Sub CopyPaste

rA = CreateUnoStruct("com.sun.star.table.CellRangeAddress")
cA = CreateUnoStruct("com.sun.star.table.CellAddress")
	
	
For iCount6 = 1 to 5

ThisComponent.Sheets(0).GetCellByPosition(rA.1, rA.iCount6).DataArray 
ThisComponent.Sheets(0).GetCellRangeByPosition(cA.3, cA.iCount6, cA.3, cA.iCount6).DataArray
ThisComponent.Sheets(0).CopyRange(cA, rA)

next iCount6

End Sub

Copy-Paste Example1.ods

1 Like

You tagged this ‘base’, but you’re actually using ‘calc’, so I updated your tag

A command like
thisSheet.CopyRange(targetCellAddress, sourceCellRangeAddress) would always copy everything including cell styles and hard attributes. I don’t think this is what you want.
You wouldn’t only have to replace formulae with their results, but also to remove / change undue styles and attributes. That’s not reasonable, IMO.

Your example document contains “buttons” (shapes) trying to call macros with location=applcation. This cannot work with a download because it hasn’t access to your local macro containers.

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 StartRowvalues) and at which cell ends (EndColumn and EndRowvalues). 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.Rowto 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

Thanks for your reply but it is not what I want.

As you see, In my macro there is a for loop. Because of this I need to write my cell number like (1,0) instead of B1. I write it with → CreateUnoStruct(“com.sun.star.table.CellRangeAddress”)

I editted my question with my code. I hope I could explain my problem.

Best regards

I believe it is exactly what you want. In case you don’t want full cell object to be copied (with all the formulas, formatting, styles, borders etc.), you need to access .DataArray property of the Cell/CellRange to get only the values. I gave you an example to demonstrate how it works, you just need to adjust it.

As a dirty and fast solution: replace or comment line ThisComponent.Sheets(0).CopyRange(cA, rA) with this ThisComponent.Sheets(0).GetCellByPosition(cA.Column, cA.Row).DataArray = ThisComponent.Sheets(0).GetCellRangeByPosition(rA.StartColumn, rA.StartRow, rA.EndColumn, rA.EndRow).DataArray

This will only work, if you are reffering only ONE cell as your rA range address, cause source and destination array size must be equal, otherwise you will get an exception.

I again couldn’t work it :frowning: I think I am not capable of it. Worse, I don’t know how can I do it and how can I study to improve my this skill.

What is the reason why you “couldn’t work it”? Is there an error or what?

@SM_Riga I added my new code and my new LibreOffice.ods in my question

Please see my edit. Go through it step by step an I hope you will understand a bit more about what and why you should do

Thanks a lot @SM_Riga I both learned it in a detail way and worked it. I am really happy about it.

First of all consider thoroughly if relying on user code is unavoidable. In most cases it’s the wrong way. The Spreadsheet-Do is to use formulae wherever possible or one of the built-in tools. Custom programming for spreadsheets is a way to ensure incompatibility mainly.

Anyway the creation of one cell at a time to perform a few actions is inefficient. What you want to achieve seems to be a kind of Paste Special.... The most efficient way to do it is to use the respective dispatcher command .uno:InsertContents for as few (as large) ranges as possible.

===EDIT1 2018-12-01 14:15 CET concerning the “new code” posted by the OP recently===
-1- You still try to copy one cell at a time.
-2- If you have created the CellAddress and the RangeAddress you need to assign the values to their numeric properties. You don’t need a Cell object and a CellRange object nor any DataArray in addition.
-3- The syntax used for GetCellRangeByPosition (e.g.) is pure phantasy.

Something like

mySheets    = ThisComponent.Sheets
sourceSeet  = mySheets(0)
targetSheet = mySheets(5)
sourceRange = sourceSheet.GetCellRangeByName("C5:D12")
REM To copy everything, including formulae:
targetCell  = targetSheet.GetCellRangeByName("F11")
sourceSheet.CopyRange(targetCell.CellAddress, sourceRange.RangeAddress)
REM To copy data only (any type of data):
targetRange = targetSheet.GetCellRangeByName("F11:G18")
targetRange.SetDataArray(sourceRange.GetDataArray())

would do.
You may define the cell and/or the ranges using the…ByPosition variant, of course.
Both variants don’t offer means to control to nore detail what should be copied or not.

To get it a bit more flexible you need to use the Clipboard with Copy/Paste Special... in their form usable in code.
If you want help on this way, please ask a new qustion.