Page 1 of 1
[Solved] Macro to copy values only
Posted: Fri Dec 08, 2023 5:08 pm
by 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?
Re: macro to copy values only
Posted: Fri Dec 08, 2023 5:37 pm
by 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).
Re: macro to copy values only
Posted: Fri Dec 08, 2023 6:00 pm
by 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?
Re: macro to copy values only
Posted: Fri Dec 08, 2023 6:07 pm
by 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.
Re: macro to copy values only
Posted: Fri Dec 08, 2023 6:19 pm
by 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.
Re: Macro to copy values only
Posted: Fri Dec 08, 2023 6:50 pm
by 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.)
Re: Macro to copy values only
Posted: Fri Dec 08, 2023 7:07 pm
by MrProgrammer
Nocton wrote: ↑Fri Dec 08, 2023 5:08 pm
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
Re: Macro to copy values only
Posted: Sat Dec 09, 2023 11:47 am
by 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.
Re: Macro to copy values only
Posted: Sat Dec 09, 2023 2:23 pm
by 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