Filter Fonction in excel with autoit

[jahjah]

Hello everybody,

Can somebody could help me to user the fonction autofilter in excel with autoit??

Here his my trouble:

I open a excel file then i try to apply to this Worksheet a filter with auto it

please somebody give me some advice

Here is a part of my script

$oExcel.ActiveWorkbook.Sheets("Feuil1").Select()

Local $nb_colonne = $oExcel.ActiveSheet.UsedRange.Columns.Count ; Compte le nombre de colonnes actives de "ONGLET_SOURCE"

Local $nb_ligne = $oExcel.ActiveSheet.UsedRange.Rows.Count ; Compte le nombre de lignes actives de "ONGLET_SOURCE"

MsgBox (0,"debug",$nb_colonne)

MsgBox (0,"debug",$nb_ligne)

Local $xlFilterInPlace = 1 ; Constante VBA = Leave data in place for AdvancedFilter

$oExcel.Sheets("Feuil1").Range( _

$oExcel.Cells("A5,O2596"), $oExcel.Cells($nb_ligne+1,$nb_colonne+1) _

).AdvancedFilter( _

$xlFilterInPlace, $oExcel.Sheets("FILTRE_ELABORE").Range("A1:A2"), false )

Edited March 1, 2011 by jahjah

[Xenobiologist]

Search for Excel_udf.au3

; #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   ;==>_ExcelFilter

Mega

[water]

  On 3/1/2011 at 4:33 PM, 'jahjah said:

Can somebody could help me to user the fonction autofilter in excel with autoit??

...

).AdvancedFilter( _

$xlFilterInPlace, $oExcel.Sheets("FILTRE_ELABORE").Range("A1:A2"), false )

Are you trying to use Autofilter or Advancedfilter?

If you try to use Advancedfilter then the number of parameters is wrong (according to M$). Try:

.AdvancedFilter($xlFilterInPlace, $oExcel.Sheets("FILTRE_ELABORE").Range("A1:A2"), "", false )

[jahjah]

Ok thanks a lot for your answer, i'll try that and give you news

[jahjah]

Thanks you to all of you for the help

I choose the function 'ill user

It's will be _excelFilter

I tried it but i've got this error message

  Quote

C:\Program Files\AutoIt3\Include\Excel_udf.au3 (31) : ==> The requested action with this object has failed.:

$oExcel.selection.Autofilter($sField, $sCriteria)

$oExcel.selection.Autofilter($sField, $sCriteria)^ ERROR

Here is my script, but i made some change

For resume it want to

Enter a word to search in a excel fil fot that i use GUICtrlCreateInput

Make a research with Find (that it's ok)

After i want to filter my file with the variable enter in GUICtrlCreateInput

-------------------------------------------

The resaerch is ok but i cannot made the filter

please find in attachement my script

testscriptpreskeok.au3

[jahjah]

Hi,

My script working but i still need some help

The excelfilter function working but not well

I cannot filter all my file when i filter i still have the other row

i want only the row i want appear.

While 1

$msg = GUIGetMsg()

Select

Case $msg = $GUI_EVENT_CLOSE

MsgBox(0, "Rachel_Search", "Merci de votre visite")

ExitLoop

;When button is pressed, label text is changed

;to combobox value

Case $msg = $Button1

$menustate = GUICtrlRead($Input1)

Chercher()

Filtre($menustate)

EndSelect

WEnd

Exit

Func Chercher()

Local $search_cell = $oExcel.Columns("A:G").Find($menustate)

If( IsObj($search_cell) = 1 ) Then ; Si la recherche à abouti

MsgBox(0, "Find success", "La recherche de "&Chr(34)&$menustate&Chr(34)&" dans la plage donne la cellule ("&$search_cell.Row &";"&$search_cell.Column&")" )

Else

MsgBox(0, "Find failed", "La recherche de "&Chr(34)&$menustate&Chr(34)&" dans la plage n'a rien donné" )

EndIf

EndFunc

Func Filtre($find)

_ExcelFilter ($oExcel,5,3,3,($find))

EndFunc

[jahjah]

I found the solution.

Thanks again