Copy Advanced Filter Results to Different Location

From The Document Foundation Wiki
< Macros‎ | Calc
Jump to navigation Jump to search
Other languages:
English • ‎italiano

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.

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

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

This Calc spreadsheet contains the above LibreOffice Basic code.