Search the Community

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

So, I was board What have I done? Well using a GUI and label controls I have made a bit of a rubbish time waster drawer script that lets you draw randomly coloured dots inside the black area by using your left mouse button. You can erase with the right mouse button. If you want to completely clear it you can press your middle mouse button (scroller wheel) Enjoy lol.... AutoItSetOption('MouseCoordMode', 2) Global $hBox[62][62], $hGUI, $iTest $iTest = 0 ; Set this to 1 to force random and automatic dot placement. #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <Misc.au3> $hDll = DllOpen('user32.dll') CreateTheGUI() While 1 $x = Floor(MouseGetPos(0) / 5) $y = Floor(MouseGetPos(1) / 5) If $iTest = 1 Then $x = Floor(Random(1, 300, 1) / 5) $y = Floor(Random(1, 300, 1) / 5) EndIf If $x >= 0 And $y >= 0 Then If $x <= 60 And $y <= 60 Then If _IsPressed('01', $hDll) Or $iTest = 1 Then If GUICtrlGetHandle($hBox[$x][$y]) = 0 Then $hBox[$x][$y] = GUICtrlCreateLabel("", $x * 5, $y * 5, 5, 5) GUICtrlSetBkColor($hBox[$x][$y], '0x' & Random(1, 16581375, 1)) EndIf If _IsPressed('02', $hDll) Then If GUICtrlGetHandle($hBox[$x][$y]) Then $hBox[$x][$y] = GUICtrlDelete($hBox[$x][$y]) EndIf If _IsPressed('04', $hDll) Then While _IsPressed('04', $hDll) Sleep(50) WEnd GUIDelete($hGUI) Global $hBox = 0 Sleep(50) Global $hBox[62][62] CreateTheGUI() Sleep(50) EndIf EndIf EndIf If GUIGetMsg() = $GUI_EVENT_CLOSE Then _Exit() Sleep(1) WEnd Func CreateTheGUI() $hGUI = GUICreate("", 300, 300, -1, -1) GUISetBkColor('0x000000') GUISetState(@SW_SHOW) EndFunc ;==>CreateTheGUI Func _Exit() DllClose($hDll) Exit EndFunc ;==>_Exit