Apache OpenOffice Community Forum

Posted: **Mon Aug 18, 2014 2:53 pm**

Hi to all,

how can i apply a filter between two date on a column in a basic macro.

i have this snippet of code that work in ms excel, obviously it does not work the trick of option vbasupport 1 in calc
This code apply the filter between the two date on the column A in the row after a2 (where a2 is the column header)

Code: Select all

    Range("A2").Select  
    Sheets(1).EnableAutoFilter = True
    Sheets(1).Protect Contents:=True, userInterfaceOnly:=True
    Selection.AutoFilter Field:=1, Criteria1:=">=" & Format("07/01/2014", "mm/dd/yyyy"), _
    Operator:=xlAnd, Criteria2:="<=" & Format("14/01/2014", "mm/dd/yyyy")

How can i write something similar in OO Basic?

Thanks to all

Posted: **Tue Aug 19, 2014 5:46 pm**

I'm not sure about the filtering. From Andrew Pitonyak's OpenOffice Macros Explained (a free 500+ page download) this is an example for sorting:

Code: Select all

Sub SortColZero
	Dim oSheet
	Dim oRange
	Dim oSortFields(0) as new com.sun.star.util.SortField
	Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue

	oSheet = ThisComponent.Sheets(0)

	REM Set the range on which to sort
	oRange = oSheet.getCellRangeByName("B28:D33")

	REM Sort on the first field in the range
	oSortFields(0).Field = 0
	oSortFields(0).SortAscending = FALSE

	REM Set the sort fields to use
	oSortDesc(0).Name = "SortFields"
	oSortDesc(0).Value = oSortFields()

	REM Now sort the range!
	oRange.Sort(oSortDesc())
End Sub

I have an older version of that book, if you download the current book maybe he's updated it with a filtering example. I would guess the code for filtering works in somewhat the same way as the sorting. I also did a quick search for examples on the forum and found this that looks like it has some helpful code.

Posted: **Wed Aug 20, 2014 2:53 pm**

done!

This for adding the filter

Code: Select all

sub filtra_data()

   Dim xRange as object
   Dim FilterDesc as Object
   Dim FilterFields(1) as new com.sun.star.sheet.TableFilterField
   
	Dim data_da
	Dim data_a
	data_da = InputBox("Insert stard date (dd/mm/yyyy): ")
	    If data_da = "" Then Exit Sub
	data_a = InputBox("Insert end date (dd/mm/yyyy): ")
	    If data_a = "" Then Exit Sub 

   xRange = thiscomponent.getcurrentcontroller.activesheet.getCellRangeByName("A2:A1048576")
   FilterDesc = xRange.createFilterDescriptor(true)
   
   FilterDesc.ContainsHeader = true
   FilterFields(0).Field = 0
   FilterFields(0).IsNumeric = false
   FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
   FilterFields(0).StringValue = data_da
   
   
   FilterFields(1).Field = 0
   FilterFields(1).IsNumeric = false
   FilterFields(1).Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
   FilterFields(1).StringValue = data_a
   
   FilterDesc.SetFilterFields(FilterFields)
   
   xRange.Filter(FilterDesc)

End Sub

And this for removing it

Code: Select all

sub Remove_filter()
   Dim xRange as object
   Dim FilterDesc as Object
   Dim FilterFields(0) as new com.sun.star.sheet.TableFilterField

   xRange = thiscomponent.getcurrentcontroller.activesheet.getCellRangeByName("A2:A1048576")

xRange.Rows.Isvisible = True
end sub

Posted: **Wed Aug 20, 2014 5:28 pm**

Hallo

Why not simply:

Code: Select all

sub Remove_filter()
     thiscomponent.CurrentController.Activesheet.Rows.Isvisible = True
end sub

Karolus

Apache OpenOffice Community Forum

Apply filter to calc column via basic macro

Apply filter to calc column via basic macro

Re: Apply filter to calc column via basic macro

Re: Apply filter to calc column via basic macro

Re: Apply filter to calc column via basic macro