Help dubugging sort in a macro

English
#1

A vendor I work with regularly supplies me with .CSV files with problematic formatting. I am writing a Macro to deal with them and format them and sort each time I receive one.

The Macro below asks for the file location, then imports the CSV, automatically formatting column C as Dates. It asks the User for a Start and End date to define a range (this is to be used in the near future but not yet), and then is supposed to sort the sheet, rows A-H, by date (column C) in descending order.

Only problem is, the macro does not cause the sheet to sort.

Can anyone tell me why the operation fails?
000Reports.ods (15.2 KB)

Sample Sheet attached, for testing purposes and to show the formatting of the CSV supplied by my vendor. It does not contain the macro code listed below. NOTE: Even though this file has an ODS extension, it is in fact a CSV file because otherwise this website will not allow it to be uploaded.

Sorry for the long macro code; I know it’s often important to see the entire thing in order to debug a single line.


Global REPORT1 As String

Sub OpenCSVFile
    Dim FileDialog As Object
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oCellRange As Object
    Dim oSortDesc(0) As New com.sun.star.beans.PropertyValue
    Dim oSortDescOrder As New com.sun.star.beans.PropertyValue

    ' Create a file dialog
    FileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")

    ' Set the dialog title
    FileDialog.setTitle("Select your ActiveReports.csv file")

    ' Set the dialog to open .csv files
    FileDialog.appendFilter("CSV Files", "*.csv")

    ' Display the dialog and get the selected file path
    If FileDialog.execute() Then
        REPORT1 = FileDialog.Files(0)
    Else
        MsgBox "No file selected.", 64, "Error"
        Exit Sub
    End If

    ' Define properties for opening the CSV file
    Dim FileProps(2) As New com.sun.star.beans.PropertyValue
    FileProps(0).Name = "FilterName"
    FileProps(0).Value = "Text - txt - csv (StarCalc)"
    FileProps(1).Name = "FilterOptions"
    FileProps(1).Value = "44,34,76,6,1/1/2/1/3/3,,true,true,true" ' The third field (Column C) is defined as a date field with the format MM-DD-YY
    FileProps(2).Name = "Hidden"
    FileProps(2).Value = False

    ' Open the selected file
    oDoc = StarDesktop.loadComponentFromURL(ConvertToURL(REPORT1), "_blank", 0, FileProps())
    oSheet = oDoc.Sheets(0)

    ' Ask the user for a START DATE
    Dim StartDate As String
    StartDate = InputBox("Enter the START DATE in the format MM/DD/YY:", "Input START DATE")

    ' Validate the START DATE
    If Not IsDate(StartDate) Then
        MsgBox "Invalid START DATE. Please enter a valid date in the format MM/DD/YY.", 48, "Error"
        Exit Sub
    End If

    ' Ask the user for an END DATE
    Dim EndDate As String
    EndDate = InputBox("Enter the END DATE in the format MM/DD/YY:", "Input END DATE")

    ' Validate the END DATE
    If Not IsDate(EndDate) Then
        MsgBox "Invalid END DATE. Please enter a valid date in the format MM/DD/YY.", 48, "Error"
        Exit Sub
    End If

    ' Convert the dates to Date data type
    Dim StartDateAsDate As Date
    Dim EndDateAsDate As Date
    StartDateAsDate = CDate(StartDate)
    EndDateAsDate = CDate(EndDate)

    ' Check if the END DATE is later than the START DATE
    If EndDateAsDate < StartDateAsDate Then
        MsgBox "END DATE must be later than START DATE.", 48, "Error"
        Exit Sub
    End If

    ' Define the range to sort (excluding headers)
 oCellRange = oSheet.getCellRangeByName("A2:H54")

    ' Define the sort field (column C, descending)
    oSortDesc(0).Name = "SortKey"
    oSortDesc(0).Value = 2

    ' Define the sort order
    oSortDescOrder.Name = "IsSortAscending"
    oSortDescOrder.Value = False

    ' Sort the data
    oCellRange.sort(Array(oSortDesc(0), oSortDescOrder))
End Sub
#2

The work of the localized date strings depends on the locale setting of the office suite, the document, the cell.

It is better to use the international standard (ISO) date format for the input of a date:
“YYYY-MM-DD”, because that will work with all of the locale settings.
.
.
Same for the CDATE function:
(CDate Function)

Or you can try the DateValue function:
(DateValue Function)

1 Like
#3

ISO-code can be insert as TEXT or as NUMERAL to handle correct by CALC:

  • YYYY-MM(M)-DD : /2023-12/09/ , /2023-Dec-09/ , or
  • YYYY-MM(M)-DD“T“hh:mm:ss (with time).
    When in TEXT try to convert with „=DATEVALUE()“.
#4

From the attached sample file:

Company A,12-08-23,
.

There is not such ISO date format. I suppose it that means: 2023 december 8. (And - IMHO - there is not such tradicional localized date format in the world:
MM-DD-YY with hyphen separator character. Only the YYYY-MM-DD, YY-MM-DD, YYYYMMDD, YYMMDD are acceptable as a standardized format.)
The localized date formats usually use the “/” or the “.” separator character.

1 Like
#5

To enter a date in MM/DD/YY format we will have to work a little.
Should work in all localizations.

' Entering and checking date in MM/DD/YY format.
Sub Test
  Dim s As String, arr, d
  
  Do While True 
    d=empty
    s=InputBox("Enter the START DATE in the format MM/DD/YY", "Input START DATE")
    If s="" Then Exit Do
    arr=Split(s, "/")
    If Ubound(arr)=2 Then
      On Error Resume Next
      d=DateSerial(2000 + Cdbl(arr(2)), arr(0), arr(1)) 
      On Error GoTo 0
      If Not IsEmpty(d) Then Exit Do
    End If
  Loop  
  
  If IsEmpty(d) Then
    Msgbox "Date not entered"
  Else
    Msgbox "Entered date: " & d  
  End If
End Sub
#6

@Zizi64 @koyotak @sokol92
Thank you all for your replies, but the problem I’m having is that the sort function,

' Sort the data
    oCellRange.sort(Array(oSortDesc(0), oSortDescOrder))

does not sort the sheet. I don’t mean it sorts poorly or incorrectly - I mean the sheet does not change at all. The line of code has no effect. Is the array or cell range wrong?

Can you help me understand why the sort function does nothing?

Incidentally, if you create this macro using my code in another sheet you’ll see it will import open the CSV file I provided with the above parameters and it correctly converts and displays column C as dates. You can sort the sheet manually perfectly well, since column C has been converted to MM/DD/YY dates.
Thank you again in advance!

#7

Can you upload a sample .ods file (with the state after importing the data from the .csv file)?

#8

There are many “Exit sub” statement in your code. Maybe it is happened?
How the StartDate and EndDate related to the task??? You have create and examined them, but they are not used for the sorting.
.
.

Here is a sample file from the AOO Forum:
Sorting.ods (11.7 KB)
(Apache OpenOffice Community Forum - [Solved] Sorting via macro in Calc - (View topic))

#9

Thank you @Zizi64, I appreciate your interest.

  • The multiple ‘Exit Sub’ statements are only in the event that an error in user input occurs. When user input is correct, no error is thrown (as evidenced by the absence of an error msgbox), so the macro does not exit.
  • As I mentioned originally, the macro asks the User for a Start and End date to define a range, but this is to be used in the near future. It is not relevant to the sort. I only included it here so that no detail was left out that might have affected the outcome.
  • Thanks for the example sorting file. I’ll try using its method in my code.
#10

Since the issue is still not closed, I will point out the source of the problem. The variables describing the sort parameters were assigned the wrong type. To describe sorting fields, you should use an array of structures of a special type - SortField
In other words, the correct code should be written like this:

Dim aSortFields(0) As New com.sun.star.util.SortField
Dim aSortDesc(1) As New com.sun.star.beans.PropertyValue
...
	aSortFields(0).Field = 2
	aSortFields(0).SortAscending = FALSE
	aSortDesc(0).Name = "SortFields"
	aSortDesc(0).Value = aSortFields()
	aSortDesc(1).Name = "ContainsHeader"
	aSortDesc(1).Value = True
	oCellRange.Sort(aSortDesc())
2 Likes
#11

Or a more recent TableSortField.

#12

To fix broken CSV I’d rather pre-process data before import with a proper text processing language like awk (maybe with a wrapper asking for start/end dates or such beforehand if needed) instead of fiddling with BASIC in a post-process of imported bad data.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.