Manjish Posted September 18, 2009 Posted September 18, 2009 (edited) 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 September 18, 2009 by Manjish [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com
Manjish Posted September 21, 2009 Author Posted September 21, 2009 Can we included this in the Excel UDF? If yes, who do I need to contact? [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now