Ask Your Question
0

Macro to sort numbers with Writer

asked 2017-02-04 12:14:12 +0200

Mrs. Brown gravatar image

I need to write a Writer macro to sort a selection with comma-separated numbers. For example, transform this

10, 23, 2, 109, 55

into this

2, 10, 23, 55, 109

I try to do it this way:

  1. Convert text into table
  2. Select table
  3. Table sort by columns
  4. Convert table into text

But the macro I've recorded always ask me sorting parameters, and the macro breaks in point 3 with the message "I can't order the selection"

The macro code:

    sub sorting
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(4) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Delimiter"
args1(0).Value = ","
args1(1).Name = "WithHeader"
args1(1).Value = false
args1(2).Name = "RepeatHeaderLines"
args1(2).Value = 0
args1(3).Name = "WithBorder"
args1(3).Value = true
args1(4).Name = "DontSplitTable"
args1(4).Value = false

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

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

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

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

rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "Delimiter"
args5(0).Value = ","

dispatcher.executeDispatch(document, ".uno:ConvertTableToText", "", 0, args5())

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

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

rem ----------------------------------------------------------------------
dim args8(1) as new com.sun.star.beans.PropertyValue
args8(0).Name = "Count"
args8(0).Value = 1
args8(1).Name = "Select"
args8(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args8())

rem ----------------------------------------------------------------------
dim args9(1) as new com.sun.star.beans.PropertyValue
args9(0).Name = "Count"
args9(0).Value = 1
args9(1).Name = "Select"
args9(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoUp", "", 0, args9())


end sub

Any ideas?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2017-02-04 15:19:59 +0200

JohnSUN gravatar image

updated 2017-02-06 11:11:47 +0200

May be something like as

Sub SortSelected
Dim oSelection As Variant, oSel As Variant, aString As Variant 
Dim sString As String
Dim i As Long, j As Long, m As Long
    oSelection = ThisComponent.getCurrentSelection()
    For i = 0 To oSelection.getCount()-1
        oSel = oSelection.getByIndex(i)
        aString = Split(oSel.getString(),", ")
        For j = LBound(aString) To UBound(aString) - 1
            For m = j+1 To UBound(aString)
                If Val(aString(m)) < Val(aString(j)) Then
                    sString = aString(m)
                    aString(m) = aString(j)
                    aString(j) = sString
                EndIf
            Next m
        Next j
        oSel.setString(Join(aString,", "))
    Next i
End Sub
edit flag offensive delete link more
0

answered 2017-02-06 09:00:49 +0200

Mrs. Brown gravatar image

updated 2017-02-06 09:03:57 +0200

Thanks JohnSUN, that's exactly what I needed. Just an improvement:

For m = j To UBound(aString)

should better be

For m = j+1 To UBound(aString)

Again, thanks a lot

edit flag offensive delete link more

Comments

Yes, you are absolutely right - it's just an unfortunate misprint, thank you.

JohnSUN gravatar imageJohnSUN ( 2017-02-06 11:11:28 +0200 )edit
0

answered 2017-02-06 11:11:59 +0200

karolus gravatar image

updated 2017-02-06 11:15:57 +0200

Hallo
All this clumpsy Basic … , thats a no-brainer in python:

def sort_selected_Numberline():
    doc = XSCRIPTCONTEXT.getDocument()
    for line in doc.CurrentSelection:
        line.String = ", ".join(map(str, sorted(map(int, line.String.split(',')))))
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-04 12:14:12 +0200

Seen: 105 times

Last updated: Feb 06 '17