Hi, all.
I have small database (4 sheets, each with around 200 rows and 6 columns) that I edit frequently (at least once a week). I can sort it via the Sort button, but would like it to sort automatically when I add or delete entries.
I’ve defined my ranges and picked up a seemingly simple macro from a LO blog. The macro simply detects changes in the range and, when it does, it runs the sorting macro.
The macro is as follows:
Sub SortRangeFilter( oEvent As Variant )
Dim sRange As String
sRange = "Brancos"
Rem ^^^^^^
Rem Hardcoded range name
Rem Get the range object in order to access its boundaries
Dim oSheet As Variant
oSheet = ThisComponent.CurrentController.getActiveSheet()
Rem Define an error handler in case the range name does not exist
On Error Goto ErrorHandler
Dim oCellRange As Variant
oCellRange = oSheet.getCellRangeByName( sRange )
Rem Check the type of 'oEvent'
Rem Both support the 'queryIntersection' method
If ( oEvent.supportsService( "com.sun.star.sheet.SheetCellRanges" ) Or _
oEvent.supportsService( "com.sun.star.sheet.SheetCellRange" )) Then
If oEvent.queryIntersection( oCellRange.RangeAddress ).count > 0 Then
SortRange( oCellRange )
Endif
Endif
Exit Sub
ErrorHandler:
Msgbox "SortRangeFilter: Range does not exist '" + sRange + "'"
Exit Sub
End Sub
Now for the problem: I can’t get it to run. Whenever it tries to run it returns an error message: SortRangeFilter: Range does not exist ‘Brancos’
Now, ‘Brancos’ is one of my ranges. I’ve double checked names, I’ve deleted the ranges and remade them…nothing works.
The blogpost is pretty old, so some of the syntax might be out of date. If anyone could help me out, I’d be much appreciated. It’s not something that makes or breaks this database - like I said, with a couple of clicks I can sort things via the Sort button - but I’d love for it to be automated.
Many thanks!
EDIT: after an embarrassing reply, I realised I forgot to use the first part of the macro, which is as follows:
Sub SortRange( oRange As Variant )
rem Sorts range 'sRange' by column 2 in descending order
rem (assumes that columns have labels in first row)
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
rem Select the range
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = oRange.AbsoluteName
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(7) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = true
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "NaturalSort"
args2(3).Value = false
args2(4).Name = "IncludeAttribs"
args2(4).Value = true
args2(5).Name = "UserDefIndex"
args2(5).Value = 0
Rem Sort by the second column of the range !!!
args2(6).Name = "Col1"
args2(6).Value = oRange.RangeAddress.StartColumn + 2
args2(7).Name = "Ascending1"
args2(7).Value = false
dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())
End Sub
Now I don’t get the error message I was getting. However, I couldn’t make it sort the way I wanted. So I used macro recorder and it gave me the following code:
sub Sort
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "DbName"
args1(0).Value = "Brancos"
dispatcher.executeDispatch(document, ".uno:SelectDB", "", 0, args1())
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(11) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ByRows"
args3(0).Value = true
args3(1).Name = "HasHeader"
args3(1).Value = true
args3(2).Name = "CaseSensitive"
args3(2).Value = false
args3(3).Name = "NaturalSort"
args3(3).Value = false
args3(4).Name = "IncludeAttribs"
args3(4).Value = true
args3(5).Name = "UserDefIndex"
args3(5).Value = 0
args3(6).Name = "Col1"
args3(6).Value = 3
args3(7).Name = "Ascending1"
args3(7).Value = true
args3(8).Name = "Col2"
args3(8).Value = 1
args3(9).Name = "Ascending2"
args3(9).Value = true
args3(10).Name = "IncludeComments"
args3(10).Value = false
args3(11).Name = "IncludeImages"
args3(11).Value = true
dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args3())
end sub
I think this is doing what I want right now. I think I can just copy it and edit the name of the data range for each of the 4 ranges I have. It’s a bit cumbersome, a lot of redundant code, so if anyone has a more elegant solution, I’m all ears.
Again, many thanks, and apologies for the confusion.