https://bugs.documentfoundation.org/show_bug.cgi?id=160605
Bug ID: 160605
Summary: Calc Sort via AU3, VBA/VBS external script doesn't
sort.
Product: LibreOffice
Version: 7.3.4.2 release
Hardware: All
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: sdk
Assignee: [email protected]
Reporter: [email protected]
Description:
I’m not fluent in appropriate scripting terms and what not, so please forgive
the poor usages.
I am in the process of creating a User-Defined Function (UDF) for users to
automate LibreOffice using AutoIt (AutoIt is a BASIC-like scripting language),
while working on making functions to work with Calc, I have run into a problem
while trying to use the Sort function. I have successfully worked with Calc
using AutoIt, for other functions.
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 still processes, but
the values aren’t sorted that way either.
-No COM Errors result from calling the script, in either case.
To rule out an incompatibility with AutoIt, 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 at the moment). The result is the same, the sort is
processed, but the output is not sorted. As a side note, the VBA code I am
running from a Microsoft Word Macro engine, the code, with a minor change runs
fine from LibrOffice itself. (see post here be JeJe
https://forum.openoffice.org/en/forum/viewtopic.php?p=545947&sid=d20cea321b08fdfe50cf39c80ad6e4d7#p545947)
I have also converted the code to work in LibreOffice Basic Macro. 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?
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 Calc document, such as
the following:
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 Calc 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 to the above
Macro, the sort fails the same as when I tried to sort directly from AutoIt.
If you need me to clarify, or try to re-word anything, let me know, and I will
try my best. As stated above I have attempted to reproduce the script in other
BASIC language versions, in the hopes of demonstrating that it is not a AutoIt
specific limitation. My sincere apologies if this is something that is known,
and is not a LibreOffice bug.
I will include the scripts in the Other Information section.
Steps to Reproduce:
1. Run one of the below scripts external to LibreOffice
2.
3.
Actual Results:
Sort is processed but fails to sort the data.
Expected Results:
Sort should be processed, and data should be sorted.
Reproducible: Always
User Profile Reset: Yes
Additional Info:
Version: 7.3.4.2 (x64) / LibreOffice Community
Build ID: 728fec16bd5f605073805c3c9e7c4212a0120dc5
CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render:
Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL
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, must be run using AutoIt scripting language editor etc, found
here: https://www.autoitscript.com/site/autoit/downloads/
I could also compile it as an exe.
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
VBA script, produces the error when ran externally from LibreOffice,
specifically from Microsoft Office Macro editor. This script works when run
from LibreOffice itself, with the minor modification of changing the line
Set tSortField =
oServiceManager.Bridge_GetStruct("com.sun.star.table.TableSortField")
to
Set tSortField = new "com.sun.star.table.TableSortField"
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 script. Produces the error when ran as a script on Windows computer.
' 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 I created as a test. This runs and produces correct
results.
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
--
You are receiving this mail because:
You are the assignee for the bug.
