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

asked 2020-09-07 19:28:20 +0100

kennyK4silva gravatar image

updated 2020-09-07 21:19:19 +0100

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

C:\fakepath\Data.ods

edit retag flag offensive close merge delete

Comments

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

newbie-02 gravatar imagenewbie-02 ( 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,

kennyK4silva gravatar imagekennyK4silva ( 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 ...

newbie-02 gravatar imagenewbie-02 ( 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.

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