Macros to write specified cell values to specified cells (& clear them)?

English
#1

Macros to write specified cell values to specified cells (& clear them)

I need numerous ‘write’ and ‘clear’ buttons.

I recorded two macros.
The ‘clear’ code seems fine … enter the range, the dispatcher then: GoToCell - ClearContents

The ‘write’ code can fail because it copies and pastes.
If Write is used, and then Clear … it fails because the range is highlighted.

Hence I need something like this:

args1(0).Name = “Source”
args1(0).Value = “$sheet1.$A$3:$D$3”

args2(0).Name = “Target”
args2(0).Value = “$sheet2.$A$5:$D$5”

dispatcher.executeDispatch(… args2()=args1()

Such a code would allow me to simply change the two ranges, and rename the macro.

I guess it is simple but I can’t find any reference to it.
Can anyone help me with the code?
(I have just downloaded all of Jean-François Nifenecker’s guides, so hopefully I will learn)

Here is the recorded macro:

sub Main
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$3:$D$3"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$A$5"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

end sub
Macro to clear a range of cells
#2

Try:

    doc = ThisComponent
    sheet_source = doc.Sheets.getByName("Sheet1")
    sheet_target = doc.Sheets.getByName("Sheet2")

    source = sheet_source.getCellRangeByName("A3:D3")
    target = sheet_target.getCellRangeByName("A5:D5")

    target.DataArray = source.DataArray
    source.clearContents(127)

Be careful, with DataArray, the ranges must be the same size.

2 Likes
#3

Thanks elamau … the above code works :slight_smile:
It transforms the potential of spreadsheets; enabling them to create & delete records, and re-populate variables (to re-create a previous session).
Ha!
That will be more complex than what we are discussing here, but in principal, the development option is visible.

However, before rushing ahead, I’d like to firm up my understanding:

Questions

For single cell 'write & delete’
Would I use: getCellByName(“A3”)
target.Cell = source.Cell
?

dim doc as object
I believe that Jean-François Nifenecker (in his guide), is saying that the dimension (dim) should be declared.
I added that line to the code … I don’t know if other dim’s should ideally be declared?

doc = ThisComponent.CurrentController.Frame
In the recorded macro code .CurrentController.Frame was added to doc = ThisComponent

What is the significance of these identifiers?
I’m wondering if it ties the macro to just the current worksheet … or what?
(I haven’t tried to add them because I don’t understand them)

.clearContents(127)
What is the significance of ‘127’ ?

#5

For values: target.Value = source.Value

For text: target.String = source.String

For formula: target.Formula = source.Formula

–

I advice you not learn with macro recorder, if you really want learn macro.

–

Look:
https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XSheetOperation.html#aecfada6903426188fb0fbb654b7df4d5

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

#4

Other than the general questions above … I’ve discovered an issue with the ‘write’ macro.

It’s not critical, but … all formatting is lost, when the data is written.
Consequently the % result becomes a plain number, without visual rounding etc.

Can we get the formatting copied along with the values?
… or perhaps not overwrite any pre-formatted cells?

Edit: I tried formatting the target cells, but the ‘write’ removed that formatting.

#6

Please, one question per topic one answer per topic, allows for a more orderly forum.

#7

Apologies :slight_smile:

RE:

I replaced DataArray with Value
… producing : Property or method not found: Value
It seems that target.DataArray is recognised, but not target.Value

I’m guessing that DataArray is a property, but Value isn’t, and that it needs to be defined.
I’ve spent the last hour trying to figure this out, but failed.

#8

You say: For single cell 'write & delete’

For single cell: target.Value = source.Value

#10

I posted this in the wrong position.
I’m struggling now to get it to post :frowning:

Here is what we started with:

This code works, but clears the formatting of the destination cells.
I changed the last line of code for:
target.Value = source.Value

The macro doesn’t run because “Property or method not found: Value”.

I’ve tried numerous lines of code.
This code does not produce errors, but it achieves no result:

    sheet_source = doc.Sheets.getByName("search2")
    sheet_target = doc.Sheets.getByName("search2")

    source = sheet_source.getCellRangeByName("A3:D3")
'    target = sheet_target.getCellRangeByName("A5:D5")

    target = source.DataArray
    source.DataArray = target
#11

I came up with this code:

doc = ThisComponent

    sheet_source = doc.Sheets.getByName("search2")
    sheet_target = doc.Sheets.getByName("search2")
 
    source = sheet_source.getCellRangebyName("A3:D3")
    arr = source.getDataArray()

    target = sheet_target.getCellRangebyName("A7:D7")
    target.setDataArray(arr)

It produces the same result as elmau’s first suggestion.
When I try to use the term value, it is not recognised.

Unless someone can come up with other suggestions for transferring range values to another range, we’ll have to consider this a fail :frowning:

There may be a way to recreate the original format using a format function.

#12

Value is a single cell property, as I have already indicated. It does not exist in a range of cells, it is clear that you will always get an error if you do: range.Value.

DataArray not change the format of ranges.

#13

Thanks for that clarification elmau :slight_smile:

The question then, is how to transfer the values of a range?

Or perhaps add to the code, to transfer a cell value.
Or even scrap the range transfer, and simply transfer the 4 cell values individually?

#14

but… I don’t understand… I already answered this…

#15

But that code does not transfer the cell values alone.
It transfers everything, and overwrites the number format (and all formatting).

Even if I pre-format the destination cells … the formatting gets wiped out.
The displayed numbers are therefore very long, and not meaningful at a glance.

I was presuming that there would be a simple method of either (effectively) copy and paste the text, numbers & format.
OR
Transfer the text & numbers into pre-formatted cells

#16

Again, DataArray does not change the format…

Without seeing how you do it, it is difficult to help you.

Peek 2022-03-15 17-11

#17

It certainly does.
Give me a minute, and I’ll take a couple of screenshots.

In the meantime, I found this on Stack Overflow (in VBA):

Worksheets(1).Cells(i, 3).Copy
Worksheets(2).Cells(a, 15).PasteSpecial Paste:=xlPasteFormats
Worksheets(2).Cells(a, 15).PasteSpecial Paste:=xlPasteValues

I don’t know how it works in Libre Office.

I’ll grab the screenshot of the code that you provided, in use.
(which is great, but for this situation, not ideal)

#18

write_without_format
write_without_format735×78 5.49 KB

macro_write

Both write button macros are the same, but on subsequent rows

#19

Here is the code that works
It is recorded, but it was planned.
The final action is to reposition the selection, to enable the possibility of an immediate ‘clear written cells’.

I then proceeded to record a macro, where the ‘paste sheet’ was different, and a ‘return to a sheet’ was added.

This creates a universal ‘write macro’, which can…

  • Copy a specified range, or cell
  • Paste (numbers, text, & formatting) into the same sheet, or a different sheet
  • Remain on the ‘paste sheet’, or move to any specified sheet.

Ideally, the controlling values, would be best declared as variables.
This I will post as a new question.
Edit: Posting a new question may not be necessary, as the answer is clearly provided in:
Andrew Pitonyak’s “OpenOffice.org Macros Explained” Pg. 36

Thanks elmau for contributing to this difficult topic :slight_smile:

Sub Main
rem define variables
dim document   as object
dim dispatcher as object
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "ToPoint"
						    '______________ SOURCE RANGE ___________

							args1(0).Value = "$A$3:$D$3"
							'_______________________________________

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
dim args3(0) as new com.sun.star.beans.PropertyValue
	args3(0).Name = "ToPoint"
						    '____________ TARGET RANGE _____________

							args3(0).Value = "$A$7"
							'_______________________________________

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
dim args4(5) as new com.sun.star.beans.PropertyValue
	args4(0).Name = "Flags"
	args4(0).Value = "SVDT"
	args4(1).Name = "FormulaCommand"
	args4(1).Value = 0
	args4(2).Name = "SkipEmptyCells"
	args4(2).Value = false
	args4(3).Name = "Transpose"
	args4(3).Value = false
	args4(4).Name = "AsLink"
	args4(4).Value = false
	args4(5).Name = "MoveMode"
	args4(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())
dim args5(0) as new com.sun.star.beans.PropertyValue
	args5(0).Name = "ToPoint"
						    '_________ REPOSITION SELECTION ________
						    
							args5(0).Value = "$A$1"
							'_______________________________________
							
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
End Sub
#20

https://wiki.documentfoundation.org/Macros/Basic/Calc/Ranges#Copy

Sub copy_range()

	SOURCE_RANGE = "A2:D3"
	TARGET_CELL = "A7"
	
	doc = ThisComponent
	sheet = doc.CurrentController.ActiveSheet
	
	source = sheet.getCellRangeByName(SOURCE_RANGE)
	target = sheet.getCellRangeByName(TARGET_CELL)
	
	sheet.copyRange(target.CellAddress, source.RangeAddress)

End Sub

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