# [SOLVED] To Copy Paste without Formulas [closed]

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.

C:\fakepath\Copy-Paste Example.ods

Editted for code:

Sub CopyPaste

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

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


C:\fakepath\Copy-Paste Example1.ods

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by erAck close date 2018-12-04 13:58:32.478678

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

( 2018-11-27 18:17:17 +0200 )edit

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.

( 2018-11-28 17:55:14 +0200 )edit

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.

( 2018-11-28 17:58:45 +0200 )edit

Sort by » oldest newest most voted

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.

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 ...

more

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

( 2018-11-27 06:56:01 +0200 )edit

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.

( 2018-11-27 13:56:51 +0200 )edit

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

( 2018-11-27 13:58:02 +0200 )edit

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.

( 2018-11-27 14:02:34 +0200 )edit

I again couldn't work it :( 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.

( 2018-11-28 06:13:35 +0200 )edit

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

( 2018-11-28 13:10:58 +0200 )edit

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

( 2018-11-28 15:31:16 +0200 )edit

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

( 2018-11-28 23:23:12 +0200 )edit

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

( 2018-11-30 08:55:16 +0200 )edit

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")
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.

more