[Solved] Macro to copy values only

[Nocton]

I am using the following macro to copy a range of values. It is adapted from Useful Macro Information For OpenOffice.org By Andrew Pitonyak.

Code: Select all

  oRangeOrg = oSheet1.getCellRangeByName("G9:G16").RangeAddress
  oRangeCpy = oSheet1.getCellRangeByName("I9:I16").RangeAddress
  oCellCpy = oSheet1.getCellByPosition(oRangeCpy.StartColumn,_
    oRangeCpy.StartRow).CellAddress
  oSheet1.CopyRange(oCellCpy, oRangeOrg)

It works OK, but copies the cell formla as well as the value.
How can I just copy the values?

[Lupp]

Code: Select all

oRangeCpy.setDataArray(oRangeOrg.getDataArray())

This will not copy general formattings.
If you want formats (and probably more), you could combine

Code: Select all

oSheet1.copyRange(oCellCpy, oRangeOrg)
oRangeCpy.setDataArray(oRangeOrg.getDataArray())

The fist command will then copy everything, and the second command will overwrite formulas with results (obtained with the formulas in their original position).

[Nocton]

Thank, but I can't get this to work. I get the error message:
"BASIC runtime error.
Property or method not found: getDataArray."
Could you give me a complete replacement for my code?

[Lupp]

See https://api.libreoffice.org/docs/idl/re ... c1ec49ca00 .
If oRangeOrg supports the service com.sun.star.sheet.SheetCellRange ist must also supply ("export") the interface XCellRangeData which in turn offers the method getDataArray() (with empty parameter list).
Check your object/variable oRangeOrg.

[Nocton]

Sorry, Lupp, but I've read what you have said and looked at the link, but can't understand what you mean when you say:
"If oRangeOrg supports the service com.sun.star.sheet.SheetCellRange ist must also supply ("export") the interface XCellRangeData which in turn offers the method getDataArray() (with empty parameter list).
Check your object/variable oRangeOrg."

For me, a practical example of the amended code would be the most helpful to help me understand.

[Lupp]

(Please don't expect a personal tutorial and working examples in every case. You will need to learn and get along step by step.)

aoo110940VariantsConcerningCopyingCalcCellRanges.ods

(20.86 KiB) Downloaded 303 times

[MrProgrammer]

I am using the following macro to copy a range of values, How can I just copy the values [to a different range]?

Do you know how to do that using your keyboard and mouse? If so, then just record those actions to create a macro. It will work immediately. For this simple task you don't need to understand how to write a macro in Basic or Python or how to use the complex OpenOffice API (Application Programming Interface) with thousands of methods getCellRangeByName(), getCellByPosition(), copyRange(), setDataArray(), setDataArray() and properties RangeAddress, StartColumn, StartRow, CellAddress, ….
[Solved] Calculate functions and store only their results

Record selecting G9:G16 and copying the data to the clipboard, then continue with selecting I9:I16, and finish with using Paste Special. End the recording. Review example PasteValues in [Tutorial] Favorite Recorded Calc Macros.

My guess is that you've wasted more than an hour searching for a macro, modifying it for your situation, testing it, deciding it doesn't do what you want, and creating this topic. You can record a Copy → Paste Special → Text+Numbers+Date&Time macro in less than a minute. Don't attempt to modify the recorded macro or understand how it works. Just use it.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Nocton]

TY MrProgrammer. Of course "I know how to do that using your keyboard and mouse?"
I had previously done a recording, but the resulting code is rather long-winded and other posters in this forum advise against using recorded code. Also, as I have done a lot of programming in VBA over the years and some in OpenOffice for Base, I wanted something easy to understand for others and concise, so Andrew Pitonyak's suggestion looked ideal. And I shall need to use the code many times in my application on different sheets, so more concise the better..
It seemed to me that all I needed to do was replace .celladdress with something like .values in the line

Code: Select all

oCellCpy = oSheet1.getCellByPosition(oRangeCpy.StartColumn,_
    oRangeCpy.StartRow).CellAddress

would be straightforward, but clearly either there is no such option or no one seems to know what it is.

So I shall have to stick with the recording I made earlier.

[Nocton]

My final solution is to adapt the recorded macro to be called from the main macro code. This gives even better clarity and simplicity than the original programmed option with the recorded code doing exactly as I want.

Code: Select all

sub CopyRange (SheetNo as Integer,sRangeFrom,sRangeTo,sFinalPos as String)
dim document, dispatcher as object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

' Go to sheet
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Nr"
args1(0).Value = SheetNo
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args1())

' select range to copy from
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = sRangeFrom
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

' select where to copy & copy it, values only
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = sRangeTo
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
dispatcher.executeDispatch(document, ".uno:PasteOnlyValue", "", 0, Array())

' go to final desired cell location
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = "ToPoint"
args6(0).Value = sFinalPos
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args6())

end sub

Calling example:

Code: Select all

Sub TestCopy
CopyRange(2,"G24:G29","I24","B23")
end sub