Jump to content

Addition to Excel UDF


Manjish
 Share

Recommended Posts

Hi I wanted to make a filter function in excel. I don't know if it has been implemented before, but I made 1. Here it is:

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelFilter
; Description ...: Applies a filter to an excel file.
; Syntax.........: _ExcelFilter($oExcel,$sRangeorRow, $iColumn, $sField, $sCriteria)
; Parameters ....: $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                  $sRangeOrRow - The range in A1 format, or a row number for R1C1 format
;                  $iColumn - The specified column number for R1C1 format (default = 1)
;                  $sField- the field no. where filter is to be applied.
;                  $sCriteria- the criteria for filter is to be applied. For e.g filter by country name "India".
; Return values .: Success      - Returns 1
;                  Failure      - Returns 0 and sets @error on errors:
;                  @error=1     - Specified object does not exist
;                  @error=2     - Row or column invalid
;                  @extended=0 - Row invalid
;                  @extended=1 - Column invalid
; Author ........: Manjish Naik (naikma@gmail.com)
; Modified.......: None as yet
; Remarks .......: None
; Related .......:
; Link ..........;
; Example .......; _ExcelFilter($oExcel,1,2,2,"Amit Ravindra,Patil"), this filters out all entries except the one having "Manager Name" as "Amit Ravindra,Patil".
;                                                                     The "manager name" is second filter field located at 1 row and 2nd column, hence $sField=2.
; ===============================================================================================================================
Func _ExcelFilter($oExcel,$sRangeorRow, $iColumn, $sField, $sCriteria)
If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        $oExcel.ActiveSheet.Cells($sRangeOrRow, $iColumn).Select
        $oExcel.selection.Autofilter($sField, $sCriteria)
    Else
        $oExcel.ActiveSheet.Range($sRangeOrRow).Select
        $oExcel.selection.Autofilter($sField, $sCriteria)
    EndIf
EndFunc

An Example:

#include<excel.au3>
$sfilepath=@DesktopDir&"\Ist Aug Six Sigma Data.xls"
$oExcel=_ExcelBookOpen($sFilepath)
_ExcelFilter($oExcel,1,2,2,"Amit Ravindra,Patil")
Edited by Manjish
[font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...