Marco in calc - cell formatting dont work?

[jjmeric]

Hello all,
using LibreOffice 7.2.5.2 on Ubuntu
I recorded my first macro. The first step works (selecting 4 columns and setting width), the final step works (selecting 4 cells and setting background and font colour), but the middle step works only partially:
selecting 4 columns and setting cell alignment and cell language.
I do not have the expected result, apparently only one cell has been updated, not the 4 columns.
What is wrong in the code generated below ?
My first attempts of course had the classical problem : selecting by mouse - but now I am at my 5th attempt…
Thanks for help and guidance
JJ

sub bamfr2
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 = “By”
args1(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = “By”
args2(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = “By”
args3(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args3())

rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = “ColumnWidth”
args4(0).Value = 10000

dispatcher.executeDispatch(document, “.uno:ColumnWidth”, “”, 0, args4())

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = “Sel”
args5(0).Value = false

dispatcher.executeDispatch(document, “.uno:GoToStartOfRow”, “”, 0, args5())

rem ----------------------------------------------------------------------
dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name = “By”
args6(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args6())

rem ----------------------------------------------------------------------
dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name = “By”
args7(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args7())

rem ----------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = “By”
args8(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args8())

rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name = “VerticalJustification”
args9(0).Value = 1

dispatcher.executeDispatch(document, “.uno:VerticalJustification”, “”, 0, args9())

rem ----------------------------------------------------------------------
dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name = “WrapText”
args10(0).Value = true

dispatcher.executeDispatch(document, “.uno:WrapText”, “”, 0, args10())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = “Sel”
args11(0).Value = false

dispatcher.executeDispatch(document, “.uno:GoToStartOfRow”, “”, 0, args11())

rem ----------------------------------------------------------------------
dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name = “By”
args12(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args12())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:Language”, “”, 0, Array())

rem ----------------------------------------------------------------------
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name = “ToPoint”
args14(0).Value = “$A$1”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args14())

rem ----------------------------------------------------------------------
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name = “By”
args15(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args15())

rem ----------------------------------------------------------------------
dim args16(0) as new com.sun.star.beans.PropertyValue
args16(0).Name = “By”
args16(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args16())

rem ----------------------------------------------------------------------
dim args17(0) as new com.sun.star.beans.PropertyValue
args17(0).Name = “By”
args17(0).Value = 1

dispatcher.executeDispatch(document, “.uno:GoRightSel”, “”, 0, args17())

rem ----------------------------------------------------------------------
dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name = “Color”
args18(0).Value = 16777215

dispatcher.executeDispatch(document, “.uno:Color”, “”, 0, args18())

rem ----------------------------------------------------------------------
dim args19(3) as new com.sun.star.beans.PropertyValue
args19(0).Name = “BackgroundPattern.Transparent”
args19(0).Value = false
args19(1).Name = “BackgroundPattern.BackColor”
args19(1).Value = 0
args19(2).Name = “BackgroundPattern.Filtername”
args19(2).Value = “”
args19(3).Name = “BackgroundPattern.Position”
args19(3).Value = com.sun.star.style.GraphicLocation.NONE

dispatcher.executeDispatch(document, “.uno:BackgroundPattern”, “”, 0, args19())

rem ----------------------------------------------------------------------
dim args20(0) as new com.sun.star.beans.PropertyValue
args20(0).Name = “ToPoint”
args20(0).Value = “$C$2”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args20())

end sub

[jjmeric]

Than you Villeroy.
I do need a macro as I am transforming lots of computer-generated csv files into odt files.
But I will try to apply styles instead of the format/cell commands.

[Villeroy]

You can assign any complex set of cell formats to any cell selection like this:
Format some cell without macro recorder.
Hit F11 for the stylist window.
Ctrl+Click to highlight the cell and drag the highlighted cell into the stylist window.
Enter a name when prompted.
Optional: Call Tools>Customize… and assign a keyboard shortcut, toolbar button or menu entry to your personal cell style.

Now you can select any range(s) of cells and apply the whole set of formatting attributes that are defined under the given style name by shortcut, button, menu entry or by hitting F11 and double-click on the name.
Extra feature: You can always right-click>Edit a style and all the cells sharing that style will be modified accordingly.
Styles can be organized in document templates so you always have the right set of styles in new documents.
It is impossible to use LibreOffice productively without using styles (Writer in particular).