Macros/Calc/ba030/es

TDF LibreOffice en español Document Liberation Project Blogs comunitarios Weblate Nextcloud Redmine Preguntas y respuestas Donar

• Inicio
• Desarrollo
• Diseño
• Control de calidad
• Eventos
• Documentación
• Sitio web
• Regionalización
• Accesibilidad
• Mercadotecnia
• Diversity
• Wiki Help

• Macros
• Macros Generales
• Macros para Writer
• Macros para Calc
• Macros para Impress
• Macros para Draw
• Macros para Base
• Macros para 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.

Nota: 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 <span lang="en" dir="ltr" class="mw-content-ltr">Copy the output data rather than filter in place.</span>
  oFiltDesc.CopyOutputData = True

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Create a CellAddress and set it for Sheet1</span>
  REM <span lang="en" dir="ltr" class="mw-content-ltr">Column B, Row 13 (remember, start counting with 0)</span>
  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.