Macro: How to move text unformatted

English
,
#1

I use following macro to move a range from a “input range” to the place I want it to be.

[...]
source = sheet.getCellRangeByName("A5:C6")
    target = sheet.getCellByPosition(0, lastRow)

    	sheet.moveRange(target.CellAddress, source.RangeAddress)
[...]

But this way the formatting gets also moved i.e. I wanted to decorate the range where I input the data (A5:C6) with a background color and a border which both gets moved by the makro to the new destination where I want the text only.

Is there a way to move the text only or would my only option be to change the macro to remove the formatting at the destination and redo it at the input range?

Version: 24.8.5.2 (X86_64) / LibreOffice Community
Build ID: 480(Build:2)
CPU threads: 16; OS: Linux 6.13; UI render: default; VCL: kf6 (cairo+xcb)
Locale: de-DE (en_US.UTF-8); UI: en-US
24.8.5-3
Calc: threaded
#11

What does this variable tell the method?
What are the indentations supposed to mean?
Often only complete macro code gives the relevant information
:::
:::
(Once more the discourse told me that my comment was too similar to something already deleted. Am I supposed by the software to be an idiot?)

#12

“text” is a bit misleading. Spreadsheet cells have content which may be text, but mostly is numbers (or formulas).

No, and even if you try to do it this way, it would be rather complicated. Anyway you would need to not only know a target given as a CellAddress, but the target CellRange as an object.
As soon as you have that range (as the value of a variable), however, the usage of the DataArray (mentioned already by @karolus) is the appropriate way.
Unfortunately, @karolus has given a significant part of the solution on his campaign for Python by using syntax that you won’t understand if you’re not familiar with Python yourself.
Suppose the top-left cell of your target range is placed in column 10, row 13 (0-based indices - that’s cell named K14), you can get the target range by

targetRange = sheet.getCellRangeByPosition( _
               10, 13, _
               10 + source.Columns.Count - 1, _
                   13 + source.Rows.Count - 1)

Then comes
targetRange.setDataArray(source.getDataArray())
and finally you may want to clear the source (input?) range by
source.clearContents(1 + 2 + 4 + 16)
where the numbers mean
1: ordinary number
2: date (as number)
4: text
16: formula
Formats of the source aren’t touched.

1 Like
#13

https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1CellFlags.html

#14

But see also:
116129 – Calc Cell Range method ClearContents does not work with com.sun.star.sheet.CellFlags.STYLES and
137667 – Calc, API: queryContentCells doesn't correctly evaluate the flag bits 2^5 through 2^8 .

1 Like
#2

Hallo

python

    source = sheet["A5:C6"]
    data = source.DataArray
    r = int(sheet.RowDescriptions[-1].split()[-1])
    sheet[ r:r+len(data), 0:len(data[0])].DataArray = data
#3

similarly so very well documented here : https://wiki.documentfoundation.org/Macros/Basic/Calc/Format :face_with_thermometer:

not so many details here either : Reading and Writing values to Ranges

1 Like
#4

mri still exists!

#5

does it ? maybe just walking dead …

I mean, when you already know the answer, it will help to find your way back in your memory;

but for a new comer, it’s almost just a middle-finger-pedagogical approach.
I’m confident we can do better :wink:

#7

for example by posting screenshots of AI prompts, like @fpy ?? rofl

#8

interesting you mention it here, and not where it would have been more appropriate.

I totally see that decade-long contributor pattern, just trapped in their own rants rather than contributing to more readible/accessible info. pity, but every one is free.

matter of months …

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