Function Reference


_Excel_FilterGet

Returns a list of set filters

#include <Excel.au3>
_Excel_FilterGet ( $oWorkbook [, $vWorksheet = Default] )

Parameters

$oWorkbook Excel workbook object
$vWorksheet [optional] Name, index or worksheet object to be filtered. If set to keyword Default the active sheet will be filtered

Return Value

Success: a two-dimensional zero based array with the following information:
    0 - On if a filter is set for this column
    1 - Number of areas (collection of ranges) the filtered column consists of
    2 - Filter criteria 1. An array of strings is returned as a string separated by "|"
    3 - Filter criteria 2. An array of strings is returned as a string separated by "|"
    4 - An XlAutoFilterOperator value that represents the operator that associates the two filter criterias
    5 - Range object for which the filters have been set
    6 - Number of visible records in the filtered range (including the row with the filter arrow)
Failure: 0 and sets @error.
@error: 1 - $oWorkbook is not an object or not a workbook object
2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code
3 - No filters found

Related

_Excel_FilterSet

Example

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Set two filters
_Excel_FilterSet($oWorkbook, Default, "A:E", 3, "<610")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $aShow[] = ["20", "40", "60"]
_Excel_FilterSet($oWorkbook, Default, "A:E", 2, $aShow, $xlFilterValues)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example", "Filters set:" & @CRLF & "  Column B: values = 20, 40 or 60." & @CRLF & "  Column C: values <610.")

; *****************************************************************************
; Display information about the filters on the active worksheet.
; *****************************************************************************
Local $aFilters = _Excel_FilterGet($oWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_FilterGet Example 1", "Error filtering data." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ArrayDisplay($aFilters, "Excel UDF: _Excel_FilterGet Example 1", Default, Default, Default, "Filter on|#areas|Criteria1|Criteria2|Operator|Range|#Records")