Copy with the same options as the UI

English
, ,
#1

I wish to copy ranges with the at least some of the flexibility shown by the various forms of Copy / Paste in the UI, but without using UNO, because that seems to require me to move my cursor around and change my selection, which are unwelcome side effects. I want to be able to select whether to copy formulae, literal text, literal numbers (without necessarily distinguishing dates from other numbers) and formats.

My current use case is to copy one range to another, copying formatting and formulae, but not literal values (text/numbers/dates). I can easily and quickly copy a range with copyRange, but that copies all content and formatting. It is straightforward (if a little tedious) to iterate through all rows and columns of a range and check the cell.Type against my options and selectively copy those matching my content options, but this proves relatively slow and I do not know the best way to copy formatting (including styles and conditional formatting).

Is there a single API function that provides this flexibility, or a small number of such functions?

#2

You can use cursors to make specific searches by type of content. Copy, format and delete only the content. No need to change the selection.

If you attach a specific file of what you have or what you expect, we will gladly help you.

#3

I would solve this problem with two consecutive copies. The first one is the one you described: use copyRange to copy all the contents, The second one is to copy and paste formulas from the source range to the target range using

targetRange.setFormulaArray(sourceRange.getFormulaArray())

#4

Possibly, you want to work with templates instead of copying around formulas and formatting.

#5

@JohnSUN getFormulaArray sounds promising, but, as I said (and you quoted!), copyRange copies too much: I only want the formulae. Perhaps there are other get|setXxxArray functions that help?

@elmau Can you point me to the documentation of functions that let me copy using cursors?

#6

So the need for “copying formatting” has already disappeared?

Yes, it is, but it does it almost instantly. You will delete unnecessary content with the following operation.
Have you tried it yet? Or are you just considering this possibility “theoretically”?
Once more:

#7

Ah, many thanks, I did not realise that — and I fear I had not yet had time to try it out, which I shall do soon, which should deal with my current case. Do you also know how to copy only literal values / only text / only numbers / some combination with/without formatting? I may find myself needing those in future.

I feel that my original question states clearly what I want: to copy one range to another with the flexibility of Paste Special to include various types of content and optionally formatting. Since that has many possible variations, it is hardly possible to give a single example. Perhaps you feel I may be asking the wrong question, and that this would become evident from my example, but I would genuinely prefer to learn a general technique applicable in varying situations: my current case suggests the other variants, and it is more satisfying to learn a general technique than bits and bobs for special cases. But thank you both for your help, which is, as ever, most welcome!

#8

The restriction of the content does not happen on copy but on paste. You can record a macro while using paste special dialog. Then you see, which arguments the .uno:InsertContents command needs.

#9

I use this list for the Flags parameter. :slightly_smiling_face:

1 Like
Macros unable to reproduce copy paste steps in LibreOffice 7.6.4.1 Windows 10 64 bit
#10

As I said, I do not want to use UNO, e.g. a recorded macro (which is what I did first), as that requires me (if I understand aright) to position to and select the target range, and I do not want those side effects. Or do the recorded arguments somehow show to call the API directly?

#11

The recorded macro uses the dispatcher. It is often easier to use the dispatcher than doing all the needed steps manually.

You might want to remember the old cell position and go back to it after pasting the content into the target range.

#12

Sorry, forgot to answer this question: No, I just meant that copyRange copied formatting (wanted) and literal content (unwanted) as well formulae (wanted), and did not realise you said the next step would correct that.

I have now, and I am afraid it did not work as required:

  • The formulae were not relocated.
  • Literal content (strings and numbers) was also copied.
    • I saw with the debugger that these were present in the 2-D array of formulae

Seeing this suggests one could:

  • get the formula array from the target range after copyRange and then
  • clean up the array (replacing literal content with empty strings) before calling setFormulaArray.

I take it this would operate appreciably faster than accessing the cells one by one. This approach could also be adapted for many of the other options of Paste Special, which would be nice! I tried replacing elements of the array, but I was told that was something like an invalid operation on object. The next thing to try is creating a new 2-D array of strings, but that must wait for another time.

#13

In the short term, yes, but I find the code produced terribly bloated and hard to maintain.

I thought of that; it ought to work, but makes everything messier.

You may be right, but that would involve a lot of work restructuring an existing spreadsheet :unamused:

#14

When I talked about FormulaArray I meant something like this:

Sub copyFormattedFormulas(oSourceRange As Variant,oTargetRange As Variant)
Dim aFormulas As Variant, i As Long, j As Long 
	oTargetRange.getSpreadsheet().copyRange(oTargetRange.getCellByPosition(0, 0).getCellAddress(), oSourceRange.getRangeAddress())
	aFormulas = oTargetRange.getFormulaArray()
	For i = LBound(aFormulas) To UBound(aFormulas)
		For j = LBound(aFormulas(i)) To UBound(aFormulas(i))
			If Left(aFormulas(i)(j),1) <> "=" Then aFormulas(i)(j) = ""
		Next j
	Next i
	oTargetRange.setFormulaArray(aFormulas)
End Sub

I suggest using .clearContents() instead of the second step. Something like this:

Sub copyAny(oSourceRange As Variant,oTargetRange As Variant, Optional aInclude As Variant)
Dim nFlag As Long 
Dim sExclude As String 
	If IsMissing(aInclude) Then aInclude = Array("VALUE","DATETIME","STRING","ANNOTATION","OBJECTS")
	oTargetRange.getSpreadsheet().copyRange(oTargetRange.getCellByPosition(0, 0).getCellAddress(), oSourceRange.getRangeAddress())
	nFlag = 0
	For Each sExclude In aInclude
	Select Case UCase(Trim(sExclude))
		Case "VALUE"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.VALUE
		Case "DATETIME"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.DATETIME
		Case "STRING"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.STRING
		Case "ANNOTATION"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.ANNOTATION
		Case "FORMULA"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.FORMULA
		Case "HARDATTR"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.HARDATTR
		Case "STYLES"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.STYLES
		Case "OBJECTS"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.OBJECTS
		Case "EDITATTR"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.EDITATTR
		Case "FORMATTED"
			nFlag = nFlag + com.sun.star.sheet.CellFlags.FORMATTED
	End Select 
	Next sExclude
	oTargetRange.clearContents(nFlag)
End Sub

To test these snippets you can use something like

Sub tests
	oSource = ThisComponent.getSheets().getByIndex(0)
	oSourceRange = oSource.getCellRangeByName("B3:E26")
	oTargetRange = oSource.getCellRangeByName("H11:K34")
	copyFormattedFormulas(oSourceRange,oTargetRange)
	Print "After call copyFormattedFormulas()"
Rem constants group CellFlags
Rem These constants select different types of cell contents. The values can be combined. 
Rem VALUE	selects constant numeric values that are not formatted as dates or times.  
Rem DATETIME	selects constant numeric values that have a date or time number format.  
Rem STRING	selects constant strings.  
Rem ANNOTATION	selects cell annotations.  
Rem FORMULA	selects formulas.  
Rem HARDATTR	selects all explicit formatting, but not the formatting which is applied implicitly through style sheets.  
Rem STYLES	selects cell styles.  
Rem OBJECTS	selects drawing objects.  
Rem EDITATTR	selects formatting within parts of the cell contents.  
Rem FORMATTED	selects cells with formatting within the cells or cells with more than one paragraph within the cells

	copyAny(oSourceRange,oTargetRange,Array("Value","Strings"))
	Print "After call copyAny(Value,Strings)"
	copyAny(oSourceRange,oTargetRange)
	Print "After call default copyAny()"
End Sub

It’s a bit of a pity that you didn’t show either the spreadsheet with the test data or the code you came up with.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.