Macros/Calc/ba030

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Macros
• General Macros
• Macros for Writer
• Macros for Calc
• Macros for Impress
• Macros for Draw
• Macros for Base
• Macros for Math
• Basic/Python

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: 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.