Ask Your Question

[SOLVED] To Copy Paste without Formulas [closed]

asked 2018-11-26 15:24:43 +0200

libremacrouser gravatar image

updated 2018-12-04 12:45:52 +0200


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!

C:\fakepath\Copy-Paste Example.ods

Editted for code:

I added my code here:

Sub CopyPaste

rA = CreateUnoStruct("")
cA = CreateUnoStruct("")

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("")
cA = CreateUnoStruct("")

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 flag offensive 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

EasyTrieve gravatar imageEasyTrieve ( 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.

Lupp gravatar imageLupp ( 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.

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

2 Answers

Sort by » oldest newest most voted

answered 2018-11-26 17:51:40 +0200

updated 2018-11-28 23:22:02 +0200

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, 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 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("")
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)

edit flag offensive delete link more


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

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

Best regards

libremacrouser gravatar imagelibremacrouser ( 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.

SM_Riga gravatar imageSM_Riga ( 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

SM_Riga gravatar imageSM_Riga ( 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.

SM_Riga gravatar imageSM_Riga ( 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.

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

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

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

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

libremacrouser gravatar imagelibremacrouser ( 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

SM_Riga gravatar imageSM_Riga ( 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.

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

answered 2018-11-28 17:43:10 +0200

Lupp gravatar image

updated 2018-12-04 14:27:28 +0200

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

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2018-11-26 15:24:43 +0200

Seen: 250 times

Last updated: Dec 04 '18