Sign in to follow this  
Followers 0
jahjah

Filter Fonction in excel with autoit

7 posts in this topic

#1 ·  Posted (edited)

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 by jahjah

Share this post


Link to post
Share on other sites



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


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

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 )

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

I found the solution.

Thanks again

Share this post


Link to post
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
Sign in to follow this  
Followers 0