LibreOffice Calc Sort in other BASIC versions

[donnyh13]

Good day all,
I am here looking for some investigative assistance. In making a function for automating Libre Office Calc’s sorting function, there seems to be an incompatibility we have come across between AutoIt and Libre Office. We have found a working method to bypass this, but I am curious if this is an incompatibility between AutoIt and LibreOffice alone, or if it is an error with LibreOffice when being automated using a BASIC language other than its own built in Macro language, and if so, then it could be reported and fixed. I am not familiar with any other versions of BASIC other than AutoIt, thus I am coming here.
    I’m not sure if this is the right question or method to test it, but what I thought is, if any one happens to be willing, and is familiar with another version of BASIC scripting language other than AutoIt, maybe they could modify and try the below script in that version of BASIC, and see if it works correctly?
    The following code works, in terms of not producing an error, for calling the sort function. I know this code is written correctly because the same code works just fine as a LibreOffice Macro.
    This code will insert 5 numbers into a range in a new Calc document, 5, 4, 3, 1, 2, and then call a descending sort command for that range, copying the output to a separate range. (This is just to show the command is being received and processed by L.O. Calc), what would be expected would be an output of 5, 4, 3, 2, 1. But what will be output, is simply the same order of numbers, 5, 4, 3, 1, 2.
    I have attempted to make it as basic and easy to convert as possible.

    The AutoIt Code: (Doesn’t Work)

expandcollapse popup

Global $oCOM_ErrorHandler = ObjEvent("AutoIt.Error", __COM_ERROR)

Global $oServiceManager = ObjCreate("com.sun.star.ServiceManager")
If Not IsObj($oServiceManager) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

Global $oDesktop = $oServiceManager.createInstance("com.sun.star.frame.Desktop")
If Not IsObj($oDesktop) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

Global Const $iURLFrameCreate = 8     ;frame will be created if not found
Global $aArgs[0]

; Create a new Calc document.
Global $oDoc = $oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", $iURLFrameCreate, $aArgs)
If Not IsObj($oDoc) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

; Retrieve Active Sheet
Global $oSheet = $oDoc.CurrentController.getActiveSheet()
If Not IsObj($oSheet) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

; Retrieve Cell Range A1 to A5
Global $oRange = $oSheet.getCellRangeByName("A1:A5")
If Not IsObj($oRange) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

; Fill Arrays with numbers
Global $aaiArray[5]
Global $aiFill[1]

$aiFill[0] = 5
$aaiArray[0] = $aiFill
$aiFill[0] = 4
$aaiArray[1] = $aiFill
$aiFill[0] = 3
$aaiArray[2] = $aiFill
$aiFill[0] = 1
$aaiArray[3] = $aiFill
$aiFill[0] = 2
$aaiArray[4] = $aiFill

; Fill the Range with numbers.
$oRange.setData($aaiArray)

Global Const _
        $LOC_SORT_DATA_TYPE_AUTO = 0, _ ; Automatically determine Sort Data type.
        $LOC_SORT_DATA_TYPE_NUMERIC = 1, _ ; Sort Data type is Numerical.
        $LOC_SORT_DATA_TYPE_ALPHANUMERIC = 2 ; Sort Data type is Text.

; Create a Sort Descriptor,
Global $tSortField = $oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
If Not IsObj($tSortField) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

With $tSortField
    .Field = 0 ; 0 = first column in the range A1-A5.
    .FieldType = $LOC_SORT_DATA_TYPE_NUMERIC ; Numerical values being sorted
    .IsAscending = False ; Descending order
    .IsCaseSensitive = False
EndWith

Global $atSortField[1] = [$tSortField]

$avSortDesc = $oRange.createSortDescriptor()

; Create a Cell Address to indicate where to copy output to. Cell C3
Global $tCellAddr = $oServiceManager.Bridge_GetStruct("com.sun.star.table.CellAddress")
If Not IsObj($tCellAddr) Then Exit ConsoleWrite("! Error: " & @ScriptLineNumber & @CRLF)

$tCellAddr.Sheet = 0    ; 0 = first sheet.
$tCellAddr.Column = 2    ; 2 = Column C
$tCellAddr.Row = 2    ; 2 = Row 3

; Apply Sort settings
For $i = 0 To UBound($avSortDesc) - 1

    Switch $avSortDesc[$i].Name()

        Case "IsSortColumns"
            $avSortDesc[$i].Value = False ; False = Sort rows top to bottom.

        Case "ContainsHeader"
            $avSortDesc[$i].Value = False ; False = Range has no headers to ignore.

        Case "SortFields"
            $avSortDesc[$i].Value = $atSortField

        Case "BindFormatsToContent"
            $avSortDesc[$i].Value = False ; False = Dont bind any formatting to the data when sorted.

        Case "CopyOutputData"
            $avSortDesc[$i].Value = True ; True = Copy the sort results instead of modifying the cell range itself.

        Case "OutputPosition"
            $avSortDesc[$i].Value = $tCellAddr

    EndSwitch

Next

; Perform the sort
$oRange.Sort($avSortDesc)


Func __COM_ERROR(ByRef $oComError)
    ConsoleWrite("!--COM Error-Begin--" & @CRLF & _
            "Number: 0x" & Hex($oComError.number, 8) & @CRLF & _
            "WinDescription: " & $oComError.windescription & @CRLF & _
            "Source: " & $oComError.source & @CRLF & _
            "Error Description: " & $oComError.description & @CRLF & _
            "HelpFile: " & $oComError.helpfile & @CRLF & _
            "HelpContext: " & $oComError.helpcontext & @CRLF & _
            "LastDLLError: " & $oComError.lastdllerror & @CRLF & _
            "At line: " & $oComError.scriptline & @CRLF & _
            "!--COM-Error-End--" & @CRLF)
EndFunc   ;==>__COM_ERROR

    LibreOffice Macro: (Works)

expandcollapse popup

REM  *****  BASIC  *****

Sub Main

' Retrieve Active Sheet
Dim oSheet 
oSheet = ThisComponent.CurrentController.getActiveSheet()

' Retrieve Cell Range A1 to A5
Dim oRange
oRange = oSheet.getCellRangeByName("A1:A5")

' Fill the Range with numbers.
oRange.setData(Array(Array(5), Array(4), Array(3), Array(1), Array(2)))

  Dim atSortFields(0) as new com.sun.star.table.TableSortField

  atSortFields(0).Field = 0 ' 0 = first column in the range.
     atSortFields(0).FieldType =com.sun.star.util.SortFieldType.NUMERIC '  = Numerical values being sorted
     atSortFields(0).IsAscending = False ' Descending order
     atSortFields(0).IsCaseSensitive = False

DIm  avSortDesc
avSortDesc = oRange.createSortDescriptor()

' Create a Cell Address to indicate where to copy output to. Cell C3
Dim tCellAddr  As New com.sun.star.table.CellAddress

tCellAddr.Sheet = 0    ' 0 = first sheet.
tCellAddr.Column = 2    ' 2 = Column C
tCellAddr.Row = 2   ' 2 = Row 3

' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)

    Select Case avSortDesc(i).Name()

        Case "IsSortColumns"
            avSortDesc(i).Value = False ' False = Sort rows top to bottom.

        Case "ContainsHeader"
            avSortDesc(i).Value = False ' False = Range has no headers to ignore.

        Case "SortFields"
            avSortDesc(i).Value = atSortFields

        Case "BindFormatsToContent"
            avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.

        Case "CopyOutputData"
            avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.

        Case "OutputPosition"
            avSortDesc(i).Value = tCellAddr

    End Select

Next

' Perform the sort
oRange.Sort(avSortDesc)
End Sub

Thank you for any assistance.

[donnyh13]

Think I figured it out.

VBA

expandcollapse popup

Sub SortLibre()

 ' Create the Service Manager
  Set oServiceManager = CreateObject("com.sun.star.ServiceManager")

  ' Create the desktop.
  Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")

  ' Open a new empty Calc document.
  Dim args()
  Dim s As String
  s = "private:factory/scalc"
  Set oDoc = oDesktop.loadComponentFromURL(s, "_blank", 0, args())

  Dim oSheet As Object
' Retrieve Active Sheet
Set oSheet = oDoc.CurrentController.getActiveSheet()

' Retrieve Cell Range A1 to A5
Dim oRange As Object
Set oRange = oSheet.getCellRangeByName("A1:A5")

' Fill the Range with numbers.
oRange.setData (Array(Array(5), Array(4), Array(3), Array(1), Array(2)))

  ' Dim atSortFields(0) As New com.sun.star.Table.TableSortField
Dim atSortFields As Variant
Dim tSortField As Object

Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")

With tSortField
.Field = 0
.FieldType = 1 ' Numeric
.IsAscending = False ' Descending Sort order
.IsCaseSensitive = False
End With

atSortFields = Array(tSortField)

Dim avSortDesc As Variant
avSortDesc = oRange.createSortDescriptor()

' Create a Cell Address to indicate where to copy output to. Cell C3
Dim tCellAddr As Object
Set tCellAddr = oSheet.getCellRangeByName("C3").CellAddress()

tCellAddr.Sheet = 0    ' 0 = first sheet.
tCellAddr.Column = 2    ' 2 = Column C
tCellAddr.Row = 2   ' 2 = Row 3

' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)

    Select Case avSortDesc(i).Name()

        Case "IsSortColumns"
            avSortDesc(i).Value = False ' False = Sort rows top to bottom.

        Case "ContainsHeader"
            avSortDesc(i).Value = False ' False = Range has no headers to ignore.

        Case "SortFields"
            avSortDesc(i).Value = atSortFields

        Case "BindFormatsToContent"
            avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.

        Case "CopyOutputData"
            avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.

        Case "OutputPosition"
            avSortDesc(i).Value = tCellAddr ' Copy to Cell C3

    End Select

Next

' Perform the sort
oRange.Sort (avSortDesc)

End Sub

VBS

expandcollapse popup

' Create the Service Manager
  Set oServiceManager = WScript.CreateObject("com.sun.star.ServiceManager")

  ' Create the desktop.
  Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")

  ' Open a new empty Calc document.
  Dim args()
  Set oDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)

' Retrieve Active Sheet
Set oSheet = oDoc.CurrentController.getActiveSheet()

' Retrieve Cell Range A1 to A5
Set oRange = oSheet.getCellRangeByName("A1:A5")

' Fill the Range with numbers.
oRange.setData (Array(Array(5), Array(4), Array(3), Array(1), Array(2)))

Set tSortField = oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")

With tSortField
.Field = 0
.FieldType = 1 ' Numeric
.IsAscending = False ' Descending Sort order
.IsCaseSensitive = False
End With

atSortFields = Array(tSortField)

avSortDesc = oRange.createSortDescriptor()

' Create a Cell Address to indicate where to copy output to. Cell C3
Set tCellAddr = oSheet.getCellRangeByName("C3").CellAddress

tCellAddr.Sheet = 0    ' 0 = first sheet.
tCellAddr.Column = 2    ' 2 = Column C
tCellAddr.Row = 2   ' 2 = Row 3

' Apply Sort settings
For i = LBound(avSortDesc) To UBound(avSortDesc)

    Select Case avSortDesc(i).Name

        Case "IsSortColumns"
            avSortDesc(i).Value = False ' False = Sort rows top to bottom.

        Case "ContainsHeader"
            avSortDesc(i).Value = False ' False = Range has no headers to ignore.

        Case "SortFields"
            avSortDesc(i).Value = atSortFields

        Case "BindFormatsToContent"
            avSortDesc(i).Value = False ' False = Dont bind any formatting to the data when sorted.

        Case "CopyOutputData"
            avSortDesc(i).Value = True ' True = Copy the sort results instead of modifying the cell range itself.

        Case "OutputPosition"
            avSortDesc(i).Value = tCellAddr ' Copy to Cell C3

    End Select

Next

' Perform the sort
oRange.Sort (avSortDesc)