Jump to content

Excel: Advanced Filter


Recommended Posts

Hello! I am wanting to apply an advanced filter in Excel. I have about a 95% success rate using a 'high quality' blend of Sends and Sleeps. However, I'd like to follow best practice when possible.

According to MSDN, the object/method is  expression.AdvancedFilter(Action , CriteriaRange , CopyToRange , Unique).

When trying to run my script, I get a console error:
"E:\Testing001.au3" (49) : ==> The requested action with this object has failed.:
     $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter($xlFilterInPlace, "$J$1:$L$3")
     $oWorkbook.ActiveSheet.Range("A1:H24")^ ERROR

Here is my example code. Attached is the .au3 and example Excel file should you want to use it. The premise is to sort for students that are not assigned devices, or if they are whether or not the Tag or Serial Number is missing from the field. Since it's for 4th grade and up, I also want to exclude third grade. 

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

DirCreate(@ScriptDir & "\Backups")
DirCreate(@ScriptDir & "\Edits_For_Uploads")
Global $BackupDir = @ScriptDir & "\Backups"
Global $EditDir = @ScriptDir & "\Edits_For_Uploads"
Global $TimeStamp = @YEAR&@MON&@MDAY&'_'&@HOUR&@MIN&@SEC
Global $SISBlankBackup = $TimeStamp & '_' & 'SISBlankBackup.xlsx'
Global $SISBlankEdit = $TimeStamp & '_' & 'SISBlankEdit.xlsx'
Global $SISAssignBackup = $TimeStamp & '_' & 'SISAssignBackup.xlsx'
Global $SISAssignEdit = $TimeStamp & '_' & 'Has Assigned Devices.xlsx'
Global $SISAssignEdit2 = @ScriptDir & '\ExcelExample.xlsx'

SISAssignExcelFilter("IN", 2)


Func SISAssignExcelFilter($vBuilding, $vStep = 1)
    ;~  Open Excel
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $SISAssignEdit2, True)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error opening workbook '" & $SISAssignEdit2 & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    Sleep(5000)

    ;~  Add Advanced Filter array
    Local $aFilterValues = ['=E2<>"03"', '=E2<>"03"', '=G2=""', '""', '""', '=H2=""']
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[0], "J2", False)
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[1], "J3", False)
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[2], "K2", False)
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[5], "L3", False)

    ;~  Apply Advanced Filter (Headers of filter array have to be blank...)
    $xlFilterInPlace = 'xlFilterInPlace'
    $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter($xlFilterInPlace, "$J$1:$L$3")

EndFunc

Thank you!

Link to comment
Share on other sites

I modified the $xlfilterinplace constant and added a COM error handler:

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

DirCreate(@ScriptDir & "\Backups")
DirCreate(@ScriptDir & "\Edits_For_Uploads")
Global $BackupDir = @ScriptDir & "\Backups"
Global $EditDir = @ScriptDir & "\Edits_For_Uploads"
Global $TimeStamp = @YEAR&@MON&@MDAY&'_'&@HOUR&@MIN&@SEC
Global $SISBlankBackup = $TimeStamp & '_' & 'SISBlankBackup.xlsx'
Global $SISBlankEdit = $TimeStamp & '_' & 'SISBlankEdit.xlsx'
Global $SISAssignBackup = $TimeStamp & '_' & 'SISAssignBackup.xlsx'
Global $SISAssignEdit = $TimeStamp & '_' & 'Has Assigned Devices.xlsx'
Global $SISAssignEdit2 = @ScriptDir & '\ExcelExample.xlsx'

Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

SISAssignExcelFilter("IN", 2)

Func SISAssignExcelFilter($vBuilding, $vStep = 1)
    ;~  Open Excel
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $SISAssignEdit2, True)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel Filtering", "Error opening workbook '" & $SISAssignEdit2 & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ;~  Add Advanced Filter array
    Local $aFilterValues = ['=E2<>"03"', '=E2<>"03"', '=G2=""', '""', '""', '=H2=""']
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[0], "J2", False)
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[1], "J3", False)
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[2], "K2", False)
    _Excel_RangeWrite($oWorkbook, Default, $aFilterValues[5], "L3", False)

    ;~  Apply Advanced Filter (Headers of filter array have to be blank...)
    $xlFilterInPlace = 1
    $oWorkbook.ActiveSheet.Range("A1:H24").AdvancedFilter($xlFilterInPlace, "$J$1:$L$3")
    
EndFunc

; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

Maybe your problem is decribed here
https://support.office.com/en-us/article/filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b
in section: Using the equal sign to type text or a value

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

Thanks for the responses!

On 3/8/2018 at 10:31 AM, Juvigy said:

Search the forum, there are examples. You can specifically check for my posts - i remember answering the same questions few months ago.

Juvigy, I searched back to October searching for posts about Excel and I did not find anything that seemed to be about this. Sorry if I missed it!

I know the Advanced filter I have in place works, because I can do it manually or send hot keys to get the job done.

Send('{Alt}')
    Sleep(100)
    Send('{a}')
    Sleep(100)
    Send('{q}')
    Sleep(100)
    Send('{Tab}')
    Sleep(100)
    Send('$J$1:$L$3')
    Sleep(100)
    Send('{Enter}')
    Sleep(500)

I guess my main question is can AutoIt process all COM's? With the error happening at the AdvancedFilter Method, either my syntax is wrong or I need to find another method to go about it. Below is the result of the COM error func Water added. Thanks for that!

Testing.au3 (41) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Exception occurred.

    err.description is:     AdvancedFilter method of Range class failed
    err.source is:      Microsoft Excel
    err.helpfile is:    xlmain11.chm
    err.helpcontext is:     0
    err.lastdllerror is:    0
    err.scriptline is:  41
    err.retcode is:     0x800A03EC

Thanks!

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...