# Sorting Columns in Calc with VBA Code (Not Basic Macros)

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.

    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)
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)
End Function

Function GetLastUsedRow(obL_Sheet) As Long
Dim obL_Cursor As Object
Set obL_Cursor = obL_Sheet.createCursor
obL_Cursor.GotoEndOfUsedArea (True)
End Function


C:\fakepath\Data.ods

edit retag close merge delete

just guessing ...
may be you need a sequence like that? (was for filter)
oFilterDesc.setFilterFields(aFieldsNeu())
oFilterDesc.ContainsHeader=true
oRange.referredcells.filter(oFilterDesc)
... macros in calc are everything but easy ...

( 2020-09-07 20:37:48 +0100 )edit

Hello,

Thanks for your answer, but my specific requirements is to sort the first column (column A) in ascending direction using VBA, not make a filter of rows of data. I know that the macro language Basic has many similarities with VBA, and maybe are valid your lines of codes with VBA for filtering, but is not what I'm looking for specifically.

Regards,

( 2020-09-07 21:11:50 +0100 )edit

pfuhhhh ...
as this sample - tested last one in thread -
worked ... even with the recursion stripped to one column ... and even with your range_finding_algo added, but fails once you strip the index from aSortFields ... may be that

obL_Sort_Field**(0)**.Field = 0
obL_Sort_Field**(0)**.IsAscending = True


(without the 'bold stars') will make it for you,
if not start with sample above and compare / 'debug' in Basic Editor ... happy hacking ...

( 2020-09-08 02:15:16 +0100 )edit

Great! I made three tests right now with different files and it worked perfectly, but not with the responses to the question asked in the thread you shared. It was with the executeDispatchmethod in the code of the question itself. At the same time, I suspect that the way I used the obL_Sort_Field - mentioned by you - must be used following the recommendation of this page using the MakePropertyValue method http://www.kalitech.fr/clients/doc/VB... for each ítem of the properties array. I will make some arrangements to the code and when I finish them I will answer this thread with the modified code.

Thank you very much.

( 2020-09-08 13:28:05 +0100 )edit