Copy Advanced Filter Results to Different Location

    From The Document Foundation Wiki
    < Macros‎ | Calc


    Other languages:

    Summary

    This page provides a LibreOffice Basic macro code snippet that copies advanced filter results to a different location. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

    Description

    The results of an advanced filter can be extracted to a different position using the OutputPosition property. Copying results in this way eliminates the need for Calc to hide rows that do not match search criteria, which it would normally do if you filter in-place.

    The macro code snippet below demonstrates how to copy filter results to a different location. Note that the filter descriptor must first be modified before these filter settings are applied.

    To demonstrate the operation of this snippet, take the code for the AdvancedRangeFilter macro procedure on the Create Advanced Filter page and insert the code given below, just before the filter method is called. The linked spreadsheet contains the modified version of the AdvancedRangeFilter macro procedure and it uses a class grade sheet in cells $Sheet1.A1:H11 as the data range and cells $Sheet2.A1:H3 as the criteria range. The filtered results are placed in cells $Sheet1.B13:I19.

    Note pin.svg

    Note:
    The OutputPosition property returns a copy of a struct. As a result, it is not possible to set individual values, such as the row or column, directly through this property. For example, setting oFilterDesc.OutputPosition.Row to 2 will not work, since it is the Row property on the copy, not the original, that changes.

    Code

    REM Copy the output data rather than filter in place.
      oFiltDesc.CopyOutputData = True
    
      REM Create a CellAddress and set it for Sheet1
      REM Column B, Row 13 (remember, start counting with 0)
      Dim outputCell As New com.sun.star.table.CellAddress
      outputCell.Sheet = 0
      outputCell.Column = 1
      outputCell.Row = 12
      oFiltDesc.OutputPosition = outputCell

    This Calc spreadsheet contains the above LibreOffice Basic code.