Hello everyone,
I made the following VBA Code in a VBA module of a software called EFT Server; a kind of software to automate tasks on an enterprise server. In that module I can automate processes with applications compatibles with this language, included LibreOffice through its UNO Bridge technology. The problem is that I made a code to open a Calc document containing a data table and I want to order that table based on the first column in ascending direction. I was looking for thoroughly in the LibreOffice documentation and I found this way that says is the recommended practice using the CoreReflection Interface for the LibreOffice structs that VBA not recognize natively, and are necessary for the sort method of LibreOffice automation. The code is working well opening the document, getting the last column and last row of the used range of the sheet, selecting the range to sort and apparently I “made it well” with the sorting code, but simply the sort method is not working at all. I attached the document I’m using for the test.
NOTE: Being VBA, this code also can be executed from the Microsoft Office VBA Modules without any problem.
Thanks in advance for your invaluable help.
Sub Main()
'VARIABLES:
Dim obL_Service_Manager As Object
Dim obL_Core_Reflection As Object
Dim obL_Desktop As Object
Dim srL_Url As String
Dim obL_Calc_Document As Object
Dim obL_Sheet As Object
Dim obL_Range_First_Column As Object
Dim obL_Range_ToSort As Object
Dim a1L_Arguments()
Dim lnL_iLast_Row As Long
Dim lnL_iLast_Column As Long
Dim csL_Sort_Field
Dim obL_Sort_Field
Dim csL_Sort_Descriptor
Dim obL_Sort_Descriptor
'PROCESS:
Set obL_Service_Manager = CreateObject("com.sun.star.ServiceManager")
Set obL_Core_Reflection = obL_Service_Manager.createInstance("com.sun.star.reflection.CoreReflection")
Set obL_Desktop = obL_Service_Manager.createInstance("com.sun.star.frame.Desktop")
srL_Url = "file:///C:/Source/Data.ods"
Set obL_Calc_Document = obL_Desktop.loadComponentFromURL(srL_Url, "_blank", 0, a1L_Arguments)
Set obL_Sheet = obL_Calc_Document.Sheets.getByIndex(0)
lnL_iLast_Column = GetLastUsedColumn(obL_Sheet)
lnL_iLast_Row = GetLastUsedRow(obL_Sheet)
Set obL_Range_ToSort = obL_Sheet.getCellRangeByPosition(0, 0, lnL_iLast_Column, lnL_iLast_Row)
csL_Sort_Field = obL_Core_Reflection.forName("com.sun.star.table.TableSortField")
csL_Sort_Field.createObject obL_Sort_Field
obL_Sort_Field.Field = 0
obL_Sort_Field.IsAscending = True
obL_Sort_Descriptor = obL_Range_ToSort.createSortDescriptor()
For n = 0 To UBound(obL_Sort_Descriptor)
If obL_Sort_Descriptor(n).Name = "ContainsHeader" Then
obL_Sort_Descriptor(n).Value = True
ElseIf obL_Sort_Descriptor(n).Name = "SortFields" Then
obL_Sort_Descriptor(n).Value = obL_Sort_Field
End If
Next n
'THIS LINE OF DOESN'T ORDER THE SELECTED RANGE ACCORDING WITH THE FIRST COLUMN.
*******************************************************
obL_Range_ToSort.Sort(obL_Sort_Descriptor)
*******************************************************
End Sub
Function GetLastUsedColumn(obL_Sheet) As Long
Dim obL_Cursor As Object
Set obL_Cursor = obL_Sheet.createCursor
obL_Cursor.GotoEndOfUsedArea (True)
GetLastUsedColumn = obL_Cursor.RangeAddress.EndColumn
End Function
Function GetLastUsedRow(obL_Sheet) As Long
Dim obL_Cursor As Object
Set obL_Cursor = obL_Sheet.createCursor
obL_Cursor.GotoEndOfUsedArea (True)
GetLastUsedRow = obL_Cursor.RangeAddress.EndRow
End Function