Macro not recording correctly

[stuarts.burgers]

Hi I have 17000 rows 20 columns spreadsheet base spreadsheet
I have 30 different iterations of it, it is a mailing list that is then manipulated by if statements, etc to create delivery routes.
I am attempting to speed up the process by using macros and the first one I recorded was an attempt to extract a delivery route mailing list

I started the recorder then highlighted the area to be sorted I then attempted to do the sort via Alt D then Alt S then moved to the correct column by using the down arrow then Atl O
I then used my mouse to stop recording
The sort has been done in the way I require but when I attempt to run the macro again all I get is the high lighting and no sort
I have looked at the code and the last lines do not look right
I can see as I tap across to highlight the top row and the use shift ctrl arrow down to complete the highlighting of the rows to be sorted but it is the last bit that is not right

Here is my code

***start edit: @karolus ***

sub testdata2021
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 = "$B$2"

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

rem   ## replaced '…Value = 1' with '…Value = 19'
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "By"
args2(0).Value = 19

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


rem ## and removed 18 identical Blocks like above
dim args21(0) as new com.sun.star.beans.PropertyValue
args21(0).Name = "By"
args21(0).Value = 1

dispatcher.executeDispatch(document, ".uno:GoDownToEndOfDataSel", "", 0, args21())

***rem ## removed 'rem' in front next line***
dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, Array())

end sub

edit by @karolus : corrected code …does it work this way??

Yes I can see the problem now thank you

[erAck]

The macro recorder records only simple command slot execution via the .uno:* call dispatcher, like actions you can perform with menus and shortcuts. It is not suited for more complex tasks. In particular it does not record anything you do in dialogs.

For your task you’d have to write a proper macro using the LibreOffice UNO API, though you can mix in dispatcher calls where applicable (selecting a data area this way is superfluous though, there are other means). See the Macros wiki page and Calc fragments for starters.

[stuarts.burgers]

Hi erAck
Are you saying that using Macro Recorder I can not write a macro that will do a sort even if I use the Alt Keys, I know that mouse clicks do not record
At this time all I am attempting to do is sort the sheet based upon 1 Column in an ascending fashion,

data to be sorted.ods (16.9 KB)

Here is part of the sheet I am dealing with

What I do is I highlight all the cells then I do a Sort via Alt D Alt S select Column A then select Ascending then Alt O

If I can get this to work then there are a number of other steps I want to do but Baby Steps to start with
Thanks for your help
Stuart

[erAck]

Just go to A1 and click the Sort Ascending button, or choose menu Data → Sort Ascending. That action is also recordable. Consecutive data range will be automatically selected and the sort be executed on the current cell cursor’s column (A).

[stuarts.burgers]

Hi
Thank you for your input I am getting better at this
As you can see above @karolus edited my code for me
I do not know what had caused my problem but now I understand at least one thing I need to look for
Stuart

[KamilLanda]

The Macro recorder sometimes adds the rem to some lines. But the sorting is more complicated, because it needs the parameters and there is empty Array() in the parameters from the macro recorder. And uno:DataSort will get the settings from last usage in the menu Data/Sort. So run your macro, then change manually the settings in the Data/Sort and run macro again → and the macro will sort according to changed conditions.

You can test the API macro (Basic):

Sub ApiSort
	dim oDoc as object, oRange as object
	dim cols(0 to 2) as new com.sun.star.util.SortField
	dim sorts(0 to 2) as new com.sun.star.beans.PropertyValue
	oDoc=ThisComponent
	
	oRange=oDoc.sheets(0).getCellRangeByName("A1:C10") 'the cells with data

	cols(0).Field=0 'column A
	cols(0).SortAscending=true 'ascend
	cols(1).Field=1 'B
	cols(1).SortAscending=true
	cols(2).Field=2 'C
	cols(2).SortAscending=false 'descend

	sorts(0).Name="SortFields"
	sorts(0).Value=cols()
	sorts(1).Name="ContainsHeader"
	sorts(1).Value=false 'no header
	sorts(2).Name="MaxFieldCount"
	sorts(2).Value=1

	oRange.sort(sorts()) 'sort
End Sub

example-API-sort.ods (14.9 kB)

[sokol92]

The SortField structure is deprecated. Use instead TableSortField.