[Solved] LibreOffice Calc Sort via AU3, VBA/VBS

[DonnyH]

Good day everyone,
I’m not sure exactly how to introduce this post, and I’m not fluent in appropriate scripting terms and what not, so please forgive the poor usage.
I am in the process of creating a User-Defined Function (UDF) for users to automate LibreOffice using AutoIt. The process has worked great so far, and I am currently working on making functions to work with Calc, but I have run into a problem while trying to use the Sort function. I have followed Andrew Pitonyak’s book, and double checked all of my code to ensure I haven’t missed anything. It seems to be all correct.
The problem I am encountering is as follows: I have a Range, say A1:A5, with the values 5, 4, 3, 1, 2. I run my AutoIt code that is supposed to sort the range (A1:A5), and have the sort copy the results to a separate cell, cell C3, so that I know the sort function is actually being called. What occurs is the values are output to cell C3 and down, but remain in the order they were found, in this case, 5, 4, 3, 1, 2. In case it matters, if I don’t copy the results, the sort occurs, but the values aren’t sorted that way either. No COM Errors result from calling the script, in either case.
To begin with I thought it was perhaps a problem with an incompatibility with AutoIt, but I wanted to make sure, so I attempted to convert the code to work in VBA/VBS (the only other BASIC language I can figure out how to use to automate LibreOffice). The result is the same, the sort is processed, but the output is not sorted.
I have also converted the code to work in LibreOffice Basic. That, of course, works fine. I am thinking that this may be a bug in LibreOffice itself, when an external language (or BASIC language at least) is used to call a sort function? But before I report it, I wanted to see if anyone here has any input on why this wouldn’t be working correctly. Perhaps I have missed some obvious limitation?

Sort results.png (49.92 KiB) Viewed 1802 times

As a side note, if I can explain this correctly, I think the main issue at hand is the SortField Struct or Sortfield Array is not being recognized in the array of sort descriptor settings. The reason for this conclusion is as follows: I can write a simple macro and store it in the LibreOffice document, such as the following:

Code: Select all

			REM Macro for Performing a Sort Function. 
			Sub AU3LibreOffice_Sort(oRange, avSortDesc, atField)
			
			For i = LBound(avSortDesc) To UBound(avSortDesc) 
			If (avSortDesc(i).Name() = ""SortFields"") Then avSortDesc(i).Value = atField
			
			Next 
			
			oRange.Sort(avSortDesc())
		End Sub

After creating the above macro in the document, I can go to AutoIt, and using the SortField Struct array, the Sort Descriptor Array, and the range Object that I tried to call the sort command with directly from AutoIt (which failed, as noted above), I can instead pass those as parameters to the Macro in the document, and then have the macro insert the SortField array and call the sort, and the sort works just fine. If I pass only the Range and the SortDescriptor array with the SortField Array inserted already, the sort fails the same as when I tried to sort directly from AutoIt.

I would appreciate any input on this issue, on whether you think this is worth reporting? Or if I have missed something obvious? If you need me to clarify, or try to re-word anything, let me know. Thank you very much for your help.

The scripts I have written are below. In all of them I have made it automatically fill in the values 5, 4, 3, 1, 2 in cells A1:A5 to ease the demonstration.
Code for AutoIt,

Code: Select all

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

And VBA

Code: Select all

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

And VBS

Code: Select all

 ' 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)

And the LibreOffice Macro.

Code: Select all

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

[JeJe]

Have you tried the macro recorder?

[DonnyH]

Thank you for your reply JeJe,
Not exactly, but I have found a way to use the uno:DataSort Execute command through AutoIt, which works successfully. I'm assuming this would be the command used by the Macro Recorder. But I was hoping to be able to make the appropriate command work, so that any user selections aren't lost. Plus DataSort doesn't offer the ability to copy the output, though that's not life and death.

[JeJe]

You're perhaps not getting other repliers because people here aren't using Autoit and can't explore your problem.

As an alternative, if you can use get / setDataArray then presumably Autoit can call its own sort array function?

Code: Select all

r =thiscomponent.sheets(0).getcellrangebyname("A1:A5")
dataarray = r.getdataarray 'get the data
'call your own sort algorithm on dataarray in AutoIt?
r.setdataarray(dataarray) 'put it back sorted

[JeJe]

Your VB5 code runs for me within LO with an error on the line

Code: Select all

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

Looking at MRI the servicemanager doesn't have that property or method


runs within LO by changing to

Code: Select all

Set tSortField = new "com.sun.star.table.TableSortField"

Code: Select all

sub tmp

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

  ' 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")

Set tSortField = new "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)

end sub

[DonnyH]

Thank you very much for the replies JeJe, yes, you are probably correct in regard to none having AutoIt here. I had hoped with including the VBA/VBS scripts it would help. I guess I was looking for any insight on whether this was a known limitation or not.

In regards to the "Bridge_GetStruct", it does work when ran from external programs. The Dev Guide says:

"The com.sun.star.reflection.CoreReflection service or the Bridge_GetStruct function that is called on the service manager object can be used to create the struct. ...... The Bridge_GetStruct function is provided by the service manager object that is initially created by CreateObject (Visual Basic) or CoCreateInstance[Ex] (VC++).c" OOo Dev Guide Page 240.

But thank you for the modification.

You bring up an interesting idea I had not thought of. Yes, I could get, sort, and set the data using AutoIt, that should work quite well, as long as I can grasp the order that LibreOffice uses. Thank you very much for that idea.